As you already know, SQL Server runs as a service. And services require a service account to run under. While this service account is likely to be a domain service account, it could be an account on the local machine. To follow good security practices you would need to specify that this account:
- Has the password frequently changed.
- Nobody knows the password.
- Cannot be used to login locally.
First of all, changing the password on a normal service account requires someone to know the password. Furthermore, it requires a service restart to pick up the change. A standalone managed service account (sMSA) cannot be used across multiple devices. Additionally, they don’t have a single point of control for password management. Using a group managed service account (gMSA) can solve all of these issues.
Introducing gMSA
A gMSA is a sMSA that can be used across multiple devices, and where the Active Directory (AD) controls the password. PowerShell is used to configure a gMSA on the AD. The specific computers that it is allowed to be used on is configured using some more PowerShell commands. The AD will automatically update the password for the gMSA at the specified interval – without requiring a restart of the service! Because the AD automatically manages the password, nobody knows what the password is.
Not all services support a gMSA – but SQL Server does. During a SQL Server installation you can specify the gMSA account. The SQL Server Configuration Manager (SSCM) tool can be used to change an existing SQL Server instance to use a gMSA. After entering the gMSA account you simply do not enter a password. The server automatically retrieves the password from the AD.
The pitfalls of using a gMSA with SQL Server
As with almost all things, there is inevitably something that doesn’t work correctly. One thing that I found is that when the server is rebooted, the SQL Server services are not restarted. And I’m not the only person that has had this problem. After digging through the Windows event logs, we were able to figure out the issues. The service was not restarting because it was trying to do things before those services had been started. We were able to overcome this issue by creating service dependencies. A service dependency prevents the service (SQL Server) from starting up before other specified services are running.
Creating Service Dependencies
To create these dependencies, you have to use the Registry Editor. In the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLSERVER\DependOnService key, add the W32Time and Netlogon services to the existing KEYISO value:
Now when you open up the service property window and click on the Dependency tab, you will see:
When the server is restarted, the SQL Server service will successfully start up.
While you’re in the service property window, we also set the first and second failure options on the Recovery tab:
You can read more about creating a gMSA here.
In retrospect, this makes sense. The gMSA needs the computer to retrieve the password from AD, so Netlogon would be necessary. gMSA uses Kerberos, and Kerberos requires computers to have their clocks in sync, which requires having W32Time. Since SQL knows that a gMSA is being used (when installing, or in SSCM), you would think that it would just make this registry entry for you. But alas, it doesn’t, so you must.
