One of my blog reader told that they are having trouble with linked server to AD. SQL SERVER – How to Query Active Directory Data Using ADSI / LDAP Linked Server. Let us learn about error related to OLE DB Provider.
They said that they are trying to use a linked server to run an LDAP query on an AD in another domain. Below us the query which works fine in one domain
SELECT * FROM OPENQUERY (ADSI, 'SELECT * FROM ''LDAP://OU=Power,OU=Non Fileshare,OU=Domain Local Groups,OU=CA Groups,DC= CA,DC=ad '' ') AD
They told that they can successfully look up items in the first domain, but when you run the same LDAP query for the second domain in a separate forest they get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “ADsDSOObject” for linked server “ADSI” reported an error. The provider indicates that the user did not have the permission to perform the operation.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query “SELECT *
FROM ‘LDAP://OU=Power,OU=Non Fileshare,OU=Domain Local Groups,OU=CA Groups,DC= CA,DC=ad’
” against OLE DB provider “ADsDSOObject” for linked server “ADSI”.
While looking at their query and my blog, I found that the linked Server was missing the domain controller name. We modified the query according to below syntax
As soon as we kept the domain controller name, it worked.
Reference: Pinal Dave (https://blog.sqlauthority.com)