How to query Active Directory using SQL Server

Posted on August 25th, 2008 webmaster 10 comments          Print

Here is one of those things that I wish I had known much sooner.  Very useful and a big time saver.  I just found out about this a few months ago when I wanted to run a report against AD to see if the account info are consistent and up to date, to see which accounts have passwords set to never expire, when they were created/last updated, etc.

So I asked myself whether it’s possible to query AD directly from SQL.  The first thing I did was do a search on Google of course and sure enough I found this website with instructions on how to do it!

Here are the steps:

1. Create a linked server to AD using this command in Query Analyzer:

sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’

2. Create views using the example below, I’m calling them vADUsers and vADGroups (replace calazan and com with your domain, add more AD attributes to the view as needed):

CREATE VIEW vADUsers AS
SELECT *
FROM OPENQUERY (ADSI,
‘SELECT co, whenCreated, whenChanged, sAMAccountName, sn, givenName, displayName, mail, telephoneNumber, mobile,
physicalDeliveryOfficeName, facsimileTelephoneNumber, title, department, company, manager, ipPhone, userAccountControl, badPwdCount
FROM ”LDAP://DC=calazan,DC=com”
WHERE objectCategory = ”Person” AND objectClass = ”user”’)

CREATE VIEW vADGroups AS
SELECT *
FROM OPENQUERY (ADSI,
‘SELECT displayName, groupType, mail, name, info, whenChanged, whenCreated
FROM ”LDAP://DC=calazan,DC=com”
WHERE objectClass = ”group”’)

3. Query the views just like you’re querying a normal SQL table:

I also recommend that you download the free ADSI Edit tool (adsiedit.msc) to see all the attributes available in your Active Directory site.  Microsoft Office Communications Server 2007 and Exchange Server 2007 for example extend the AD schema and you might want to query some of those extra attributes as well.