Using Group Managed Service Accounts with SQL Server

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. Use Active Directory Security Groups to allow a one-to-many relationship between an account and the computers.

Group Managed Service Account Prerequisites

Prerequisites for Managed Service Accounts with SQL Server:

• Domain Functional Level of 2012 or higher
• SQL Server 2014 or higher
• Window Server 2012 R2 Operating System
• Active Directory PowerShell module installed

With Windows Server 2012 R2, install KB 2998082 for SQL Server to use a gMSA as its service account.

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

Use the Active Directory PowerShell module to create Group Managed Service Accounts. There is no facility to do this in the Active Directory Users and Computers admin tool. Install the PowerShell module on the workstation used to create the accounts and the servers where the accounts will be used.

Installing Active Directory PowerShell Module

Use the following PowerShell command to install the AD PowerShell module on a server:

If needed, download the install for Remote Server Administration Tools (RSAT) onto a Windows 10 workstation.

Creating Active Directory Group

This is where group Managed Service Accounts (gMSA) differ from Managed Service Accounts (MSA). Use the following process to facilitate the one-to-many relationship between gMSA and computers:

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 create the group, add the computer objects as members of the newly created group, then check the group members. Alternatively, use 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 computer objects the name of the computer account needs to be suffixed with a $
• Restart the computer needs to force group membership

Creating the gMSA

Complete prerequisites then create the account using PowerShell using the following command:

New-ADServiceAccount -Name gMSA01 -PrincipalsAllowedToRetrieveManagedPassword gsg_gMSA01 -Enabled:$true -DNSHostName gMSA01.demo.lab01 -SamAccountName gMSA01 -ManagedPasswordIntervalInDays 30

The group created earlier has the PrincipalsAllowedToRetrieveManagedPassword parameter and password rotation specified. The SQL Server services can now use the gMSA.

Installing and Using Group Managed Service Accounts

Once created, install MSA on the needed servers. To use PowerShell, install the Active Directory PowerShell module for SQL Server. 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

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. Previously John has worked as a product manager for SentryOne and a Premier Field Engineer with Microsoft UK.

Having over a decade of experience working with SQL Server and the Microsoft Data Platform. Working as a DBA, Developer and Consultant for Microsoft, John has been lucky enough to see how best, and how not, to use SQL Server and the Data Platform effectively.