Posted Under: Blogroll, How-To, Object Search, Query, SQL Server 2005, SQL Server 2008, TSQL
There can be a scenario where you will want to query Active Directory, directly from your stored procedure. This can be achieved by creating a linked server to your target Active Directory and then querying it through OPENQUERY functionality. Following is an example to achieve this:
Step 1: Add Linked Server for Active Directory
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
Step 2: Query the Active Directory
SELECT * FROM OpenQuery ( ADSI, --Name of the linked server for Active directory, created in step 1 'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn --Fields you want to retreive FROM ''LDAP://DC=TestDomain'' --Name of your Active Directory Domain where objectClass = ''User''' --Type of objects you want to query e.g. User, Person etc... )
NOTE: The order in which columns will return will be exactly reversed of what you mentioned in your query. For example, in above query we have mentioned
title, displayName…….,facsimileTelephoneNumber, sn but the resultset will return as
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.