Use SQL to view Active Directory Data
January 19, 2010 1 Comment
If you want an easy way of viewing objects in Active Directory one good way of doing it is creating a view or stored procedure on SQL Server. By doing this you can easily call now the views or stored procedure from you application without bothering to use System.DirectoryServices, the only downside it limits the resultset 1000 records, not sure how to increase this as of the moment but this is good for selecting a dataset from AD which have common properties.
You need to do the following steps:
Step 1: Create a linked server to your Active Directoy from SQL by running this command.
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
‘ADSI’ can be any name you want.
After doing that you will see the new linked server from the Server Objects – Linked Server.
Step 2: Adjust the security settings as needed, if you want to run the command as a specific user then you have to right click on the linked server you just created and select properties then go to the security tab where you can set the login and password of the account that will run the command.
Step 3: You can now create you view or stored procedure, for this sample we will create a stored procedure which have the sAMAccountName as the parameter. sAMAccountName is the login name of a specific user.
CREATE PROCEDURE [dbo].[ActiveDirectory_GetUserByUserName] @sAMAccountName varchar(200) AS BEGIN SET NOCOUNT ON; declare @STRSQL varchar(8000); select @STRSQL = 'SELECT employeeNumber, sAMAccountName, displayName, givenName, sn, initials, name, title, company, department, facsimileTelephoneNumber, telephoneNumber, homePhone, mobile FROM OPENQUERY (ADSI, ''SELECT employeeNumber, sAMAccountName, displayName, givenName, sn, initials, name, title, company, department, facsimileTelephoneNumber, telephoneNumber, homePhone, mobile FROM ''''LDAP://DC=test,DC=com'''' where objectClass = ''''User'''' and objectCategory = ''''Person'''' and sAMAccountName = ''''' + @sAMAccountName + ''''''')' EXEC(@STRSQL) END