A. This information is supposed to get ADSI working from SQL Server, however your mileage may vary (please provide any feedback on updating/correcting the information here).

In order to get an ADSI linked server to SQL Server 7.0, you will need to install the following pre-requisites. ADSI 2.0 doesn't work with MDAC 2.1 (installed w/ SQL Server 7.0). For more information on the issues of ADSI 2.0 and MDAC 2.1, see:

http://support.microsoft.com/support/kb/articles/q216/7/09.asp

Requirements:
========



1. You must install SQL Server 7.0. For more information about SQL Server, visit http://www.microsoft.com/sql 

2. You must install ADSI 2.5 Runtime on the machine SQL Server is installed :-
http://www.microsoft.com/adsi -or- 
ftp://ftp.microsoft.com/bussys/sitesrv/sitesrv-public/fixes/usa/siteserver3/sp2/x86/adsi/ads.exe


3. You must either use Active Directory (Windows 2000), an NT 4.0 SAM database or Exchange Server as a basis for directory information.


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=use
r));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=use
r));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 helpfull:

====================================

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

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

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