Distributed Query

Since ADSI is an OLE DB provider, it can participate in Distributed Query introduced in Microsoft® SQL 7. This brings up many interesting scenarios:

• Joining Active Directory objects with SQL Server data.
• Updating SQL data from Active Directory objects.
• Three- or four-way joins with other OLE DB providers. For example, Index Server, SQL Server, and Active Directory.

To set up a distributed join, you must first set up ADSI mapping using the sp_addlinkedserver stored procedure in a SQL Server Tool such as SQL Query Analyzer. This procedure links a name to an OLE DB provider's name.

For example:

sp_addlinkedserver 'ADSI', 'Active Directory Services 2.0', 'ADSDSOObject', 'adsdatasource'

Optionally, you can use sp_addlinkedsrvlogin to specify which user should be run. Since SQL 7 participates in Single Sign On (SSO), Kerberos delegation can be used when Kerberos is the chosen authentication package. If you elect to use Kerberos, you should not run this option.

Example of an sp_addlinkedsrvlogin call:

sp_addlinkedsrvlogin ADSI, false, 'REDMOND\Administrator', 'CN=Administrator,OU=Sales, DC=actived,DC=Microsoft,DC=com', 'secret**2000'

Once you have established this link, you can start running the query against the directory.

For example:

select  Name, sn lastName, street Street, l City, st State
from OpenQuery( ADSI, 
'SELECT Name,sn, street, l, st, description FROM "LDAP://OU=Sales, DC=actived,DC=Microsoft,DC=Com" WHERE objectCategory="person" AND objectClass="contact"')