4

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?

  • Taking a guess, it sounds like a replication issue with one or more Domain Controllers due to the fact the issue is intermittent. – rvsc48 Apr 15 at 16:19
  • Is it possible you are using a backup domain controller or that the domain controller is simply overwhelmed? Is this the only server using this DC, or is the problem also reproducible on other servers? It also could be some kind of packet loss issue - it doesn't seem like the DC is unreachable, more like the data it receives isn't always complete. – Aaron Bertrand Apr 15 at 16:49
  • Thanks for the replies. I welcome any/all guesses. This is the only server I know of having this issue. It is special, in that it is a secondary in a high availability group and is located off site, with the two sites connected via a 10 GB pipe. Now, that being said, for the first two and a half months offsite, there were none of these connection issues. The network guy I'm working with has been focusing on the domain controller. Maybe we're on the right path. – BillBikesInCleveland Apr 15 at 17:19
  • I am not really sure if this would help you or not, are you using fully qualified name for server name when trying to access the server. I meant if server name is ABC-DEF and domain name is company.com. We should be using ABC-DEF@company.com and same should be applicable for login. If login is TestLogin then login should be used as company.com\TestLogin. Otherwise for server name, you may use direct IP(if clustered then VIP) – Learning_DBAdmin Apr 15 at 18:17
6

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.

  • Check the server error log on both the sql server and the DC. It will probably be revealing. – rottengeek May 8 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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