Querying Active Directory from SQL Server
Had a request a few weeks ago to get a list of AD users by writing some custom code. While I love to write code, I thought there had to be an easier (faster) way to get my data. There is... t-sql! Here's what I did:
1. Add a linked server to Active Directory. You can do this with t-sql (below) or through the Enterprise Manager / Management Studio interface.
sp_addlinkedserver 'ADSI', 'Active Directory Service
Interfaces', 'ADSDSOObject', 'adsdatasource'
2. Do a t-sql SELECT. The t-sql below returns name and email address.
SELECT *
FROM OPENQUERY (ADSI,
'SELECT givenName, sn, mail FROM ''LDAP://DC=<domain>,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')