SQL query to linked server error: "Msg 18456, Level 14, State 1, Line 901 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Question

I have two SQL servers that both run their instances from a domain service account. The domain service account has delegated privilege's to "any service". I have the correct SPN's created for both SQL servers, when I run "SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;" I see TCP / KERBEROS, so the SPN's appear to be working (otherwise I might see a NTLM request instead of Kerberos). Some of my users can run the linked server query's and it works fine (myself included), others get the Anonymous login error, so for some reason it's not passing their logged in credentials across the SQL linked server. I have a dev environment where users are setup identically, and this issue does not exist for any users, so this is not a permissions issue.

This error can also be replicated outside of a linked server query by simply running SMS and right-click the Linked server and "test connection". They will then see the anonymous logon error, they have the same access I do and I can right-click and "test connection" and I get a "success" response.

I've checked my DC's and I don't appear to be having any replication issues, or Kerberos issues that I can see. Hoping someone has some other idea's of things I can try to get this working.

Answer 1

I have found David Postlewaite's presentation on Kerberos very instructive. I'm still a bit confused and not in state to help others, but here is a link to David's presentation: https://www.youtube.com/watch?v=oY9-qctTMwQ.

Answer 2

Hi @JoFri ,

Welcome to Microsoft Q&A!
Please refer to this similar thread: https://learn.microsoft.com/en-us/answers/questions/562781/linked-server-login-failed-for-user-39nt-authority.html
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.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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 3

Some more info:
I'm troubleshooting the DC/Kerberos side a bit more. When I run a DCDIAG /q I do see some Kerberos errors, but I'm running into more walls trying to figure out why these occur and where theyr'e coming from:

Here's a screenshot of the original error the user gets when testing the Linked server from within SQL Manamgement Studio:

Here's the details section from the screenshot:

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

The test connection to the linked server failed.

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

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()

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

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient Data Provider)

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

For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

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

Server Name: SQLSERVERNAME
Error Number: 18456
Severity: 14
State: 1
Line Number: 1

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

Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

This issue is a thorn in my side, and I would love to find someone who knows this stuff inside out and can help me figure out why this continues to happen.

Answer 4

I finally figured out the issue... I had a read-only domain controller in a separate site. For some reason credentials weren't caching correctly at the RODC, All my groups and even server accounts were setup to replicate and sync creds... long story short, I demoted the RODC and created a new full DC at that site, with a full DC up and running at the site, the Kerberos issues are all resolved.