question

$$ANON_USER$$ avatar image
0 Votes"
$$ANON_USER$$ asked ErlandSommarskog commented

Linked server connection does not work from local

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLZealots avatar image
0 Votes"
SQLZealots answered

Try the steps in the below link:
https://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ErlandSommarskog commented

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DeepKaur-3078,

Any update for this thread? Did you resolve your issue? If the response helped, do "Accept Answer".

Best regards,
Cathy

0 Votes 0 ·

Hi @Cathyji-msft I am having the same issue in a AZ VM running SQL Server, and I don't have the Active Directory option.


164816-image.png


0 Votes 0 ·
image.png (116.2 KiB)

If you have a problem post a Question, describing your problem from start to end. Don't post your Question as a comment in an existing thread.

0 Votes 0 ·