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. [CLIENT: 192.168.1.20].
Error: 17806, Severity: 20, State: 14.
This error can happen due to Active Directory configuration issue or Network related issues. Assuming they are fine, another reason could be related to failed Kerberos Authentication to connect to SQL Server.
In Windows 2003 and above, the default method of establishing a connection is Kerberos but if it fails, then connection may be established using NTLM. An error may be registered on SQL Server error log due to Kerberos failure.
To confirm if you have any database connection using Kerberos authentication, run this on your SQL Server
SELECT distinct auth_scheme FROM sys.dm_exec_connections
If you only see NTLM and SQL then, there is no Kerberos connection.
To use Kerberos, the client and SQL Server must be in the same domain or trusted domains and a Service Principal Name (SPN) must be registered with Active Directory for SQL Server service account.
You can check if the service account already has SPN, by running this on the SQL Server
setspn -l <domain\sqlserviceaccount>
If you don't see anything starting with MSSQLSvc\ then the account is not setup with SPN.
You 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/<SQL Server computer name>:1433 <Domain\Account>
- To create an SPN for the FQDN of the SQL Server use the following command: setspn -A MSSQLSvc/<SQL Server FQDN>:1433 <Domain\Account>
If SQL Server service account is granted "Validated write to service principal name" privilege in Active Directory, SQL Server database engine will register itself when it starts and unregisters at shutdown. If you Domain Administrators do not permit this setting then you should manually register the SPN and the SSPI error will go away.
Also the SQL Server service account must be granted "Account is trusted for delegation" privilege in Active Directory. If your SQL Server connects to other SQL Server using Linked Server then the server must also be granted "Trust this computer for delegation to any service" privilege.
For a named instance, you can use the FQDN of the named instance instead of SQL Server FQDN and use the port number.
For a clustered server, you should use the FQDN of the SQL Server virtual name. In this case you will need two entries, one with port name and one without such as
setspn -A MSSQLSvc/<Virtual SQL Server FQDN> <Domain\Account>
setspn -A MSSQLSvc/<Virtual SQL Server FQDN>:1433 <Domain\Account>
Kerberos authentication works when SQL Server, Service Account and Windows account accessing the SQL Server are in same windows domain. If anyone these are in different domain, there must be bidirectional trust established between the domains, otherwise it does not work.