locked
Linked Server issu RRS feed

  • Question

  • Hi,

    I have 2 standalone SQL servers in the same AD domain. Let say SQL01 and SQL02 for the purposes of the post (SQL Server 2017).

    I am trying to add SQL02 as a linked server from SQL01. When I do this directly from SQL01 it works. I am using the logons current context to connect.

    Obviously, we don't want developers accessing this directly from SQL so have an RDS server that they access Management Studio from. When they test the connection they get the below error:

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient Data Provider)

    I assumed this was an SPN issue so downloaded the Kerberos Configuration tool. When it analysed the SPNs it says mismatched. Is this because we are using the "NT Service" accounts for login? Eg should we be using proper service accounts for the SQL Services?

    Or is It something completely different? 

    Wednesday, January 16, 2019 4:04 PM

All replies

  • Hi MessageUndeliverable,

     

    According to your description, my understanding is that you can connect to SQL Server normally on SQL01.But when you use the client on RDS server to connect to SQL server, it has the error you described.If anything is misunderstood, please tell me.

     

    You need to first confirm whether the client login account you are using now is delegation enabled. You should uncheck the "Account is sensitive and cannot be delegated" checkbox of user properties in AD.

     

    >>Is this because we are using the "NT Service" accounts for login?

     

    "NT Service"  is a virtual account in SQL Server.This account has the  permission to automatically register and log out of the SPN. If you use your domain account as a starter account, you will need to either manually register the SPN or give your domain account the permission to automatically register the SPN.

     

    For more details, please see the blog: https://stackoverflow.com/questions/12462674/sql-server-returns-error-login-failed-for-user-nt-authority-anonymous-logon

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by pituachMVP Thursday, January 17, 2019 5:44 AM
    Thursday, January 17, 2019 5:26 AM