Article ID: 299410
Article Last Modified on 4/11/2006
APPLIES TO
- Microsoft Active Directory Service Interfaces 2.5
- Microsoft Active Directory Service Interfaces 2.5
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft Active Directory Service Interfaces 2.5
This article was previously published under Q299410
SUMMARY
This article provides information about using the Active Directory Services Interfaces (ADSI) OLEDB provider from SQL Server to perform a distributed query.
MORE INFORMATION
Requirements and General Information
A distributed query allows the developer to populate a SQL Server table or view with data in the Active Directory or other Lightweight Directory Access Protocol (LDAP) server. In order to perform a distributed query by using ADSI, the server must be running SQL Server 7.0 or later. ADSI is a system component of Windows 2000 so no additional installation in required on that operating system. If the server's operating system is Windows NT 4.0, the Active Directory Client Extension (DSClient) for Windows NT 4.0 must be installed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
288358 How to install the Active Directory Client Extension
A step-by-step procedure for performing a distributed query using ADSI can be found under "Joining Heterogeneous Data" in the following MSDN Library article:
Joining heterogeneous data
http://msdn.microsoft.com/library/en-us/adsi/adsi/joining_heterogeneous_data.asp
When the OPENQUERY command is issued, SQL Server will invoke ADSI's OLEDB provider, ADsDSOObject, which was set up in the sp_addlinkedserver function. The provider will pass the query to underlying ADSI lDirectorySearch interface functions. These ADSI functions will make the necessary LDAP calls to the desired server. The responses from the server will then be passed back up the same layers to SQL Server.
A query in either the LDAP or SQL dialects can be used. Additional information on searching with ADSI's OLEDB provider can be found under the following MSDN article:
Searching Active Directory
http://msdn.microsoft.com/library/en-us/ad/ad/searching_active_directory.asp
A discussion of the two dialects is found under the following subtopic in MSDN:
If an asterisk (*) is specified for the attribute list, the ADSI OLEDB provider returns a recordset that contains only the ADsPath of each entry. ADsPath is a client-side generated attribute and is guaranteed to be returned as a singled valued attribute.
A serverless bind string is one that does not contain the name or IP address of the target server. Serverless binds are used to locate the best Windows 2000 domain controller to handle the request. Serverless binds cannot be used when the target server is not a Windows 2000 domain controller for the default domain. For additional information on serverless binds, see
Serverless binding and RootDSE
http://msdn.microsoft.com/library/en-us/ad/ad/serverless_binding_and_rootdse.asp
In the example in the white paper, the LDAP server will be contacted by using the credentials under which the OPENQUERY call was made. Depending upon how your application is designed, this may or may not be desirable. If SQL Server authentication instead of Windows Authentication is used, the SQL credentials will be conveyed to the directory. Authentication problems will also occur if the target directory service doesn't support Windows Authentication. For more information on using ADSI to query these LDAP servers, click the following article number to view the article in the Microsoft Knowledge Base:
251195 How to use ADSI to query a third-party LDAP server
If it is not desirable to contact the directory by using the same credentials as the OPENQUERY call, a sp_addlinksrvlogin can be used to alter the identity under which a remote server is contacted. When the sp_addlinksrvlogin is implemented ADSI will invoke a simple bind to the target directory service with the information specified in the rmtuser and rmtpassword parameters. It is important to note that a simple bind will convey the credentials to the directory server in clear text. This has the advantage that it will be understood by many directory servers, but a drawback is that the user name and password will be visible on a network monitor. For more information on simple binds see:
ldap_simple_bind_s
http://msdn.microsoft.com/library/en-us/ldap/ldap/ldap_simple_bind_s.asp
Once added, the sp_addlinksrvlogin can be removed by calling sp_droplinkedsrvlogin.
Limitations
The process of using the OPENQUERY statement to pull information from an LDAP server does suffer from some limitations. The limitations can be circumvented in some cases, but in others the application design must be altered. An external application or COM object that uses ADSI to retrieve the information from the LDAP server and then build a table in SQL by using ADO or other data access methods is another viable method.
The first limitation is that multivalued properties cannot be returned in the result set to SQL Server. ADSI will read schema information from the LDAP server that defines the structure and syntax of the classes and attributes used by the server. If the attribute that is requested from the LDAP server is defined in the schema as being multi-valued it cannot be returned in an OPENQUERY statement.
It is typical for a directory server to enforce a server limitation on the number of objects that will be returned for a given query. This is to prevent denial-of-service attacks and network overloading. To properly query the directory server, large queries should be broken up into many smaller ones. One way to do this is through a process called paging. While paging is available through ADSI's OLEDB provider, there is currently no way available to perform it from a SQL distributed query. This means that the total number of objects that can be returned for a query is the server limit. In the Windows 2000 Active Directory, the default server limit is 1,000 objects. More information on paging can be found under the following MSDN Library topic:
Paging with IDirectorySearch
http://msdn.microsoft.com/library/en-us/adsi/adsi/paging_with_idirectorysearch.asp
For more information about how to modify the server limit for maxPageSize by using NTDSUtil, click the following article number to view the article in the Microsoft Knowledge Base:
315071 How to view and set LDAP policy in Active Directory by using Ntdsutil.exe
REFERENCES
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
187529 How to use ADO to access objects through an ADSI LDAP provider
Further information, including a sample, can be found in the ADSI section of the Platform SDK, which is available from the following Microsoft Web site:
Additional query words: adsi sql dq "query analyzer"
Keywords: kbinfo kbdswadsi2003swept KB299410
