Query AD from sql server 2005

Question

I am attempting to query AD via ldap from within SQL Server 2005 but get the following error:

Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT NAME,MAIL FROM "LDAP:///CN=foo,CN=Users,DC=bar,DC=com"" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

This is after executing the following stored proc:
exec sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADsDSOObject', 'adsdatasource'

Currently I'm running the query on my local SQL Server 2005 instance. I've tried changing the security context to 1) Made Without... , 2) made using the login's current..., and 3) Be made using this security context: specifying my own domain account. Same error with all three.

Not sure if it matters, but "bar" (see ldap query above) is not the domain of either my machine (local sql server instance) or the ldap server.

Any ideas?

Answer 1

The main problem is that the double quotes around the LDAP query need to be doubled single quotes.

The LDAP query can include as server name or IP and/or a LDAP specification.

Some queries that work for me:

select * from openquery (ADSI,'SELECT NAME FROM ''LDAP://ldap.server.name''')

select * from openquery (ADSI, 'SELECT name, sAMAccountName, distinguishedName FROM ''LDAP://DC=mycompany, DC=mytld'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')

select * from openquery (ADSI,'SELECT name, sAMAccountName, distinguishedName FROM ''LDAP://ldap.server.name/OU=ITDept, OU=users, OU=DC, OU=Corporate, DC=mycompany, DC=mytld'' WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')

Once again... there are no double quotes in the above... multiple single quotes.

ADSI needs to be registered/linked with

exec sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

if you have permissions issues you can set the account used under the security property tab on the ADSI linked server.