SSPI handshake failed” could result when the security event log has reached the maximum log size
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.
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:
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
Logon Error: 17806, Severity: 20, State: 2.
Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT:192.168.0.5]
Logon Error: 18452, Severity: 14, State: 1.
Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT:192.168.0.5]
We normally see two kinds of SSPI errors. One is “Cannot generate SSPI context” and the other is “SSPI Handshake Failed”. The first error is commonly because the client is trying a Kerberos authentication and that failed, but it did not fall back to NTLM. The second one happens usually when the user is not authenticated. So I looked into the SQL Server Security Event Logs and I can see this entry:
Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT:192.168.0.5]
Logon Error: 18452, Severity: 14, State: 1.
Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT:192.168.0.5]
We normally see two kinds of SSPI errors. One is “Cannot generate SSPI context” and the other is “SSPI Handshake Failed”. The first error is commonly because the client is trying a Kerberos authentication and that failed, but it did not fall back to NTLM. The second one happens usually when the user is not authenticated. So I looked into the SQL Server Security Event Logs and I can see this entry:
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
No comments:
Post a Comment