LDAP Query

The active directory services on your domain controller allows querying objects within T-SQL. During the preparation for the exam 70-462 I had to build up a domain in which I joined 6 computers. The domain is called consto (not contoso!). Here is how to query against the ADSI (active domain service interface).

1) Using a linked Server (Linked server is called ADSI)

you use OPENQUERY to get results over a linked server:

SELECT * FROM OPENQUERY( ADSI, 'SELECT Name, displayName,givenname,distinguishedName, SAMAccountName FROM ''LDAP://consto.com/cn=users,dc=consto,dc=com''')

here is users a container and therefore it must be classified with cn. In order to link the server you must provide the credentials of a user, which has right to read the informarmation from AD

2) Using direct access

you use OPENROWSET to access the AD Service

SELECT Name, displayName,givenname,distinguishedName, SAMAccountName, telephoneNumber

FROM

OPENROWSET('ADSDSOObject','adsdatasource'; 'consto\kim_akers';'herecomespassword*****',

'SELECT Name, displayName,givenname,telephoneNumber, distinguishedName, SAMAccountName

FROM ''LDAP://consto.com/cn=users,dc=consto,dc=com''')

You can find out the list of the attributes using the Active Directory Explorer v1.44 by Mark Russinovich [click here]