Has anybody successfully queried LDAPS (LDAP over SSL/TLS) from SQL Server? How did you do it?
A client has asked us to set up our program on a network that uses LDAPS. I’ve used LDAP queries successfully at many other locations, but it’s not clear whether this will work with LDAPS, or what the syntax is. I would expect it to work, but not sure how to set it up.
As a test, we tried setting up LDAPS on our own network. We can connect using ldp.exe, but attempting to query it from SQL consistently fails with “Msg 7321, Level 16, State 2…”
- can a SQL query connect to LDAPS?
- Should I specify the port?
- Does the server certificate need to be imported into SQL Server, or elsewhere?
- Are there other extra setup steps needed beyond those normally needed for querying LDAP?
Here’s the query that normally works, which I’m trying to modify for LDAPS.
SELECT COUNT(*) FROM OPENROWSET('ADSDSOObject', 'adsdatasource';
'domain user here'; 'domain password here',
'SELECT SAMAccountName FROM
''LDAP://192.168.0.105
WHERE SAMAccountName = ''test.test'' and objectClass = ''user''
')
1条答案
按热度按时间5rgfhyps1#
Partial success - with the help from a few other stack overflow posts, I got the syntax figured out and am able to successfully query the domain server from itself.
The correct syntax is:
Note that the protocol name "LDAP" is case-sensitive.
Edit: figured out how to query the domain controller from the database server. I exported certificate from the domain controller, and imported it into the data server's Trusted Root Certification Authorities store.
I believe that if the certificate came from a CA rather than being self-signed, it would go in the Personal store rather than the Trusted Root CA store.