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.
Really good post!
I´ve noticed aswell the services wont startup sometimes and i added a delay of SQL services as a workaround but this is much better solution.
Thanks for your effort 🙂
Reply
Worked like a charm !
thank you
Reply
Weird this I had only the issue on one server
Reply
I’m glad that it worked for you. I hope that others find this post useful as well.
Reply
Great post!
Just a comment regarding service dependencies: Instead of using Regedit you can also use the “sc” command from an elevated command shell to create servive dependencies like this:
“sc config ServiceA depend= ServiceB”
Reply
Ahh, this is good. It is wise to avoid changing the registry directly whenever possible. Thanks!
Reply
To add to the comment above, the exact command you want to use here is:
sc config mssqlserver depend=w32time/netlogon
Needs to be a one liner because you have to specify both services at once…
Reply
Thanks! I can see people trying to do this one service at a time.
Reply
Here is another blog that is helpful too.
//batenict.blogspot.com/2018/03/using-gmsa-with-sql-server.html
Reply
Thanks for the tip, but I can’t get it to work. After adding the W32Time and Netlogon to the dependencies in the Registry Editor, I go the the MSSQLSERVER service properties and no dependencies are shown. The KEYISO is not there either.
For context, I’m working in a virtual environment with one Domain Controller and two SQL Server member servers. I’ve successfully set up a gMSA, but SQL Server refuses to start automatically on startup on both of the members. The SQL Servers will work if I manually start them.
Thoughts?
Reply
Are you opening up Registry Editor in Administrator mode? Are you using an account that is in the local administrator’s group?
Also, see the note from cmcapellan above about using the SC utility to set these dependencies with:
sc config mssqlserver depend=w32time/netlogon
Reply
No I wasn’t. I took both of those suggestions and it appears to be working now. Thanks for the reply and the tip!
Reply
Wayne, what SQL Server version are you using? I am using an AWS SQL Server Std Edition on Windows Server 2016 Datacenter 10.0 x64 Build 14393 (Hypervisor).
Also, I believe keyiso is only automatically added to DependsOn iff you use cryptography features like cryptographically signed certificates to protect things like data and code. I believe you should update your post to spell out these nuances.
Reply
Hi John,
A gMSA can be used with SQL Server 2014+. This article was based off of SQL Server 2016 and 2017.
About KeyISO: SQL Server uses certificates, so it needs this service. In my experience, I’ve always seen KeyISO being a dependent service for SQL Server.
Reply
Hello,
I also ran in the problem when my SQL Service did not start after reboot using managed service accounts.
I solved the problem when I modified the following registry value (maybe the key difffers according to your instance name or the key might be missing entirely):
Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
Value: ServiceAccountManaged
Datatype: REG_BINARY
On one working machine the content of this key was
01 00 00 00
The other machine which was NOT working had its value set to
00 00 00 00
Note: You have to reboot your OS after you modified the registry value.
I don’t know why this happened, but I tried several reboots and every time the SQL Server Service starts fine even without configuring any service dependencies.
Hope this helps!
Kind Regards,
Michael
Reply
Thanks Michael. The next time that I run into this, I’ll be sure to check this out.
Reply
I wonder if the behavior Michael sees is due to the good server being configured solely through SQL Server Configuration Manager. See this: //blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/
Reply
Fwiw – I am seeing some of the same behavior Michael has seen regardless of the services being configured via SCCM or even if the gmsa was added during SQL Server setup. It seems random as to when that ServiceAccountManaged is not set.
Reply
We had the same issue on a CRM SQL server. Couldn’t get the gMSA account to stick after a reboot for SQL Server, Reporting Services or SQL Server Agent. Everything we tried would not allow the account to start until we blanked out the password.
Finally changed the registry entry mentioned above by Michael:
Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
Value: ServiceAccountManaged
Datatype: REG_BINARY
01 00 00 00
Added this also for Reporting server and SQL Server Agent services in the registry.
Everything started on reboot after that.
Reply
We have a CRM SQL Server using a gMSA account that wouldn’t start SQL server on reboot no matter what we did. I had to keep blanking out the password to get services to start.
Finally was able to get it working on reboot by applying Michael’s suggestion from above:
Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER
Value: ServiceAccountManaged
Datatype: REG_BINARY
01 00 00 00
Had to do this to Reporting Services and SQL Server Agent as well to get them to start.
Reply
Great, thank you so much. I just added Dependencies and Reg-Key ServiceAccountManaged in a SQL 2022 installation in an Azure VM. Everything works now. Interesting: I have several instances on the server. There were ones with “ServiceAccountManaged” missing, some with value 1, some with value 0. (don’t know why they are different; I added all gmsas at the same time)
Missing and 1 worked, 0 didn’t.
Reply