Secured connection to AD with linked server (LDAPS)

  • tharn31

    SSC Rookie

    Points: 32

    Hi,

    Our system team warn us they detected unsecured connections when one of our linked server try to connet to the AD.

    We managed to find the concerned request :

    SELECT * FROM OPENROWSET('ADSDSOObject', 'adsdatasource';'LDAP_LOGIN';'Password',

    ' SELECT SAMAccountName FROM ''LDAP://ServerName''

    WHERE SAMAccountName = ''NameTest'' and objectClass = ''user'' ')

    After some research, we should use a syntax like

    LDAPS://ServerName:636

    But we got the following :

    for execution against OLE DB provider "ADSDSOObject" for linked server "null"

    We tried

    LDAP+636 - KO

    LDAPS+636 - KO

    LDAPS without port - KO

    Our System team have also tested ad-hoc queries, including in the query all the domain information such as domain user, password, OU, CN, etc with the same result.

    Also tried to import also the AD certificate in the trusted store of the server, same error.

    Any clues about this problem ?

    Thanks,

     

  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22904

    We do not use LDAPS at my workplace.  But, after a quick google I found this post:

    https://stackoverflow.com/questions/45224506/how-to-query-ldap-over-ssl-from-sql-server

    but it sounds like you already tried that.

    Could you post the full error (stripping out any PII)?  I know if I run it with an account I know doesn't exist, I get the following:

    The OLE DB provider "ADSDSOObject" for linked server "(null)" reported an error. The provider indicates that the user did not have the permission to perform the operation.

    That is followed with the query I ran, but that part I don't need to share, especially since it has the password in plain text!

    The error makes sense as I know that the user doesn't exist and therefore shouldn't be able to query AD.  But if I use a valid user (such as myself  in the format DOMAIN\USERNAME), it comes back correctly.

    Once you get this sorted out, I recommend changing that to use a linked server where you can secure the password rather than have it in plain text.

    Another thought - are you using the name of the server that is associated with the certificate?  Certificates can have multiple names, but the common practice is  to use the fully qualified domain name.  So if your domain was google.com and your server was AD1, for the server name you may need to use "AD1.google.com".  On the other hand, if the certificate DOESN'T contain the domain,you would need to use "AD1" for the server name.

    • This reply was modified 1 month ago by  Mr. Brian Gale. Reason: added another thought on why it may be failing

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply