Querying Active Directory through SQL
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