Use SQL to view Active Directory Data

By | January 19, 2010

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.

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.

Security Context

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


One thought on “Use SQL to view Active Directory Data

  1. Nathan

    One thing to note when using an ADSI linked server is that your query results are limited to 1000 rows – this is due to a setting in Active Directory that helps protect against large queries causing slowdown on domain controllers. It’s not much of an issue on small domains or with queries that only return a few rows, but can become a problem when you have more than 1000 user or computer accounts.

    A different approach is to set up an export of details from Active Directory using the CSVDE utility that ships with Windows Server 2003 and later, then import the resulting file into a database for further querying. This obviously means there is a time delay between AD changes being reflected in the database, but in a lot of situations I’ve found this isn’t necessarily an issue.

    cheers, Nathan

    Reply

Leave a Reply