Linked server connection does not work from local

Anonymous
2020-09-03T16:31:20.783+00:00

I have created a linked server from server A to server B. I am using the security setting "Be made using login's current security context". I am able to query the linked server from SSMS on Server A. When I use SSMS on my lptp, I can query all databases on server A. but the query to linked server B fails with the following error message:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I tried creating a mapping for NT AUTHORITY\ANONYMOUS LOGON to my logon, but it throws permission error.

Please advise what needs to be done to use the linked server from lptp. Not everyone who needs to use the linked server has access to remote desktop on on Server A, but have access to query server A.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,490 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SQLZealots 276 Reputation points
    2020-09-03T16:58:32.48+00:00

    Try the steps in the below link:
    https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189580(v=sql.105)?redirectedfrom=MSDN

    --------------------------------------------------------------------------------------------------------------------

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
    Blog
    LinkedIn

  2. Erland Sommarskog 67,721 Reputation points Microsoft MVP
    2020-09-03T21:50:59.873+00:00

    This is a typical double-hop problem. You log on to your laptop which passes you login token to server A. When you access the linked server, server A must be able to pass your login token to server B. This requires that you have Kerberos up and going. Or rather that your Windows admin has it up and running.

    I am mot very good at Kerberos myself, so I am not able to give a recipe. I think that the link that SQLZealots gave you is a start, but may not take you all the way.

  3. CathyJi-MSFT 20,656 Reputation points Microsoft Employee
    2020-09-04T02:41:05.757+00:00

    Hi @DeepKaur-3078,

    As Erland mentioned, your issue is typical double-hop problem. The issue is that SQL A does not pass login credentials on to SQL B. Why doesn’t SQL A pass through the credentials? Because the service account under which SQL A(middle server) is running was not configured as below screenshot. Active Directory User and Computer > User> service account >Properties > Delegation. Make sure Advanced Features are being shown, or the delegation tab will not show up.

    22583-annotation-2020-09-04-102317.jpg
    Please refer to below links to get more details information.

    Double-Hop Linked Servers
    How to link two SQL Server instances with Kerberos
    SQL Server: Curse Of Linked Server Security And The Fix: Pass-through Authentication

    Best regards,
    Cathy

    ===============================================

    If the response helped, do "Accept Answer" and upvote it.