Linked server connection does not work from local

Question

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.

Answer 1

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.

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.

Answer 2

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.

Answer 3

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