Errors and optional solutions while querying LDAP

ERROR:

An error occurred while preparing the query ... for execution against OLE DB provider ADSDSOObject for linked server.

Optional Solutions/problems:

Add the server name to the LDAP address: LDAP://CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 --> LDAP://SERVERNAME/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2.

ERROR:

The OLE DB provider "ADSDSOObject" for linked server "adsi_test" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Optional Solutions/problems:

Run the query with user that have relevant permissions in the LDAP.

ERROR:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsi_test".

Optional Solutions/problems:

Maybe the data from the LDAP is wrong.

ERROR:

An error occurred while preparing the query "

...........

" for execution against OLE DB provider "ADSDSOObject" for linked server "adsi_test". 

Optional Solutions/problems:

- Wrong syntax of the query.

- Maybe there is a problem to run the query from other machine that it's not the SQL Server.

Querying LDAP through SQL Server

In order to querying LDAP (Active Directory, LDS, etc.) through SQL Server, we need to create a Linked Server to the LDAP, and use OPENROWSET statement to select data from it.

-- create new linked server

exec sp_addlinkedserver  

  @server = 'adsi_test',-- linked server name

  @srvproduct = 'LDAP',  

  @provider ='ADSDSOObject',-- provider name

  @datasrc = ''  

go

-- create linked server login for sa

exec sp_addlinkedsrvlogin

  @rmtsrvname = 'adsi_test',-- linked server

  @useself = false,

  @locallogin = 'sa',-- sqlUser

  @rmtuser = 'domain\master_user',-- domainName\userName

  @rmtpassword = 'password'-- domain User Password

go

-- 2 options of query from LDAP:

--1:

select * FROM OPENQUERY(adsi_test, '<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;(&(name=*));name,distinguishedName;subtree

')

--2:

select *  

from  openquery(adsi_test, '  

select  userPrincipalName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person'' and objectClass = ''user''  

')

• CN is containers that are usually defined as users, contacts, groups.
• OU is organizational units that usually contain objects such users, contacts, groups and other OUs.
• DC is domain containers that are created by separating the full internal domain name.
• LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 = LDAP:// SERVER NAME : port / Root of the LDS/AD tree
• (&(name=*)) = WHERE NAME = * (no filter by name)
• ;name,distinguishedName - fields to select from LDS
• ;subtree - include all the tree from the declared root

Examples:

-- select all groups:

select distinguishedName from  openquery(adsi_test, '  

select name, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Group''  

and  objectClass = ''Group''

')

-- select all users of group 'Managers' in AAA

select *

from  openquery(adsi_test, '  

select objectGUID, name, givenname, sn, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person''  

and  objectClass = ''user'' 

AND memberOf = ''cn=Managers,CN=AAA,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''

')

-- list of the groups that the user itaig is a member of: (the function 'member')

select * FROM OPENQUERY(adsi_test, 

'<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;

(&(objectCategory=group)(member=CN=itaig,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2));name,distinguishedName,objectGUID

')


Search Filter Syntax (msdn):
http://msdn.microsoft.com/en-us/library/Aa746475