Group Managed Service Accounts (gMSA) are one of my favourite Windows Server features and are fully supported by SQL Server. I still don’t see them being used very much although we use them all the time with our Managed DBA Service for SQL Server customers. I’m not sure whether it’s because of a lack of awareness or that they seem too complicated to setup so I aim to address both of those problems in this post.
What’s the big deal with gMSA?
The result of using a gMSA is, in short, that you don’t need to use passwords for your SQL Server service accounts. Passwords still exist but they’re managed automatically between the server and the domain with no human interaction and this gives you the following benefits:
- No need to create a secure password
- No need to securely store that password
- No need to retrieve that password and provide it for build scripts
- No need to manually cycle the password for security reasons as it’s done automatically
How do you create a gMSA?
Simply put, you:
- create a group in Active Directory and add the computer accounts of the servers that you want to use a particular service account.
- create the service account giving permission to that group to use it.
- use the service account as normal adding $ to the account name without specifying a password.
What are the prerequisites for using a gMSA with SQL Server?
You’re going to need:
- all your domain controllers to be running at least Windows Server 2012 but there is no domain or forest functional level requirement.
- your database server to be running at least Windows Server 2012 R2 and SQL Server 2014.
How do I create a gMSA for SQL Server step-by-step?
Step 1 – Ensure the Key Distribution Service has a root key
The Microsoft Key Distribution Service is used to provide access to the service account password and needs a Root Key to be setup on the domain.
NOTE: If you’re using AWS Managed AD, a KDS Root Key has automatically been created for you and you can skip this step.
Use the following PowerShell command to see if a root key exists:
Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyId
If it doesn’t return a value of ‘True’ then you’ll need to create a key with the following command (you’ll need to be a Domain Admin):
Add-KdsRootKey -EffectiveImmediately
You might have to wait up to 10 hours for the key to replicate across all your domain controllers.
Step 2 – Create a global security group for your servers
Create a group and add the server that you want to be able to use a particular set of service accounts. Use a separate set of service accounts for each server unless you’re using a SQL Server Failover Cluster Instance or an AlwaysOn Availability Group where you’ll want all the servers involved to be in this group so they can use the same accounts.
In the below example I’m creating the group and adding two servers: sql01 and sql02
New-ADGroup -Name gsg_SQLgMSA -Description “Security group for SQLgMSA computers” -GroupCategory Security -GroupScope Global Add-ADGroupMember -Identity gsg_SQLgMSA -Members sql01$,sql02$
NOTE: You’ll need to reboot the servers at this point for their group membership to take effect.
Step 3 – Create your gMSA service accounts
You should always create separate service accounts for SQL Server and SQL Server Agent so that the minimum necessary security permissions can be applied for each account.
In the below example I’m creating two service accounts that I’ll use for SQL Server and SQL Server Agent, giving permission for the computers in my security group to access the passwords, and specifying that I want the passwords to be cycled every 30 days.
New-ADServiceAccount -Name SQLgMSA -PrincipalsAllowedToRetrieveManagedPassword gsg_SQLgMSA -Enabled:$true -DNSHostName SQLgMSA.cloudrede.com -SamAccountName SQLgMSA -ManagedPasswordIntervalInDays 30
New-ADServiceAccount -Name SQLAgMSA -PrincipalsAllowedToRetrieveManagedPassword gsg_SQLgMSA -Enabled:$true -DNSHostName SQLAgMSA.cloudrede.com -SamAccountName SQLAgMSA -ManagedPasswordIntervalInDays 30
Step 4 – Use your gMSA service accounts
Once the accounts have been created you can use them straight away, just don’t forget to add a $ at the end so SQL Server knows it’s a managed service account.
You can use them during install by not specifying a password or change an existing installation from SQL Server Configuration Manager (rather than the services applet) so that SQL Server can apply the correct permissions.
In the below example, I’m changing the service account for a SQL Server instance to use one of my new accounts using SQL Server configuration manager.
What’s the best practice for gMSA accounts and multiple SQL Server instances?
You’ll make life easier for yourself if you use seperate accounts for SQL Server and SQL Server Agent on each instance. That way you can apply specific permissions to each service account on each instance. However, if you have SQL Server instances that run the same workload you might want to use the same SQL Server and SQL Server Agent accounts across them all because the permissions would be the same. For example, in an AlwaysOn Availability Group cluster I would use the same gMSA accounts across all the failover nodes because I want them to be equivalent (as I did in Step 2 above).
If you get stuck, feel free to post a question below and I’ll do my best to help or if you’re tired of having to worry about all this stuff yourself we also offer a Managed DBA Service for SQL Server where we’ll take care of all this for you!
