By: John Martin | Comments (7) | Related: > Security
Problem
In my previous tip about Using Managed Service Accounts with SQL Server, we discussed how to create and use these accounts. But what if you have Failover Cluster Instances or Always On Availability Groups and you want to gain the benefits of using a Managed Service Account?
In this tip, we will look at how group Managed Service Accounts (gMSA) can help solve these problems.
Solution
Whereas SQL Server 2012 only supports the use of Managed Service Accounts (MSA), SQL Server 2014 introduced support for group Managed Service Accounts when running on Windows Server 2012 R2 and above.
Group Managed Service Accounts solve the problem of one-to-one relationships between MSA and Computer. This is done by making use of Active Directory Security Groups to allow a one-to-many relationship between an account and the computers.
Group Managed Service Account Prerequisites
To be able to make use of Managed Service Accounts with SQL Server there are certain prerequisites that need to be met, these are as follows:
- Domain Functional Level of 2012 or higher
- SQL Server 2014 or higher
- Window Server 2012 R2 Operating System
- Active Directory PowerShell module installed
If you are using Windows Server 2012 R2 as the operating system, for SQL Server to be able to use a gMSA as its service account KB 2998082 needs to be installed.
Prior to being able to create a gMSA in the domain, the Key Distribution Service needs to have a root key in place to function. This is completed by issuing the following PowerShell command:
Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyId
If there is not a valid KDS Root Key then use the following to create one:
Add-KdsRootKey -EffectiveImmediately
Be aware that even with the EffectiveImmediately configuration switch it can take up to ten hours for the key to become active and allow the creation of the group Managed Service Accounts.
Creating Group Managed Service Accounts
Group Managed Service Accounts are created via the Active Directory PowerShell module as there is no facility to do this in the Active Directory Users and Computers admin tool. The PowerShell module will need to be installed on the workstation that will be used to create the accounts as well as the servers that the accounts will be used on.
Installing Active Directory PowerShell Module
Installation of the AD PowerShell module on a server operating system is done using the following PowerShell command.
If you need to install the Remote Server Administration Tools (RSAT) onto a Windows 10 workstation then they can be downloaded here.
Creating Active Directory Group
This is where group Managed Service Accounts (gMSA) differ from Managed Service Accounts (MSA). To facilitate the one-to-many relationship between gMSA and computers this is achieved via the following process:
- Create Active Directory Security Group
- Add computer objects to Security Group
- Create gMSA and specify Security Group to link the account and computers
The following commands are used to create the group, add the computer objects as members of the newly created group, then check the group members. Alternatively, this can be done via the Active Directory Users and Computers admin tool.
New-ADGroup -Name gsg_gMSA01 -Description “Security group for gMSA01 computers” -GroupCategory Security -GroupScope Global Add-ADGroupMember -Identity gsg_gMSA01 -Members winsql-0$, winsql-01$ Get-ADGroupMember -Identity gsg_gMSA01
There are two important things to account for in this process. When adding the computer objects the name of the computer account needs to be suffixed with a $ and in order to force the group membership to be picked up the computer needs to be restarted.
Creating the gMSA
Once all the prerequisites are completed the account can be created using PowerShell, this is achieved with the following command:
New-ADServiceAccount -Name gMSA01 -PrincipalsAllowedToRetrieveManagedPassword gsg_gMSA01 -Enabled:$true -DNSHostName gMSA01.demo.lab01 -SamAccountName gMSA01 -ManagedPasswordIntervalInDays 30
You will notice here that the group created earlier is specified for the PrincipalsAllowedToRetrieveManagedPassword parameter, as well as being able to specify how often the password should be rotated. Now that the gMSA has been created it can be used by the SQL Server services on our servers.
Installing and Using Group Managed Service Accounts
Once the MSA has been created, it needs to be installed on the server that it will be used on. To do this, the Active Directory PowerShell module will need to be installed on the SQL Servers. Make sure the AD PowerShell cmdlets are installed, you can now log in to the server.
Install-ADServiceAccount -Identity gmsa01 Test-ADServiceAccount -Identity gmsa01
Once the gMSA is set up and linked to the computer, it is time to use this as the service account for SQL Server. There are two options for updating the service account details. The first is to use the SQL Server Configuration Manager tool. In this instance you will need to make sure that the “Service Accounts” object type is selected for account validation:
The other option, and the one that I prefer is to make use of the Update-DbaSqlServiceAccount cmdlet from the DBATools PowerShell module. By making use of the DBATools module, you can easily script the entire process – from MSA creation all the way through to implementation.
When using this cmdlet, it is important to remember to qualify the account with the domain name, as well as appending the account name with a $ (which denotes it as a gMSA).
Summary
Here we have looked at how to set up SQL Server to use a gMSA as its service account and be able to take advantage of the capabilities that come with it. This includes easy password rotation using the Reset-ADServiceAccountPassword, managing SPNs and ensuring it is restricted to the server that it is linked to.
It is important to remember, however, there are some limitations, notably that SQL Server 2014 or higher is required to support the use of gMSAs. However, unlike MSAs gMSAs can be used with Failover Cluster Instances and are a great option for Availability Group scenarios.
Next Steps
When setting up SQL Server to make use of Managed Service Accounts you should check out these additional tips that cover a range of recommended practices.
- SQL Server Installation Best Practices
- How to Create Secure SQL Server Service Accounts
- Running SQL Server Agent with a least privilege service account
- Register a SPN for SQL Server Authentication with Kerberos
- Why a System Account is a bad idea for SQL Server Service Account
- SQL Server Service Account Privileges
- Using Managed Service Accounts with SQL Server
About the author
John Martin is a Data Platform Engineer working in the financial sector as well as Director-at-Large for the PASS organisation and currently a Microsoft Data Platform MVP.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
