The Test connection to the Linked Server failed.

Question

• Summary: We are using Windows 11 device "B" to connect to an instance "A" using SQL management Studio. It connects to "A" perfectly fine. Now, when we try to test connection with a Linked Server "C" created on instance "A" we get this error: "The Test connection to the Linked Server failed". "Login failed for User "NT AUTHORITY\ANONYMOUS LOGON". (Microsoft SQL Server, Error:18456)" This works fine with any Windows 10 device or from a Jump Server. Furthermore, the databases on Linked Servers are not visible.
• History: The issue started with Windows 11 and not working for any user.
• Troubleshooting:
  • We tried to connect with different versions of Management Studio, but same issue.
  • Followed all the solutions on following link:
    https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16
  • This works fine on any Windows 10 device.
  • Tried to follow, many articles for SQL issue, but this seems to be a bug in Windows 11.
  We want to know if anyone else are facing the same issue. Any suggestions or solutions are appreciated.

Answer 1

There is not much information in your post to work from in your post. But if I understand it correctly, you log in on workstation A and connect to server B with SSMS and then you try to set up a linked server to server C.

You don't say how you set up the linked server, nor which error message you get. But I assume that you set up the linked server with self-mapping.

Now, this is not a bug in Windows 11 - after all, the linked server is on machine B, so why would your client matter?

This is a double-hop situation. That is, server B must be able to pass the Kerberos ticket further to server C.

The first check is to run

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

It needs to return KERBEROS for the double hop to work. If if returns NTLM, this what you need to fix. As for how, that's outside my expertise, but may be someone else can chime in.