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