Compartir a través de

“SSPI handshake failed” could result when the security event log has reached the maximum log size

  • Artículo

I see a lot of issues related to SQL Server connectivity. One common error I see in the SQL Server logs is the SSPI error.

 Log Name: Security 
Source: Microsoft-Windows-Security-Auditing 
Date: 1/15/2011 2:52:01 PM 
Event ID: 4625 
Task Category: Logon 
Level: Information 
Keywords: Audit Failure 
User: N/A 
Computer: SQLMACHINE.corp.mydomain.com 
Description: 
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: APPSERVER$ 
Account Domain: CORP.MYDOMAIN.COM 
Failure Information: 
Failure Reason: Unknown user name or bad password. 
Status: 0xc000006e 
Sub Status: 0x0 
Process Information: 
Caller Process ID: 0x0 
Caller Process Name: - 
Network Information: 
Workstation Name: - 
Source Network Address: - 
Source Port: - 
Detailed Authentication Information: 
Logon Process: Kerberos 
Authentication Package: Kerberos 
Transited Services: - 
Package Name (NTLM only): - 
Key Length: 0

In this scenario, the app was trying to connect with the machine account. Typically when using windows authentication, if the application is running in the context of Local System or Network Service, the application will connect using the machine account. If you take a Profiler trace, the account name is shown as MachineName$.
If we add the APPSERVER$ account to the Administrators group of the SQL Server machine, we don’t see the problem.

Looking into this error code:

 err 0xc000006e 
# for hex 0xc000006e / decimal -1073741714 
STATUS_ACCOUNT_RESTRICTION ntstatus.h 
# Indicates a referenced user name and authentication 
# information are valid, but some user account restriction 
# has prevented successful authentication (such as 
# time-of-day restrictions).

Here, 0xc000006e means STATUS_ACCOUNT_RESTRICTION, which indicates referenced user name and authentication information are valid, but some user account restriction has prevented successful authentication. If we add that user account to the local Administrators group, we are able to connect to SQL Server. We believe it is because we have “Audit: Shutdown system immediately if unable to log security audits” policy enabled. If HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\CrashOnAuditFail is 1, the local policy “Audit: Shut down system immediately if unable to log security audits” is enabled. When the security logs become full, the CrashOnAuditFail value will be changed to 2. At this point, only local administrators can logon and non-administrator accounts will fail.

We checked HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\CrashOnAuditFail and the value was 2

This problem occurs if the security event log has reached the maximum log size and the Event Log Wrapping setting is set to Overwrite Events Older than X Days or Do Not Overwrite Events. Because the security event log is full, and the CrashOnAuditFail registry key is set, Microsoft Windows does not permit accounts that are not administrator accounts to log on.

If the machine is a Web Server you may also see websites with anonymous access are being affected. When anonymous access is configured, requests to the Web site try to authenticate by using the IUSR_computername and IWAM_computername accounts. These accounts are not administrator accounts.

For further details please refer the following KB 832981

Comments

  • Anonymous
    May 15, 2013
    Thanx for this post. I had several of this errors in my application log and I couldn't sort out what they ment or where they came from. Your post/blog made it clear to me and solved this matter.