Wednesday, October 01, 2008

Adding an OpenLDAP server as SQLServer linked server

... and querying it.

You can link to an OpenLDAP server from SQLServer by:

1. Running the following stored procedures:

Use master
go
exec sp_addlinkedserver
'ADSI_OpenLDAP', --name of the linked server
'My OpenLDAP Linked server', --description
'ADSDSOObject',
'adsdatasource'
go
exec sp_addlinkedserverlogin
'ADSIOpenLDAP',
False,
'sa',
'your_root_dn',
'your_root_dn_password'
go

Note that this skips each and every proper security management practice.

Now you can query the linked server this way:

select
*
from
OpenQuery(ADSI_OpenLDAP,
'SELECT
*
FROM
"LDAP://localhost/dc=mydomain,dc=com"
WHERE
objectClass="organizationalUnit"')

which will return all organizational units.
Or:

select
*
from
OpenQuery(ADSI_OpenLDAP,
'SELECT
*
FROM
"LDAP://localhost/ou=myou,dc=mydomain,dc=com"
WHERE
objectClass="InetOrgPerson"')

which will return all InetOrgPerson (i.e. users) from the specified organizationalUnit (ou)
here called "myou".
This is also available as a PDF file on my old site