Common ‘SSPI handshake failed’ errors and troubleshooting
Hello all,
I came across a scenario where System Center monitoring tool was constantly reporting “SSPI Handshake errors” and I was approached by SQL DBA team to assist in addressing these errors. SCOM monitors the SQL Server Error log folder and reports these alerts. In this blog, I am covering the cause of this issue and the solution we followed to fix it:
Below errors were reported frequently in SQL Error log:
2016-02-07 12:44:22.81 Logon Error: 17806, Severity: 20, State: 14.
2016-02-07 12:44:22.81 Logon SSPI handshake failed with error code 0x8009030c, 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. The logon attempt failed [CLIENT: 10.x.x.x]
2016-02-07 12:44:22.81 Logon Error: 18452, Severity: 14, State: 1.
2016-02-07 12:44:22.81 Logon Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.x.x.x]
To share some information about SSPI:
SSPI (Security Support Provider Interface) is an interface between transport-level applications, such as Microsoft Remote Procedure Call (RPC), and security providers, such as Windows Distributed Security. SSPI allows a transport application to call one of several security providers to obtain an authenticated connection.
The following parameter is commonly used in connection strings for Windows authentication with trusted connection:
Integrated Security=SSPI
There can be 2 variants in SSPI errors:
“Cannot generate SSPI context “ and “SSPI Handshake Failed”
Cannot generate SSPI context: We generally get this error when the client is trying a Kerberos authentication and that fails but it does not fall back to NTLM.
SSPI handshake failed: We get this when the user is not authenticated.
In the issue we worked on we were encountering “SSPI Handshake Failed” which indicates that the SQL Server was unable to authenticate the user.
To debug the error further, we reviewed the security logs in Event viewer on SQL Server box during the time of the issue:
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: sqlaccount
Account Domain: contoso
Failure Information:
Failure Reason: The user has not been granted the requested logon type at this machine.
Status: 0xC000015B
Sub Status: 0x0
Process Information:
Caller Process ID: 0x0
Caller Process Name: -
Network Information:
Workstation Name: SQLclient
Source Network Address: -
Source Port: -
Detailed Authentication Information:
Logon Process: NtLmSsp
Authentication Package: NTLM
Transited Services: -
Package Name (NTLM only): -
Key Length: 0
This event is generated when a logon request fails. It is generated on the computer where the access was attempted.
The Subject fields indicate the account on the local system which requested the logon. This is commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.
The Logon Type field indicates the kind of logon that was requested. The most common types are 2 (interactive) and 3 (network).
The Process Information fields indicates which account and process on the system requested the logon.
The Network Information fields indicates where the remote logon request originated. Workstation name is not always available and may be left blank in some cases.
The authentication information fields provide detailed information about this specific logon request.
- Transited services indicate which intermediate services have participated in this logon request.
- Package name indicates which sub-protocol was used among the NTLM protocols.
- Key length indicates the length of the generated session key. This will be 0 if no session key was requested.
Inference:
The Security log gave us a lot of information to process. Decoding the logs closely:
From the workstation “SQLClient”, “Contoso\sqlaccount” is trying to connect to the SQLServer box with logon type 3: Network (A user or computer logged on to this computer from the network) and the error thrown is: The user has not been granted the requested logon type at this machine.
To isolate the issue, we logged on to SQLClient box using the account “contoso\sqlaccount” and launched the udl file to connect to SQL instance:
We got the same error reported in SQL Error log:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication
Resolution:
To address the issue:
We added the account “contoso\sqlaccount” to “Access this computer from the network” local security policy (secpol.msc) on the SQL Server box and post which we were successfully able to connect to the instance from the application.
To address the SSPI Handshake failed errors, always review the security logs post enabling Audit Logon events. Security logs would give a good amount of information needed to address this issues.
Reference:
https://technet.microsoft.com/en-us/library/cc787567(v=ws.10).aspx
Please share your feedback, questions and/or suggestions.
Thanks,
Don Castelino | Premier Field Engineer | Microsoft
Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.