Linked Server - Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Question

I created a linked server between a SQL 2008 R2 (SP3) and a SQL 2019 (RTM). It uses a domain account, which has been set to the "sysadmin" server role on both servers. When I run a query like this using SSMS 2008 R2 (v10.50.6560.0): SELECT * FROM [LinkedServer].[DBName].[dbo].[TableName] I get back records. However when I run the same query, but connect to that same server using SSMS v18.9.2 (v15.0.18386.0), I get:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

In both scenarios, I am RDPing into the machines.

Answer 1

Hi @Mark Sanchez ,

In this scenario, client make tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as 'ANONYMOUS LOGON'.

The workaround here is:
a. ask your domain administrator to manually register SPN if your SQL Server running under a domain user account.
b. use NP connection.
c. change your sql server to run under either localsystem account or networkservice account.

Here, a is recommended.
For more information, please see:
https://learn.microsoft.com/en-us/archive/blogs/sql_protocols/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The version of SSMS should not really have anything to do with it. But it matters if you logged into Windows on the machine where the linked server is defined, or you are logged on to a different machine. In the second case, there is a double-hop and a couple of things has to be set up.
The service account for SQL Server must be trusted for delegations, there must be correct SPNs, and the it helps if the linked server is set up with FQDN.

The short name here is Kerberos, of which I am by no means an expert, but at least I can share you the basics. You admin folks may be able to help you with the rest.

Answer 3

Microsoft venez me chercher je réside a Montréal et je suis a armé du salut.Microsoft come get me out am living at Montreal at the salvation army