Directory Services

Joining Heterogeneous Data

Typical organizations store data in multiple heterogeneous databases. Human Resources data may be stored in SQL Server, while account management data is stored in the directory. Other data may be stored in proprietary formats.

With, SQL Server 7.0 Distributed Query, ADSI, OLEDB, and Active Directory, it is possible to join data from Active Directory to data in SQL Server. You can even create a view of the joined data.

To join Active Directory Data with SQL Server Data

  1. Run the Query Analyzer (Start | Programs | Microsoft SQL Server 7.0)
  2. Log on to the SQL Server computer.
  3. Execute the following line (by highlighting it and pressing CTRL+E):
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
go

This directs SQL Server to associate the word "ADSI" with the ADSI OLE DB provider, "ADSDSOObject."

You can now access Active Directory from SQL Server.

Type and execute:

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

You can also use the ADSI LDAP dialect. For example:

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