Post reply

Secured connection to AD with linked server (LDAPS)

  • June 2, 2020 at 2:06 pm

    #3759385

    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,

     

  • June 3, 2020 at 2:10 pm

    #3760115

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

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

  • June 3, 2020 at 4:01 pm

    #3760147

    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 3 years, 10 months ago by  Mr. Brian Gale. Reason: added another thought on why it may be failing

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • July 21, 2020 at 1:46 pm

    #3771749

    Thank you for your answer Brian.

    After a workshop with Microsoft, our system team got the solution. There were 2 problems.

    • The certificates installed were corrupted on the VM.
    • The syntax was not good.

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

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

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

    Maybe this will help some people.

     

  • July 21, 2020 at 4:01 pm

    #3771799

    Thanks for the followup and solution!

    That is weird about the syntax... I was using your original syntax when I tested it and it worked for me.  But maybe it was a version issue.  I don't have SQL 2014 and I am not sure what AD version we have (I have no visibility into that).

    Glad it is working for you now though.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 5 posts - 1 through 4 (of 4 total)

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