Intermittent Login Issues with Windows Authentication

Question

I have a strange one. I've been experiencing occasional login errors via windows authentication for the past couple weeks and the network team and I have yet to find a solution.

I am running SQL 2016 on a physical server running Windows Server 2012 R2.

While this has been happening, I've been telling users to wait five minutes and try to login again. If the user waits five minutes, they will (most times) be able to login. I've had instances where I couldn't log in for 15 minutes, then my next login attempt works.

From the SQL error log, I see entries like this:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: XX.XX.XXX.XXX] Error: 18452, Severity: 14, State: 1.

SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. No authority could be contacted for authentication. [CLIENT: XX.XX.XXX.XXX] Error: 17806, Severity: 20, State: 14.

From the server, I see this:

An account failed to log on.

Subject: Security ID: NULL SID Account Name: - Account Domain: - Logon ID: 0x0

Logon Type: 3

Account For Which Logon Failed: Security ID: NULL SID Account Name: A User Account Domain: A Domain

Failure Information: Failure Reason: An Error occured during Logon. Status: 0xC000005E Sub Status: 0x0

Process Information: Caller Process ID: 0x0 Caller Process Name: -

Network Information: Workstation Name: A Computer Source Network Address: - Source Port: -

Detailed Authentication Information: Logon Process: NtLmSsp Authentication Package: NTLM Transited Services: - Package Name (NTLM only): - Key Length: 0

Any ideas what could be causing (sometimes) login failures like this?

Answer 1

SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. No authority could be contacted for authentication.

If you cut up the HRESULT listed, as the error indicates, it maps to: SEC_E_NO_AUTHENTICATING_AUTHORITY

FAILURE (1), FACILITY_SSPI (0x9), Code 0x311

Failure Information: Failure Reason: An Error occured during Logon. Status: 0xC000005E Sub Status: 0x0

If you cut up the HRESULT listed in this one, it maps to: STATUS_NO_LOGON_SERVERS

Both of these error are fairly indicative that the domain controllers and infrastructure around them should be investigated. Additionally if it's a single server having the issue, look at recent patches or group policy items that have been pushed, recently, especially due to OU changes or membership changes. If others on the same network are having issues, network hardware or improper domain controller resources might be an issue.