共用方式為

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.

Comments

  • Anonymous
    August 05, 2016
    Thanks Don, with help of your article, I could investigate and resolve error being logged on our SQL Server
    • Anonymous
      August 08, 2016
      Thank you Vikram for the feedback.
  • Anonymous
    October 06, 2016
    Thanks Don, This article helped me troubleshoot and resolve a Production issue.
  • Anonymous
    November 10, 2016
    Thanks Don, This article guided me right direction to resolve issue
  • Anonymous
    July 10, 2017
    Thanks Don,For sharing detail info on issue.
  • Anonymous
    March 05, 2018
    Thanks )
  • Anonymous
    August 01, 2018
    Thanks Don, but the solution didn't work for me. The problem always occurs by logging in using Windows Authentication on the sql server itself. On any other client there is no problem using the same credentials. This is on our new SQL2017 server. On our SQL 2016 server we do not have this problem and it resides in the same domain/network.
    • Anonymous
      August 03, 2018
      Hello Gregory,Couple of things to check here are:1. Is the connectivity breaking only for Windows authentication or SQL authentication as well?2. If its SSPI error while connecting to SQL instance, then any errors reported in security logs? What is the error code reported? can you print the error message reported in error log here?
  • Anonymous
    December 11, 2018
    Very helpful information. Thanks for posting this.
  • Anonymous
    January 08, 2019
    The comment has been removed
  • Anonymous
    February 12, 2019
    Another cause can be if the account is locked out. We had this due to a rogue service running every half hour with the wrong password. The account was a service account and set to unlock itself after a few minutes where this occurred so it was quite difficult to track down.