2023-11-02

SQL Server Service Account "Log on as a service" right in GPO

In the past month, one of my clients called me for an emergency problem, saying a production SQL Server down and cannot be started. When I check the Windows event log, I found the error as depicted below:

Saying the MSSQLSERVER service account does not have the required user right "Log on as a service.".
It's quite strange, as the "Log on as a service" right was being assigned automatically by the installer to the sql service account when the sql server was installed, and that sql server had been running well for a while.
Then I immediately solved that problem by adding the service account back to the allowed list in Local Security Policy > Local Policies > User Rights Assignment > Log on as a service:

But on the next day morning, my clients called me again, saying the same problem occurred again. I found the sql service account disappeared again in the Log on as a service allowed list.

Then I checked the Microsoft KB, I found below article:
Error 1069 occurs when you start SQL Server Service
Below sentences catch my eye:
If you have already assigned this user right to the service account, and the user right appears to be removed, check with your domain administrator to find out if a Group Policy object associated with this node might be removing the right.
Ah-ha, that's a clue. I asked the domain administrator when the GPO associated with this server removed the right or not. First time the domain admin told me there's no any GPO which removes any user right. Then I insisted him to give me the GPO associated with that server. The domain admin gave me below screenshot:

Then I told the domain admin to add the sql service account into the above GPO Log on as a service allowed list. The problem resolved and doesn't occur anymore.

No comments:

Post a Comment