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.