SQL SERVER – Logon Failure: The User has not Been Granted the Requested Logon Type at This Computer

Sometimes DBA do something which they are not aware of and end up in looking at the logs to see what went wrong. Here is one of the articles I wrote about the changing service account from configuration manager. Why to Use SQL Server Configuration Manager Over Services applet (services.msc)? – Interview Question of the Week #112. In this blog post, let us learn how to fix logon failure error.

Now the situation here was not same, but kind of similar. SQL Server service was not getting started and ERRORLOG was also not getting generated. I looked into System event logs and found below the message.

The MSSQLSERVER service was unable to log on as SQLAuthority\SQLFarmService with the currently configured password due to the following error:
Logon failure: the user has not been granted the requested logon type at this computer.
This service account does not have the required user right “Log on as a service.”

WORKAROUND / SOLUTION

We can see the permission in the error message. In an attempt to fix above error, we did below.

1. Click on Start > Run
2. Open SecPol.msc to open local security policy.
3. Go to Security Settings > Local Policies > User Rights Assignment
4. Locate Log on as a Service (This is what we had in error message)
5. Make sure SQL Service account is added in this. In our case, it was missing so we added it manually.

We attempted to start again and it failed with new error.

A fatal error occurred while creating an SSL server credential. The internal error state is 10013.
This is generally due to incorrect TLS settings. We took backup of below registry key
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SecurityProviders\SCHANNEL\protocols\TLS1.0

We set the value of enabled as 1 and restarted the machine to take effect. Once restarted, we were able to start SQL Service.

Reference: Pinal Dave (http://blog.SQLAuthority.com)