6

Here is an odd issue that is causing me to lose all my hair.

Every few days or so our delegated authentication from one SQL server to another fails with the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The SQL server logs show: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.1.x.x] Error: 18456, Severity: 14, State: 5."

Our environment consists of 6 Domain controllers ranging from Windows 2012 to Windows 2019, The SQL servers are Windows Server 2016 running SQL Server 2017.

After a few hours the issue goes away by itself, unless the Kerberos tickets are purged on either one of the affected SQL servers which resolves the issue in about 15 minutes.

At first we thought it was an issue with a particular SQL server so we replaced it, however over the last year it has affected several servers with several different delegated connections.

To date we have tried updating SQL server, deleting and recreating SPNs, upgrading the bulk of our DCs to Windows 2019, updating the domain functionality level, and multiple Microsoft tickets. We have a suspicion that leaving SSMS sessions to the SQL Servers overnight triggers the Kerberos delegated connections to fail more often but it may be superstition at this point.

Any ideas? It works 80% of the time which has made it especially tricky to hunt down. The domain controllers appear to be syncing normally, but I'm not sure what to look for in the case that the DCs are the problem.

Oddly, we are able to connect to the individual servers via protected AD accounts during the outage and the log ins are listed as Kerberos, it's just the AD delegated permissions that stop working.

We do the DC maintenance over the weekends and the issue seems to pop up at any time of any day.

Improve this question
2
  • "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." Indicates that Kerberos likely isn't working at all, and your Authentication is falling back to NTLM.
    – AMtwo
    Oct 27, 2020 at 2:58
  • After several experiments, it turns out that SSMS is in fact breaking it! For some reason, any time a member of our Dev team connects to one of the SQL servers via SSMS from a non-domain machine over a VPN all the delegated linked permissions fail until the kerberos tickets or purged from the server. We have been able to reproduce this on multiple test servers. This explains why the problem got so much worse after COVID-19! Aside from making a policy to not connect to our SQL servers from non-domain machines, any ideas on why this might be happening or what could be done to future proof?
    – Rachel
    Nov 9, 2020 at 14:33

2 Answers 2

Reset to default
3

If your SQL Servers are clustered, and their service accounts are configured to allow them to manage their own Service Principal Names, you might be running into a known issue.

In an environment with physically distanced Domain Controllers, a planned failover event can cause the Kerberos delegation for double-hop authentication to fail with symptoms matching the error message in your question.

When SQL Server shutdown occurs, the SQL Server service attempts to deregister the Kerberos SPN with a Domain Controller. Then cluster failover occurs, and the SQL Server service is started up on one of the other nodes. This can happen quickly enough that the other Domain Controllers in the organization haven't yet received the synchronization for the removal of the SPN. At this point, SQL Server will try to register the SPN, but can't because it hasn't yet been removed on all Domain Controllers. As a result, the attempt at registering the SPN fails. SQL Server starts up normally, accepting auth requests via NTLM and no one is any wiser unless someone attempts double-hop authentication.

There isn't a great solution to this, other than having permanent SPN entries created in the Active Directory domain, and preventing SQL Server from managing them automatically.

Improve this answer
1
  • Our SQL servers are clustered, however our failover events happen very rarely and the SPNs have already been manually created.
    – Rachel
    Nov 9, 2020 at 14:27
1

Does your issue happen to coincide with domain controller maintenance? At my organization, we ran into this problem and started proactively changing our authentication servers before maintenance to avoid expiring tickets. Our friends on serverfault tipped us off to this solution.

Improve this answer
1
  • Not that we have seen, we do the DC maintenance over the weekends and the issue seems to pop up at any time of any day. How did you change your authentication servers?
    – Rachel
    Oct 27, 2020 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.