Have you ever had the need to query Active Directory right out of SQL Server using native TSQL?  There are 3 ways to accomplish this.  The first is CLR, but I won’t be covering that method.  The other two methods are OPENROWSET and OPENQUERY.  OPENROWSET requires Ad Hoc Distributed Queries to be enabled and OPENQUERY requires a linked server.  I had a fellow MVP ask me how to do this recently, but his restriction was that he could not use linked servers.  He will need to use the OPENROWSET method to avoid linked servers, but I also challenged him to consider creating the linked server, running the OPENQUERY, and then deleting it.  You can do all that in 1 piece of code and I’ll show that below.  It’s just an option worth knowing in case it fits for your environment.

In the case of my friend, they used something random for user names, but he wanted to get the friendly name.  In Active Directory the attribute he was looking at is called the sAMAccountName, but the attribute he needed was the displayName.  My examples below will only pull the displayName attribute, but you can easily add in any attribute you are looking for.  If you are not sure what attribute you are looking for or what is even available, then I suggest you download and take a look at the Softerra LDAP Browser.  It is also useful for learning how to write LDAP queries.  The last thing to be aware of is that what you see in Active Directory Users and Computers is generally not the real attribute name or it is not spelled exactly the same when referencing it programmatically via an LDAP query.  Below are some screen shots of those attributes we are dealing with in this article and their real name in AD.  You can reference this article on translating what you see in ADUC to LDAP or the real AD name.

Attributes in ADUC

In the code below, the first thing we do is enable Ad Hoc Distributed Queries so we can try out the OPENROWSET method.  The advantage to this method is not having a linked server and being able to call it directly out of TSQL.  Once we have that enabled we write our query and you’ll notice that we are essentially doing 2 queries.  The first query is the LDAP query inside the OPENROWSET function.  Once those results are returned we are using another query to get what we want from the result set.  Here is where I want you to stop and think about things.  If my LDAP query pulls back 50 attributes, or “columns” in SQL terms, and I tell it I only want 10 of them, what did I just do?  I brought back a ton of extra data over the wire for no reason because I’m not planning to use it.  What we should see here is that the columns on both SELECT statements are the same.  They do not, however, have to be in the same order.  The reason for that is because LDAP does not guarantee to return results in the same order every time.  The attribute or “column” order in your first SELECT statement determines the order of your final result set.  This gives you the opportunity to alias anything if you need to.

The second part of the code looks at using OPENQUERY.  The first thing we do is create a linked server using the Active Directory provider called ADsDSOObject.  Next we write our OPENQUERY and you’ll notice that it looks very similar to OPENROWSET, but instead of defining the connection provider in the function call we reference the linked server.  The same rule applies here about how many attributes you pull back and how many you actually plan on using, so continue to keep that in mind.  The last thing I do is delete the linked server.

Query Active Directory

--There are 2 ways to query AD from SQL Server.  The first is using OPENROWSET and the second is using OPENQUERY which requires a linked server.

/*** OPENROWSET METHOD ***/
--You have to enable Ad Hoc Distributed Queries to use OPENROWSET.  Note the OPENQUERY does NOT require this to be enabled since it uses Linked servers.

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
RECONFIGURE;
GO

SELECT DisplayName
FROM OPENROWSET('ADSDSOOBJECT','adsdatasource','SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass = ''User'' ')

/*** OPENQUERY METHOD ***/
--Here is where we create our Linked Server connection to AD
EXEC master.dbo.sp_addlinkedserver @server = N'AD', @srvproduct=N'Active Directory Services Interface', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'AD', @locallogin = NULL , @useself = N'True'
GO

--Here is the query using the above created Linked server
SELECT displayName FROM OpenQuery (AD,
'SELECT displayName
FROM ''LDAP://mydomainFQDN.com/ou=mySubOU,ou=myTopOU,dc=mychilddomain,dc=myTLdomain,dc=com''
WHERE objectClass = ''User'' ')
GO

--Here we delete our Linked Server
EXEC master.dbo.sp_dropserver @server=N'AD', @droplogins='droplogins'
GO

More Active Directory and SQL Related Articles