How to Query Active Directory (AD) from SQL Server

This post was written by namwar on August 30, 2009
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
sn,facsimileTelephoneNumber……,displayName,title.
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.

Reader Comments

I’m trying to pull the AD field description but I get the following error when I do that:

“Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider “ADSDSOObject” for linked server “ADSI”. Could not convert the data value due to reasons other than sign mismatch or overflow.”

It works fine without this field. Any way around this error?

Thanks

#1 
Written By Robert on December 11th, 2009 @ 7:46 pm

I am very glad I found your site on myspace. Thank you for the sensible critique. Me and my brother were just preparing to do some research about this. I am glad to see such reliable info being shared for free out there.
Best wishes,
Albern from Topeka city

#2 
Written By birth control forum on February 23rd, 2010 @ 8:23 pm

Add a Comment

required, use real name
required, will not be published
optional, your blog address