Table of Contents
SQL Server Error: 17806, Severity: 20, State: 14.
SSPI handshake failed with error code 0x8009030c, state 14
Details of Error:
Error: 17806, Severity: 20, State: 14.
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.
These errors appear in SQL Error logs.
Cause of Error:
Can be due to multiple causes like
1. This issue can be related to Kerberos and most of the SSPI handshake error messages due to Kerberos failure. Kerberos failure can be most likely be related to non-existent SPN (Service Principal Name) or bad SPN for SQL Server. Normally local connections work properly as they use NTLM and the SPN is not required for NTLM.
To confirm if we are Kerberos authentication, we can run below query
SELECT distinct auth_scheme FROM sys.dm_exec_connections
2. SSPI handshake errors can be Time/Clock difference between client clock differs server clock
Resolution:
1. Reboot the misbehaving Domain controller
2. Correct Client to Server time
3. Set the SPN manually correctly for SQL Server using below
If we are using Kerberos then both the client and SQL Server must be in the same domain or trusted domains with bidirectional trust established between the domains and a Service Principal Name (SPN) must be registered with Active Directory for SQL Server service account.
We can check if the service account already has SPN, by running this on the SQL Server in question.
setspn -l <DomainName\SqlServiceAccount>
If we do not see any entries starting with MSSQLSvc\ then the account is not setup with SPN.
We can manually register SPN as a Domain Administrator using these commands.
To create an SPN for the NetBIOS name of the SQL Server use the following command:
setspn –A MSSQLSvc/:1433 Domain\Account
To create an SPN for the FQDN of the SQL Server use the following command:
setspn -A MSSQLSvc/:1433 Domain\Account
Very nice article, hi. I hope you will publish again type of post.
Thank you!
King regards,
Lunding Cannon