Requirements and General InformationA 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:
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:
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
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:
Once added, the sp_addlinksrvlogin can be removed by calling sp_droplinkedsrvlogin.
LimitationsThe 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:
The above link also has information about where to get the download for various Windows SDKs at the bottom of the page under the sectiont titled: "What Others Are Downloading".
Article ID: 299410 - Last Review: Jun 19, 2009 - Revision: 1