OLE DB Provider for Microsoft Directory Services

OLE DB Provider for Microsoft Direc...

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

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. The maximum number of objects that can be returned by a query using this provider is 1000.

To create a linked server against Windows 2000 Directory Service

Create a linked server by using ADSDSOObject as the provider_name and adsdatasource as the data_source argument of the sp_addlinkedserver system stored procedure, such as:
```
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 by using SQL Server Security Delegation. Because the self-mapping is created by default for linked servers created by running sp_addlinkedserver, no other login mapping is required.

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

Note:
When possible, use Windows Authentication.

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.

Note:
The Microsoft OLE DB Provider for Microsoft Directory Services does not support LDAP queries directly from Integration Services. Instead, create a linked server to Microsoft Directory Services and use OPENQUERY as described in this topic, or use a Script Task. For an example, see Querying the Active Directory with the Script Task.

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.adventure-works.com. The command inside the OPENQUERY function is an SQL query against the directory to return the Name, SN, and ST attributes of objects that belong to Class contact at a specified hierarchical location (OU=Sales) in the directory. The view then can be used in any SQL Server query.

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=adventure-works,DC=com''
   WHERE objectCategory = ''Person'' AND
      objectClass = ''contact''')
GO
SELECT * FROM viewADContacts

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

Concepts

Other Resources

Help and Information