Query Active Directory \ LDAP

The following will explain how to set up and query Windows Active Directory (AD) (LDAP) to return all employee account records into a temporary table, overcoming the 1000 \ 1500 record limit imposed by AD. This is done by making multiple queries of sAMAccountName using combinations of the first two characters (i.e. AA*, AB*, AC*, ...)

We will be using OPENQUERY so we will need a linked server to be defined on the SQL Server.

Linked Server Set Up:

You can create a linked server connection to Active Directory by using "OLE DB Provider for Microsoft Directory Services". It will not require anything else but an account with access to read Active Directory which really is any user. In the script below I've just called the linked server "ADSI".

We specify the Domain to connect to in the actual OPENQUERY statement rather than in the linked server properties. The following query will test the linked server.

Be sure to change "LDAP://DOMAINname/OU=Accounts,DC=domain,DC=internal" to match your AD domain.

LDAP Query Script:

The following query will return all the OU=Accounts into a temporary table. If you get an error because a query is returning more than 1000 or 1500 records, then simply add a third WHILE loop.