Intro:
When I encountered a situation where the monitoring tool was consistently reporting “SSPI Handshake Issues” the team contacted me for assistance in fixing these problems. The monitoring program tracks the SQL Server Error log folder and alerts are generated.
The cause of the issue and the procedures we took to fix it are covered in this blog post:
Issue:
2022-12-07 12:44:22.81 Logon Error: 17806, Severity: 20, State: 14.
2022-12-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]
2022-12-07 12:44:22.81 Logon Error: 18452, Severity: 14, State: 1.
2022-12-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]
For some background information about SSPI:
The Security Support Provider Interface (SSPI) links security providers like Windows Distributed Security with transport-level programs like Microsoft Remote Procedure Call (RPC). A transport application has the option to use SSPI to ask one of many security providers for an authenticated connection.
The following argument is frequently used in connection strings for Windows authentication with a trustworthy connection:
Integrated Security = SSPI
There are two types of SSPI errors:
- Cannot generate SSPI context
- SSPI Handshake Failed
Cannot generate SSPI context:
This issue commonly arises when a client attempts Kerberos authentication and fails, but does not fall back to NTLM authentication.
SSPI handshake failed:
This error occurs when the user is not authenticated.
The problem we were working on had the error “SSPI Handshake Failed” which meant that the SQL Server was unable to authenticate the user.
We looked at the security logs in Event Viewer on the SQL Server box at the time of the issue to further explore the error:
An account failed to log on.
Subject:
Security ID: NULL SID
Account Name: –
Account Domain: –
Logon ID: 0x0Logon Type: 3
Account For Which Logon Failed:
Security ID: NULL SID
Account Name: abc
Account Domain: sqlindFailure Information:
Failure Reason: The user has not been granted the requested logon type at this machine.
Status: 0xC000015B
Sub Status: 0x0Process Information:
Caller Process ID: 0x0
Caller Process Name: –Network Information:
Workstation Name: SQLclient
Source Network Address: –
Source Port: –Detailed Authentication Information:
This event is set off when a logon request is unsuccessful. It is produced on the device where the access attempt was made.
Logon Process: NtLmSsp
Authentication Package: NTLM
Transited Services: –
Package Name (NTLM only): –
Key Length: 0
The Subject columns show which account on the local system made the logon request. A common example is a service, like the Server service, or a local process, like Winlogon.exe or Services.exe.
The intended type of logon is specified by the Logon Type option. The second and third categories are the most well-liked (network).
The Process Information columns contain a list of the account and process on the system that made the logon request.
The origin of the remote logon request is displayed in the Network Information columns. Sometimes the name of the workstation is unknown, and it might even be left blank.
Precise information about this login request is contained in the authentication information sections.
- Services that have been transited display which intermediary services were used in the login request.
- Which NTLM protocol sub-protocol was used is indicated by the package name.
- The key length identifies the size of the generated session key. This value will be 0 if no session key request was made.
Conclusion:
The Security log provided us with a lot of information to process. a thorough review of the logs:
The problem reads: The user has not been granted the requested login type at this machine. The login account “sqlind\abc” is attempting to connect to the SQLServer box from the workstation “SQLClient” with logon type 3: Network (A user or computer logged on to this computer from the network).
In order to narrow down the issue, we launched the udl file to connect to the SQL instance while logged in as “sqlind\abc” on the SQLClient box
We received the same error, which was recorded as follows in the SQL Error log:
The attempt to log in was unsuccessful. Windows authentication is not going to function because the login is coming from an untrusted domain.
Resolution:
To fix the problem, we added the account “sqlind\abc” to the SQL Server box’s local security policy (secpol.msc), “Access this machine from the network,” and then we were able to successfully connect to the instance from the application.
As soon as you enable Audit Logon events, be sure to check the security logs to fix any SSPI Handshake failure issues.
A different occurrence of the SSPI Handshake Error code 0x8009030c
Scenario:
The other day, I connected to an instance while working in SSMS. That is not exceptional in any way. I opened a window later and was met with this error warning; I’m not sure what it was—possibly a report or a database option. After that, I was unable to perform anything in the database.
The SSPI handshake during the establishment of a connection with integrated security failed with error code 0x8009030c, state 14, and the connection was terminated. For whatever reason, AcceptSecurityContext failed. The Windows error code identifies the root of the issue. [CLIENT 10.1.1] .1]
The SQL Server log included the same problem with the same error message.
Error: 17806, Severity: 20, State: 14.
It turned out that between the time I launched the instance and the time I was looking at something in the database, my Windows password had expired. As soon as I changed my password, I was good to Connect