It is also possible to query Active Directory from an SQL Server instance using an SQL distributed query. You use a system stored procedure in SQL Server to add Active Directory as a linked server. Then you use the SQL OPENQUERY command to invoke the ADSI OLEDB provider ADsDSOObject, pass a query to Active Directory, and return a recordset. SQL Server must be version 7.0 or above.

The first step is to use the system stored procedure sp_addlinkedserver to add your Active Directory as a linked server. The syntax would be (this is one line):

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADsDSOObject', 'adsdatasource'

If you use Windows authenticated logins, which is recommended, this is all that is required. If you use SQL Server authenticated logins you must use the sp_addlinkedsrvlogin system stored procedure to configure a login.

Once the linked server is established, you can use the OPENQUERY command to pass a query to Active Directory. You use either SQL syntax or LDAP syntax. An example using an SQL syntax query:

SELECT * FROM OPENQUERY(ADSI,
    'SELECT sAMAccountName, mail
    FROM ''LDAP://dc=MyDomain,dc=com''
    WHERE objectCategory = ''person'' AND objectClass = ''user'''

The same query using LDAP syntax would be:

SELECT * FROM OPENQUERY(ADSI,
    '<LDAP://dc=MyDomain,dc=com>;
    (&(objectCategory=person)(objectClass=user));
    sAMAccountName,Mail;subtree')

Note that constants, like "person" and "user", are enclosed in single quotes when you use SQL syntax, but are not enclosed by any character when you use LDAP syntax. The entire query is enclosed in single quotes, so any single quotes in the query, such as those enclosing the constants, must be doubled.

There are two limitations you should be aware of. First, the OPENQUERY statement does not support multi-valued attributes. You cannot retrieve the values of multi-valued attributes, like memberOf. Second, the total number of records that can be retrieved is limited to 1500 (1000 in Windows 2000 Active Directory). Paging is not supported from an SQL distributed query, so this limitation cannot be overcome, except by modifying the Active Directory server limit for maxPageSize.

For more information, see these links:

http://support.microsoft.com/kb/299410
http://msdn.microsoft.com/en-us/library/aa746379(VS.85).aspx
http://msdn.microsoft.com/en-us/library/aa746494(VS.85).aspx
http://msdn.microsoft.com/en-us/library/aa746385(VS.85).aspx

Many people have reported problems using OPENQUERY. It may be necessary for the SQL Server instance to run with a service account that is a domain user. If the local system account is used instead, it may be that the instance lacks permission in Active Directory.