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.
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:
Log Name: Security
Date: 1/15/2011 2:52:01 PM
Event ID: 4625
Task Category: Logon
Keywords: Audit Failure
An account failed to log on.
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 Reason: Unknown user name or bad password.
Sub Status: 0x0
Caller Process ID: 0x0
Caller Process Name: -
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:
# for hex 0xc000006e / decimal -1073741714
# 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
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.