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
reconfigure

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” ‘)