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

SQL Server would not start, Windows said the account had not been granted the requested logon type, and there was no ERRORLOG to read. That is the part that makes this one frightening. There was no ERRORLOG because SQL Server never ran. Windows refused to start it, so the answer was in the Windows logs all along. I am also going to disagree with the advice I gave at the end of this one years ago. It has not aged well.

No ERRORLOG Means Look Somewhere Else

When a service will not start, people go straight to the SQL Server ERRORLOG. Reasonable, and useless here.

SQL Server writes that file. If SQL Server did not run, the file was never written. The one on disk is from last time. An ERRORLOG that ends normally hours ago is not a clue about this morning. It is the absence of one.

So when the ERRORLOG is missing or stale, stop reading it. Windows tried to start a process. Something stopped it before SQL Server had a say.

The Message

The MSSQLSERVER service was unable to log on as CORP\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".

That last line is the whole diagnosis, and it is unusually generous. Windows has told you the name of the right that is missing.

Read the first line carefully too. It says unable to log on with the currently configured password. That sends people off resetting passwords. The password is fine. The account is being refused a kind of logon, not refused for its credentials.

Finding It Without Clicking Around

Two commands. The first says which account the service is trying to use, and it needs no special privileges:

Get-CimInstance Win32_Service -Filter "Name LIKE 'MSSQL%'" |
    Select-Object Name, StartName, State, StartMode

On my own machine:

Name                   StartName                         State    StartMode
MSSQL$SQLDEV           NT Service\MSSQL$SQLDEV           Running  Auto
MSSQLLaunchpad$SQLDEV  NT Service\MSSQLLaunchpad$SQLDEV  Stopped  Disabled

The second pulls what Windows said when the start failed:

Get-WinEvent -FilterHashtable @{
    LogName      = 'System'
    ProviderName = 'Service Control Manager'
} -MaxEvents 20 | Select-Object TimeCreated, Id, LevelDisplayName, Message

Both ran here without an elevated prompt. That matters when you are on somebody else’s machine with somebody else’s account. Event 7000 is the service failing to start. Event 7038 is the logon failure that caused it.

Granting the Right

Through the interface it is Start, then secpol.msc, then Security Settings, Local Policies, User Rights Assignment. Find Log on as a service and add the account.

To read the current setting from the command line:

secedit /export /cfg C:\temp\rights.cfg /areas USER_RIGHTS
Select-String -Path C:\temp\rights.cfg -Pattern 'SeServiceLogonRight'

That one does need an elevated prompt. I tried without and was told plainly to run as administrator. Correct behaviour, for something that reads the machine’s security policy.

Look for the opposite setting while you are in there. SeDenyServiceLogonRight is Deny log on as a service. A deny beats a grant every time. If the account is in both lists, adding it again to the first achieves nothing. People lose an hour to that.

Why It Went Missing

Nobody removes this right on purpose. Three things do it for you.

Changing the service account outside SQL Server Configuration Manager. Configuration Manager grants the right as part of the change. The Services applet and sc.exe do not. This is by far the most common cause. It is why I keep saying to use Configuration Manager for this one job, whatever you prefer for everything else.

A domain Group Policy that sets User Rights Assignment. It overwrites the local list rather than adding to it. So a hardening policy applied on Tuesday takes the right away that night, and the server fails at its next restart, which might be weeks later. That delay is what hides the cause.

A security baseline script. Same effect, same delay.

If the right vanished once and you do not know which of those did it, it will vanish again. Find out which.

The Second Error, and Where I Was Wrong

We fixed the right, tried again, and got a different failure:

A fatal error occurred while creating an SSL server credential.
The internal error state is 10013.

At the time I fixed it by enabling TLS 1.0 in the registry. It worked. The server came up and everyone went home.

Do not do that today. TLS 1.0 has been deprecated for years. Switch it back on to start a service and every connection to that server drops to a protocol nobody should still be using. I fixed a stopped service by opening something that should have stayed shut.

Error state 10013 means SQL Server could not build its SSL credential. Three real causes are worth working through first.

The service account cannot read the certificate’s private key. This is the most common by far, and it is the same root cause as the first half of this post. Change the service account and the new one has no permission on the key the old one used. Fix it in the certificate store: find the certificate, Manage Private Keys, add the service account with read.

The certificate has expired, or does not match the server name, or is not in the Personal store of the Local Computer account where SQL Server looks for it.

The protocols have been hardened and the SQL Server build predates TLS 1.2 support. The right answer is to patch SQL Server, not to unharden Windows. TLS 1.2 support has been available for a long time. On very old builds it needs a specific update.

Check What Is Actually Set

Before assuming a protocol is the problem, look:

$base = 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols'
Get-ChildItem $base -Recurse | ForEach-Object {
    $v = Get-ItemProperty $_.PSPath
    [PSCustomObject]@{ Key = $_.Name; Enabled = $v.Enabled; DisabledByDefault = $v.DisabledByDefault }
}

I ran that here and it returned nothing at all. The Protocols key exists with no subkeys under it. That is the state a normal Windows install is in. Nothing has been explicitly enabled or disabled, and the operating system decides.

That reading is worth having in your head, because it makes the opposite obvious. Run this on a broken server and find a wall of subkeys with Enabled set to zero, and somebody has been hardening. Now you know what changed. The conversation becomes which protocol SQL Server can be brought up to, not which one Windows can be dropped back to.

The Order I Would Work In

Check whether an ERRORLOG was written at all. If not, this is a Windows problem and the SQL logs will not help.

Read the System event log for Service Control Manager. It usually names the cause outright.

Confirm which account the service is using, then check both the grant list and the deny list for Log on as a service.

And if a certificate error follows, go at the private key permission first. A changed service account causes it, and you have just been reminded that the service account is in play.

A missing ERRORLOG is not a missing clue, it is the clue.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.