Using Group Managed Service Accounts with SQL Server

By:   |   Comments (7)   |   Related: > Security


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.


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
Testing for KDS Root Key - Description: Check to see if KDS Root Key exists.

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.

Install AD PowerShell Module - Description: Installation of AD PowerShell module via PowerShell.

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:

  1. Create Active Directory Security Group
  2. Add computer objects to Security Group
  3. 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
distinguished name

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
managed service accounts

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
Install and Test gMSA - Description: Install and Test that the group Managed Service Account is in place.

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:

Select Service Accounts object type - Description: Selecting the Service Accounts object type is needed in order to do name validation via the Service Configuration Management tool.

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.

Setting Service Accounts with DBATools

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).


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 categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author John Martin 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

Comments For This Article

Saturday, March 9, 2024 - 12:00:19 AM - curtiss Back To Top (92053)
"his includes easy password rotation using the Reset-ADServiceAccountPassword, managing SPNs "

the summary is the first time "SPN" appears on this page. where is the actual section on managing spns for gmsas?

Wednesday, November 2, 2022 - 11:04:06 AM - Jean Yves Back To Top (90656)

Nice article, indeed. Neverthless, I can't make GMSA account work in combination with SQL Server's ServiceBroker feature.
Error says that GMSA account is not allowed to be granted CONNECT TO ENDPOINT permission :/

Wednesday, October 26, 2022 - 4:26:31 PM - TBWork Back To Top (90637)
You might want to do a quick refresh on this article: With gMSA you no longer need to run this command: "Install-ADServiceAccount -Identity gmsa01", which makes it much easier to deploy as you don't have to install AD PowerShell stuff on each target host. Although you do need to reboot the host(s) in the security group that you grant -PrincipalsAllowedToRetrieveManagedPassword to, before they can start using the new gMSA.

Friday, March 27, 2020 - 11:13:36 AM - Steve Marak Back To Top (85205)

Very well-written and complete, thank you!

Tuesday, February 4, 2020 - 1:56:57 PM - Aleksander Back To Top (84153)

What "-DNSHostName", I should use, when I have SQL FailOver Cluster? Is this Cluster network name?

Saturday, May 5, 2018 - 12:47:19 PM - Jan Jansen Back To Top (75871)

 If I look at this site,

if i have to update the acl to allow this account to register it's spn's. 

The parameter -DNSHostName gMSA01.demo.lab01 is wrong I think, as far as i understood, it should be pointing to the DC

Another thing is that I am confused about the part of installing the RSAT-ADPOWERSHELL on every machine and install the ADServiceAccount. If I don't do this, then I can still see (onWin10) my global service account and assign it to a service.


Friday, May 4, 2018 - 10:04:08 AM - Jan Jansen Back To Top (75867)


nice post. I got this configured, and i have changed the account through the SQL Server Configuration Tool. This works OK, but when the SQL Server restarts, it can not register the spn's MSSQLSvc/ It fails with the Windows return code 0x200b, state 15. The domain level functionality is Windows Server 2016

Do you have any idea.


get free sql tips
agree to terms