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
ADSDSOObjectas the provider_name and
adsdatasourceas the data_source argument of the
sp_addlinkedserversystem 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.
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.
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
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.
Help and Information
17 July 2006