Distributed Query

Because ADSI is an OLE DB provider, it can participate in Distributed Query introduced in Microsoft® SQL 7. The following are possible scenarios:

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

To set up a distributed join, 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 name.

The following code is an example.

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

You can also use sp_addlinkedsrvlogin to specify which user should be run. Because SQL 7 participates in Single Sign On (SSO), Kerberos delegation can be used when Kerberos is the authentication package. If you choose Kerberos, do not run this option.

Example of an sp_addlinkedsrvlogin call:

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

When you have established this link, run 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=Fabrikam,DC=Com" WHERE objectCategory="person" AND objectClass="contact"')