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'' ')
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
9 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |