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 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