Querying Active Directory through SQL

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

Popular posts from this blog

Getting Started with Mirth (Part 2)

Mirth

Visual Studio 2012–Debug in Chrome Incognito Mode