Share via

OLE DB Provider for Microsoft Directory Services

  • Article

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.

See Also

Concepts

OLE DB Providers Tested with SQL Server
Distributed Queries

Other Resources

sp_addlinkedsrvlogin (Transact-SQL)
sp_addlinkedserver (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Added the sentence stating that 1000 objects is the maximum number of objects that can be returned by a query using this provider.
  • Added the Note about querying Microsoft Directory Services from Integration Services.