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, now you can easily call the views or stored procedures from your application without bothering to use System.DirectoryServices. The only downside is that it limits the resultset to 1000 records, not sure how to increase this as of the moment but this is good for selecting a dataset from AD which has common properties.

You need to do the following steps:

Step 1

Create a linked server to your Active Directory 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 has 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