Active Directory Service Interfaces 2.5

With Microsoft SQL Server 7.0, Active Directory, and ADSI, you will be able to get the data from Active Directory into SQL Server tables or views. You'll be able to manipulate it with other SQL Server tables, views, or any other tables obtained from other OLE DB data sources. One interesting scenario is to join data between Active Directory and the SQL Server. For example, Human Resource data may reside in the SQL Server database, while the account and groups information is stored in Active Directory. An administrator can create a quick report which combines the data from those two sources into one single SELECT statement. Another scenario is to join information from Exchange, Active Directory, and Index Server.

SQL Server Distributed Query, OLE DB, and ADSI technologies make these scenarios possible.

Requirements

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) on the machine on which SQL Server is installed.
You must install Active Directory, (http://www.microsoft.com/windows). You can also use Exchange Server (http://www.microsoft.com/exchange).

Step-by-Step Instructions

In SQL Server:

Run the Query Analyzer (Start | Programs | Microsoft SQL Server 7.0)
Logon to the SQL Server machine.
Execute the following line (by highlighting it and pressing CTRL+E)

sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go

This tells SQL Server to associate the word 'ADSI' with the ADSI OLE DB provider - 'ADSDSOObject'.
Now we are ready to access Active Directory from SQL Server. Type and execute:

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 the following:

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 the following:

SELECT * from viewADUsers

Heterogeneous Join Between SQL Server and Active Directory

Create an employee performance review table using SQL.

CREATE TABLE EMP_REVIEW
(
userName varChar(40),
reviewDate datetime,
rating decimal
)
Insert a 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
Viola! You should get the result from both SQL Server and Active Directory.
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 Active Directory. To specify the alternate credential, type the following:

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 the following:

sp_droplinkedsrvlogin ADSI,'MICROSOFT\Administrator'

You can get the SQL script source from here.