Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Group Managed Service Accounts with SQL Server


By:   |   Updated: 2018-02-23   |   Comments (2)   |   Related: More > 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
			
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).

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.



Last Updated: 2018-02-23


get scripts

next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, May 05, 2018 - 12:47:19 PM - Jan Jansen Back To Top

 If I look at this site,

https://blog.coeo.com/jongurgul/2017/04/04/managed-service-accounts-with-sql-server

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 04, 2018 - 10:04:08 AM - Jan Jansen Back To Top

 Hi,

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/Host.contoso.com:SQL2016. It fails with the Windows return code 0x200b, state 15. The domain level functionality is Windows Server 2016

Do you have any idea.

 


Learn more about SQL Server tools