OLE DB Provider for Microsoft Directory Services

The Microsoft OLE DB Provider for Microsoft Directory Services provides access to information in the Microsoft® Windows® 2000 Directory Service. This OLE DB Provider supports two command dialects, LDAP and SQL, to access the directory service and return results in a tabular form that can be queried using SQL Server distributed queries.

To create a linked server against Windows 2000 Directory Service

• Create a linked server using ADSDSOObject as the provider_name and adsdatasource as the data_source argument of the sp_addlinkedserver system stored procedure.

  EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 
  'ADSDSOObject', 'adsdatasource'
  GO
  

For Windows authenticated logins, the self-mapping is sufficient to access the directory using SQL Server Security Delegation. Because the self-mapping is created by default for linked servers created through sp_addlinkedserver, no other login mapping is necessary.

For SQL Server authenticated logins, suitable login/passwords can be configured for connecting to the directory service using the sp_addlinkedsrvlogin system stored procedure.

Querying the Directory Service

The Microsoft OLE DB Provider for Microsoft Directory Services supports two command dialects, LDAP and SQL, to query the Directory Service. The OPENQUERY function can be used to send a command to the Directory Service and consume its results in a SELECT statement.

The following example shows creating a view that uses OPENQUERY to return information from the directory at the server ADSISrv whose domain address is sales.northwind.com. The command inside the OPENQUERY function is an SQL query against the directory to return the Name, SN, and ST attributes of objects belonging to Class Contact at a specified hierarchical location (OU=Sales) in the directory. The view then can be used in any SQL Server queries.

CREATE VIEW viewADContacts 
AS
SELECT [Name], SN [Last Name], ST State
FROM OPENQUERY( ADSI, 
   'SELECT Name, SN, ST
   FROM ''LDAP://ADSISrv/ OU=Sales,DC=sales,DC=northwind,DC=com''
   WHERE objectCategory = ''Person'' AND
      objectClass = ''contact''')
GO
SELECT * FROM viewADContacts

For more information about the LDAP and SQL dialects, see Microsoft Active Directory Services documentation.