Wednesday, October 10, 2007

Querying Active Directory through SQL

October 10, 2007 Posted by Jason Irwin , , 2 comments
Lately I’ve had quite a lot of interaction with Active Directory, using multiple tools and methods to get the data I require. As a SQL developer, I was very pleased to find out that active directory could be queried through SQL, allowing data from active directory to be joined on data from our database, allowing queries across multiple operational domains.

This is a quick introduction to querying Active Directory using SQL – these queries
work in both SQL 2000 and 2005.

To query AD through SQL it is first necessary to create a linked server to Active Directory using the ADSI provider. This can be achieved as follows:



Next we query the linked server using the openquery command in SQL. The
below example selects all the users in the Accounting Employees organizational unit
on the irwinj domain.


Finally, if we want the same group of employees, but this time we need
to filter to show only active users, we can use bit-masking on the userAccountControl
to filter the group as follows:


NOTE: A major drawback when querying AD (regardless
of the method used) is that the default AD server setting imposes a return limit
of 1000 objects. If a query returns more than 1000 objects, this list will be cut
short. It is not recommended to increase this default return value, so we must query
around this! The obvious method, and SQL best practice, is to provide the most narrowing
filter possible to your SQL query, but if all else fails there are other possibilities
(http://blogs.msdn.com/ikovalenko/archive/2007/03/22/
how-to-avoid-1000-rows-limitation-when-querying-active-directory- ad-from-sql-2005-with-using-custom-code.aspx
)

2 comments:

Tom De Cort said...

Hi,

I just don't get this thing to work!

I have a domain controller:
'DC-01'

so the procedure would be:
EXEC master.dbo.sp_addlinkedserver
@server = N'ADSI',
@srvproduct=N'Active Directory Services',
@provider=N'ADsDSOObject',
@datasrc=N'LDAP://DC-01'


Whenever I try to launch a query I always get the same error:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "my query" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

jirwin said...

Tom,
That is correct. In my example i bind to the domain itself, but your code should allow you to bind directly to the domain controller - in fact, i have used precisely the same code (save the actual domain controller) on another server and it worked like a charm.

Is your error occcuring on creation of the linked server, or on a query you perform over that linked server? If you want to send me more details I'll be happy to try and work this out with you.
Thanks
Jason