2

Below is an abbreviated query I am trying to execute against LDAP using a linked server:

select * from openquery(ADSI,'')

The query runs fine, except when I change the "Domain controller: LDAP server signing requirements" security setting to "Require signing" on the LDAP server. When I do that, I get the following error:

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

The linked server ADSI is created using a domain account. Also, we would prefer not to use OPENROWSET if we can help it.

Kevin Barker
  • 63
  • 6

2 Answers2

1

Are you using TLS/SSL?

If you "Require signing", then the LDAP data-signing option must be negotiated unless Transport Layer Security/Secure Socket Layer (TLS/SSL) is being used.

See http://support.microsoft.com/kb/823659 for more information.

May be unrelated, but I noticed there is no explicit 'SELECT * FROM "LDAP://DC=MyDC,DC=MyOtherDC"' in the query portion of OPENQUERY

Good luck

Ben Tennen
  • 445
  • 2
  • 6
0

I had this problem and the solution was to set up a kerberos trust between the SQL server and the domain controller, so it would allow user credentials to be passed-through (aka double-hop).

I used "Active Directory Users & Machines" to find my SQL server and add a SPN allowing credentials to be passed from the SQL server to the LDAP/DC server.

In SQL server, my query looked a little different from the ones shown above:

select sAMAccountName as UserName, displayName, userAccountControl 
from OPENQUERY( ADSI, 'select sAMAccountName, displayName, userAccountControl 
     from ''LDAP://DomainServerName/DC=Domain01,DC=CompanyName01,DC=local'' 
     where objectClass = ''Person''')

I was able to construct the LDAP query using another tool called Softerra LDAP Administrator. You expand (depthwise) util you get to something like ServerName\DC=Corporation01\OU=Office01\CN=Users\CN=Department. If it has the list of users you want, check properties of that folder and your LDAP query will be in the title. It also does a decent job of showing which properties you can select or filter-on.

tgolisch
  • 6,549
  • 3
  • 24
  • 42