Use SQL to view Active Directory Data

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