Within one of the SQL Server environments that I support, we recently experienced a Windows Server 2012 operating system crash followed by a situation where the SQL Server 2012 service was unable to start with the following error message:
Error message 7038 – The MSSQLSERVER service was unable to log on as NTSERVICE\MSSQLSERVER with the currently configured password due to the following error:
This user isn’t allowed to sign in to this computer.
After investigating, we determined that the issue did not pertain specifically to SQL Server, but instead to an operating system feature within Local Security Authority (LSA) called CrashOnAuditFail. As part of the Windows Server 2012 / 2012 R2 operating system security hardening guidelines, the operating system must shut down by default upon audit failure per STIG vulnerability V-57657. This feature is enabled by configuring the policy value for Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> Security Options -> “Audit: Shut down system immediately if unable to log security audits” to “Enabled”.
When the server is unable to write audit information with this setting enabled, the server updates the registry key HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\CrashOnAuditFail to a value of 2, followed by server shutdown. Upon restart, only members of the Local Administrator group are able to logon to the server in order to remediate the underlying cause of the failed audit logging. In our case, it was an intermediate network failure preventing the audit from being written. We were able to reset the registry value back to 1, restart the server, and bring all services back online.
For additional information on the STIG security guideline:
For additional TechNet information regarding the CrashOnAuditFail registry key:
Hope this helps,
Sam Lester (MSFT)