1.       Create a linked server connection to Active Directory

EXEC sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 3.0’, ‘ADSDSOObject’, ‘adsdatasource’

2.       In SQL Server Management Studio, go to Server Objects > Linked Servers > ADSI and go to properties

3.       In the Security Tab, specify a username that will be used to run the query.

a.       Local Login is the domain login for SQL Server (so <domain>\<user> for example)

b.      Or enter a SQL Server login (e.g. sa) and enter a domain login in remote user and password

4.       Run a query

SELECT * FROM OpenQuery(

ADSI,'<LDAP://<domain.com>;(&(objectCategory=Person)(objectClass=user));sAMAccountName,givenName,sn, displayName, telephoneNumber, mobile, mail, department, physicalDeliveryOfficeName’)

Note that the bit in red will need to be changed to the fully qualified domain.

It’s worth bearing in mind that this will then be restricted to the rights that the user has for querying Active Directory, so if the user doesn’t have access to query Active directory, they’ll get an error when you run the query from SQL Server.

Tags: ,
Advertisement