• You must install SQL Server 7.0. For more information about SQL Server, visit http://www.microsoft.com/sql
• You must install ADSI 2.5 Runtime (http://www.microsoft.com/adsi) -or- (ftp://ftp.microsoft.com/bussys/sitesrv/sitesrv-public/fixes/usa/siteserver3/sp2/x86/adsi/ads.exe) on the machine SQL Server is installed.
• You must install Active Directory, (http://www.microsoft.com/windows).
  You can also use
  -or-
  
• Exchange Server (http://www.microsoft.com/exchange)
  

Step by Step Instructions
======================
In to add a linked server, do the following:

1. Run the Query Analyzer
   
2. Logon the the SQL Server machine.
   
3. Execute the following line.
   /***********************************************************/
   sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
   go
   /***********************************************************/

This tells SQL Server to associate word 'ADSI' with ADSI OLE DB provider - 'ADSDSOObject'

Following is an example scenario of common operations:
======================================================

/***********************************************************/
SELECT * FROM OpenQuery(ADSI,'<LDAP://DC=Microsoft,DC=com>;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree')
/***********************************************************/

Note: you should change the DC=.., DC=.. accordingly. This query asks for all users in the 'Microsoft.com' domain.

You may also use the ADSI SQL Dialect, for example:

/***********************************************************/
SELECT * FROM OpenQuery( ADSI, 'SELECT name, adsPath FROM ''LDAP://DC=Microsoft,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''')
/***********************************************************/

Creating, Executing a View
==========================

You may create a view for data obtained from Active Directory. Note that only the view definition is stored in SQL Server, not the actual result set. Hence, you may get a different result when you execute a view later.

To create a view, type and execute:

/***********************************************************/
CREATE VIEW viewADUsers AS
SELECT * FROM OpenQuery(ADSI,'<LDAP://DC=Microsoft,DC=com>;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree')
/***********************************************************/

To execute a view, type

/***********************************************************/
SELECT * from viewADUsers
/***********************************************************/

Heteregenous Join between SQL Server and Active Directory


Create a SQL table, a employee performance review table

/***********************************************************/
CREATE TABLE EMP_REVIEW
(userName varChar(40),
reviewDate datetime,
rating decimal )
/***********************************************************/

Insert few records

/***********************************************************/
INSERT EMP_REVIEW VALUES('Administrator', '2/15/1998', 4.5 )
INSERT EMP_REVIEW VALUES('Administrator', '7/15/1998', 4.0 )
/***********************************************************/

Note: You can insert other user names.

Now join the two

/***********************************************************/
SELECT ADsPath, userName, ReviewDate, Rating
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name
/***********************************************************/

Now, you can even create another view for this join,

/***********************************************************/
CREATE VIEW reviewReport
SELECT ADsPath, userName, ReviewDate, Rating
FROM EMP_REVIEW, viewADUsers
WHERE userName = Name
Advanced Operations
/***********************************************************/

You may log-on as different user when connecting to the Active Directory.
To specify the alternate credential.

Example:

/***********************************************************/
sp_addlinkedsrvlogin ADSI, false, 'MICROSOFT\Administrator', 'CN=Administrator,CN=Users,DC=Microsoft,DC=com', 'passwordHere'
/***********************************************************/

This line tells Distributed Query that if someone logs on in SQL Server as 'Microsoft\Administrator', the Distributed Query will pass the 'CN=Administrator,CN=Users, DC=Microsoft, DC=com' and 'passwordHere' to ADSI as the credentials.

To stop connecting as an alternate credential, type:

/***********************************************************/
sp_droplinkedsrvlogin ADSI,'MICROSOFT\Administrator'
/***********************************************************/

The following links may also be helpful:
=========================================

http://msdn.microsoft.com/developer/news/feature/120197/Exchange/adsi.htm

http://msdn.microsoft.com/library/sdkdoc/adsi/ds2cli_9x9u.htm#_ds_formal_grammar_for_the_sql_queries

http://msdn.microsoft.com/library/sdkdoc/adsi/ds2intro_0ckz.htm