Sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’
"ADSDSOObject" is the provider_name, "adsdatasource" is the data_source and should not be changed. For more information, search for "ADSI" in SQL Server Books Online (Updated SP3).
An entry is put into the sysservers table in the master database. Open SQL Enterprise Manager to check that ADSI is added as a linked server in <servername>, Security, Linked Servers. Right-click on ADSI and select Properties. On the Security tab, select Be made using this security context and enter “domain\user” and password.
To query the Directory Service, the format is used similar to that given in SQL Server Books Online (Updated SP3), Accessing and Changing Relational Data, Distributed Queries, OLE DB providers tested with SQL, OLE DB provider for Microsoft Directory Services. This uses the OPENQUERY function to send a command to the directory service and return the data to the Select command:
FROM OPENQUERY (ADSI,
FROM ‘’LDAP://DCname/ DC=uk,dc=company,dc=com’’
WHERE objectCategory=”Computer” AND memberof ‘’cn=Apps,ou=Local Apps,ou=Application Groups, DC=uk,dc=company,dc=com’’’)
Because of an error in the Notification Server code, an extra closing parenthesis, ’)’, must be added at the end of the Value SELECT command.
Note: LDIFDE.exe, a utility found on Windows 2000 Servers, can be run to export the active directory to a text file, which will assist with deriving the LDAP URL from the LDAP Distinguished Name:
Ldifde –f c:\temp\dc.txt –s DCname.uk.microsoft.com
Create a new (dynamic) collection in Software Delivery Solution. Add a filter, Type “Machine”, Table “Wrksta”, Field “name”, Operator “IN”. Value is the select command above, no carriage returns; with the appropriate computer group Distinguished Name.
Please note that creating LDAP based queries with an OLE DB provider causes the SQL transaction to become a unserializable transaction. Therefore, the Delta Collection Refresh scheduled task will fail on this collection. All other collection refresh tasks will work correctly. See article 20465 for further details.