I came into a situation where the monitoring tool was continually indicating “SSPI Handshake problems,” and the SQL DBA team asked me for help in resolving these issues. The SQL Server Error log folder is monitored by the monitoring programme, and alerts are reported. In this blog, I’ll discuss the root of the problem and the steps we took to resolve 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 tells us 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 Cannot be used with Windows authentication as Login failed. The login is from an untrusted domain. [CLIENT: 10.x.x.x]
To provide some background on SSPI:
The Security Support Provider Interface (SSPI) connects transport-level applications like Microsoft Remote Procedure Call (RPC) with security providers like Windows Distributed Security. SSPI allows a transport application to request an authenticated connection from one of multiple security providers.
For Windows authentication with trustworthy connection, the following argument is typically used in connection strings:
There are two types of SSPI errors:
“Cannot generate SSPI context “ and “SSPI Handshake Failed”
Cannot generate SSPI context: This issue usually occurs when a client attempts Kerberos authentication and fails, but does not fall back to NTLM authentication.
SSPI handshake failed: When the user is not authenticated, we get this error.
The error “SSPI Handshake Failed” was seen in the issue we were working on, indicating that the SQL Server was unable to authenticate the user.
To further investigate the error, we examined the security logs in Event Viewer on the SQL Server box at the time of the problem:
An account failed to log on.
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 Reason: The user has not been granted the requested logon type at this machine.
Sub Status: 0x0
Caller Process ID: 0x0
Caller Process Name: –
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
When a logon request fails, this event is triggered. It’s created on the machine where the attempted access was made.
The account on the local system that requested the logon is indicated in the Subject columns. A service, such as the Server service, or a local process, such as Winlogon.exe or Services.exe, are frequent examples.
The Logon Type parameter specifies the type of logon requested. The two most popular categories are 2 (interactive) and 3 (network).
The account and process on the system that requested the logon are listed in the Process Information fields.
The Network Information fields show the origin of the remote logon request. The name of the workstation is not always known, and in some circumstances it may be left blank.
The authentication information fields include precise details about this logon request.
- Transited services show which intermediate services were involved in the login request.
- The package name identifies which NTLM protocol sub-protocol was utilized.
- The length of the created session key is indicated by key length. If no session key was requested, this value will be 0.
We had a lot of data to process from the Security log. Close examination of the logs:
The Login Account “Contoso\sqlaccount” is attempting to connect to the SQLServer box from the workstation “SQLClient” with logon type 3: Network (A user or computer signed on to this computer from the network), and receives the error: The user has not been granted the requested login type at this machine.
To isolate the problem, we used the user “contoso\sqlaccount” to log in to the SQLClient box and launched the udl file to connect to the SQL instance:
We got the same error reported in SQL Error log loos like below :
The login attempt was unsuccessful. Because the login is from an untrusted domain, Windows authentication will not work.
To resolve the issue:
we added the account “contososqlaccount” to the SQL Server box’s “Access this machine from the network” local security policy (secpol.msc), after which we were able to successfully connect to the instance from the application.
Always inspect the security logs after enabling Audit Logon events to resolve SSPI Handshake failure problems.
Another situation of SSPI Handshake Error code 0x8009030c Scenario::
I was working in SSMS the other day and connected to an instance. There’s nothing unusual about that. I later opened a window (I’m not sure what it was–maybe a report or a database option) and was greeted with this error message. I couldn’t do anything in the database after that.
While establishing a connection with integrated security, the SSPI handshake failed with error number 0x8009030c, state 14; the connection has been closed. AcceptSecurityContext failed for some reason. The cause of failure is indicated by the Windows error code. [CLIENT: 10.1.1.1]
The SQL Server log included the same problem with the same error message.
Error: 17806, Severity: 20, State: 14.
My Windows password had expired between the time I started the instance and the time I was looking at something in the database, it turned out. I was good to proceed after I reset my password.