Calazan.com
Share the Knowledge
-
How to query Active Directory using SQL Server
Posted on August 25th, 2008 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.
10 responses to “How to query Active Directory using SQL Server”
-
How can I get past the ldap 1000 lines limit?
-
webmaster April 5th, 2009 at 14:41
Hi Luke,
It looks like you will need to change the value of the MaxPageSize attribute in Active Directory:
MaxPageSize – This value controls the maximum number of objects that are returned in a single search result, independent of how large each returned object is. To perform a search where the result might exceed this number of objects, the client must specify the paged search control. This is to group the returned results in groups that are no larger than the MaxPageSize value. To summarize, MaxPageSize controls the number of objects that are returned in a single search result.
Default value: 1,000
Source: http://support.microsoft.com/default.aspx?scid=kb;en-us;315071&sd=tech
-
Ian Schwamberger July 13th, 2009 at 13:47
Use filters to page through results. See http://msdn.microsoft.com/en-us/library/ms808539.aspx.
You can cause all kinds of problems (especially with Exchange) if you increase the maxPageSize.
-
webmaster August 2nd, 2009 at 15:26
Hi Santiago,
I believe you can specify the OU in the FROM like this:
SELECT *
FROM OPENQUERY (ADSI,
‘SELECT displayName, groupType, mail, name, info, whenChanged, whenCreated
FROM ”LDAP://OU=Marketing, DC=domain,DC=com”
WHERE objectClass = ”group”’)Haven’t tested it though.
-
how do I query other properties such as otherFacsimileTelephoneNumber …
when I include this in my query I get the error Cannot get the current row value of column “[ADsDSOObject].otherFacsimileTelephoneNumber” from OLE DB provider “ADsDSOObject” for linked server “ADSI”. Could not convert the data value due to reasons other than sign mismatch or overflow.
-
Lawrence December 29th, 2009 at 14:04
Hi, thanks for this info, trying to look for it for ages!
I was wondering, how can I populate a table and then edit the data from Active Directory, so by changing values in a field, I can then update it and Active Directory will be modified, Its so I can manage AD from a website I am building for a school project!
Thanks,
Lawrence
-
webmaster December 29th, 2009 at 20:21
Hey Lawrence,
I’ve wondered about modifying Active Directory using an SQL query myself but I haven’t found a way to do it and I’m not sure if it’s even possible.
-
Clement January 18th, 2010 at 10:50
Hi,
I have the same issue as Abi when I try to query the postOfficeBox :
SELECT *
FROM OPENROWSET(
‘AdsDsoObject’
,’ADSI Flag=0×11;Page Size=10000′
,’SELECT postOfficeBox
,sn FROM
”LDAP://FOOBAR/DC=FOO,DC=COM”’)Could not convert the data value due to reasons other than sign mismatch or overflow
Have any idea ?
a+, =)
-=Clement=-
Leave a reply
-
Random Pics
Luke March 26th, 2009 at 15:45