One of my clients wanted to secure their SQL Server. One of the rules was that wanted to avoid running SQL Server service with an account which is part of local “Administrators” group. They wanted to provide the minimal permissions needed for the SQL server service account for SQL Server to function.
Below are the respective articles for each version that outline the permissions assigned to the service accounts:
Configure Windows Service Accounts and Permissions (SQL Server 2016 and later)
Configure Windows Service Accounts and Permissions (SQL Server 2014)
Configure Windows Service Accounts and Permissions (SQL Server 2012)
Setting Up Windows Service Accounts (SQL Server 2008 R2)
Setting Up Windows Service Accounts (SQL Server 2008)
Setting Up Windows Service Accounts (SQL Server 2005)
When I spoke to my client, they already removed service account from the Administrators group. As per article, we have permissions in security policy, but still we were not able to start SQL Service. By looking further into the Event Viewer, under Application Log we see the error :
“initerrlog: Could not open error log file ”. Operating system error = 3(The system cannot find the path specified.)”.
If we provide incorrect path, we generally see below message.
initerrlog: Could not open error log file ‘C:\BadPath\Errorlog.txt’. Operating system error = 3(The system cannot find the path specified.).”
Notice the blank path in an error message. Later, we used one of my favorite tools called as Process Monitor and ran the tool to look for registry read activity. We knew that the service was queried for the for the start-up parameters which are saved in the registry and we wanted to check how it does it. We saw we were getting Access Denied for the Account: Americas\SAPPROD_SQLSvc on the registry hive:
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\Parameters
Seeing that, we went to the registry hive and checked if the service account had permission and found that the user did not have permission, we also saw that the there was a group, SQLServerMSSQLUser$MPNA01$MSSQLSERVER
We were able to fix the issue by adding Service Account Americas\SAPPROD_SQLSvc to the group SQLServerMSSQLUser$MPNA01$MSSQLSERVER
Reference: Pinal Dave (https://blog.sqlauthority.com)