SQL Server: How to connect and query Active Directory

Tweet

You might need to query your domain's Active Directory for different reasons. There are limitations in doing so (which I explain below), but in case you need to retrieve those information anyway, here's how to do it.
First of all, we need to create a new linked server in SQL Server. You can connect to Active Directory executing the following small stored procedure:

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI',
@srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject',
@datasrc=N'W2k3dc'

The new server will be named ADSI. Remember to replace W2kdc with the full computer name of your AD server.
You can now query the new linked server using the OPENQUERY command:

SELECT givenName AS firstName, sn AS lastName, displayName, LOWER(sAMAccountName) AS accountName, mail AS emailAddress, telephoneNumber AS phoneNumber, mobile AS cellPhoneNumber, physicalDeliveryOfficeName AS siteName, department, division FROM OPENQUERY
(
ADSI,'SELECT givenName, sn, displayName, sAMAccountName, mail, telephoneNumber, mobile, physicalDeliveryOfficeName , department, division
FROM ''LDAP://W2k3dc''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

The query will retrieve data from your AD filtering only users. Again, remember to replace W2kdc with the name of your domain controller.
As I said before, there are some things to remember and some limitations. Specifically:

1. "LDAP" is case sensitive, if you try using "ldap", the query will throw an error;
2. you cannot retrieve passwords - no way!;
3. there is a query limit: by default, it returns only a maximum of 1000 (the number might be different in accordance with the server SO version) objects in response to a single LDAP query (to increase the limit see this article);
4. you need to execute the OPENQUERY from a domain user account. If you use a local system account you won't have enough permissions to query your AD.

I think that's all.

If you want to know more, an interesting article can be found here.