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.
Server Name not valid in Connect to Server: Reporting Services
Find in Files command in SQL Server Management Studio
What is the best way to create an enhanced Data Dictionary?
The LDAP query can include as server name or IP and/or a LDAP specification..
SQL Server : How to get rownumber for each common set of values?
Some queries that work for me:.
How to prevent items from going to another page in Reporting Services 2005
SELECT * FROM OPENQUERY (ADSI,'SELECT NAME FROM ''LDAP://*ldap.server.name*''')
newid() vs newsequentialid() What are the differences/pros and cons?
SELECT * FROM OPENQUERY (ADSI, 'SELECT name, sAMAccountName, distinguishedName FROM ''LDAP://DC=*mycompany*, DC=*mytld*'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')
SQL Server ContainsTable not finding partial matches
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''')
Connection string error
there are no double quotes in the above...
multiple single quotes.. ADSI needs to be registered/linked with.
if you have permissions issues you can set the account used under the security property tab on the ADSI linked server..
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'