Create a SQL linked server to ADSI
Recently my client asked me to run a promotion. They were moving code from development to QA and that too in a newly configured server.
The promotion contained certain SQL scripts which queries the Active directory. But since there were no ADSI linked server created and configured, I had to create it.
Sometimes we would need to query the Active directory using SQL query.
The script below will create a linked server and and use OPENQUERY to retrieve data.
1. Create the linked Server – to create a linked server
EXEC sp_addlinkedserver @server = ‘ADSI’, @srvproduct = ‘Active Directory Services 2.5’, @provider = ‘ADSDSOObject’, @datasrc = ‘adsdatasource’
2. Add a security Context – create a security context
EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘ADSI’, @useself = ‘False’, @locallogin = ‘sa’, @rmtuser = ‘<DOMAIN>\<username>’, @rmtpassword = ‘<password>’
3. Configure the server to allow OPENQUERY functions
–Configure the server to allow OPENQUERY functions
sp_configure ‘show advanced options’, 1
reconfigure with override
sp_configure ‘Ad Hoc Distributed Queries’, 1
We can now access Active Directory using a query such as this:
SELECT * FROM OpenQuery(ADSI, ‘SELECT * FROM ”LDAP://DC=<DOMAIN>,DC=com” WHERE objectCategory=”User” ‘)