Group Managed Service Accounts (gMSA) and SQL Server 2016

This updated post comes from another colleague of mine, Norm Eberly.  Norm is a dedicated Premier Field Engineer for Microsoft.  An overt anglophile and avid Alaskan angler, he lives near Seattle and has been working with SQL Server since 1994.  His experiences include database administration, external storage subsystems, consulting, and support engineering.  Norm’s expertise is in performance tuning, operational excellence, high availability, and functional business expertise.

By Norm Eberly

A major reason behind the development of Group Managed Service Accounts (gMSAs) for services such as SQL Server is that they remove the need to manage the service accounts with respect to the overhead of service account password management.

Managed Service Accounts (MSAs) are also designed to address these two issues. However, MSAs are limited to a single computer account – they cannot be used as the service account for a SQL Server failover clustered instance which can run across multiple Windows servers.

Group Managed Service Accounts extend MSA functionality to cover multiple servers.

See the following reference for a more detailed discussion about gMSAs: https://technet.microsoft.com/en-us/library/hh831782.aspx

A major pain point in environments with a large number of SQL Server instances deployed is managing the service accounts according to published best practice guidelines, especially when the service accounts are domain accounts:

• Each service should be using a different service account (to prevent the compromise of all services using the same service account if one service account is compromised).
• Each service account should have its passwords managed in accordance with domain account policies (changing every 90 days for example)

Imagine the administrative overhead of having to manage 1000 separate domain accounts and their passwords. While some of the tasks can be automated, there is still overhead and coordination to ensure passwords meet complexity requirements as well as usage history. A single PowerShell script would have to be able to connect remotely to all of the relevant servers in order to access the WMI service on each server to change the password’s for the services programmatically.

Under normal circumstances, it is not unusual for domain account service accounts and their passwords to be known by the service administrators, after all they are usually the people responsible for setting and maintaining them. And at some level having even administrators know these accounts and passwords may be considered a security vulnerability.

In response to the challenges with password management, many environments compromise by using a single domain account as the service account for all of their SQL Server instances. Many also take a different approach to password policy for service accounts (perhaps they allow the same password for 12 months rather than 90 days, etc.).

Group MSA’s address both of these:

1. By automating the process within Active Directory for the password management. Passwords are very complex and changed automatically as often as desired (by default every 30 days). The passwords are cryptographically random and 240 bytes long. In addition, they cannot be used to interactively logon. Nor can they be locked out.
2. There is also no longer a need to restart the SQL Server service after a service account password reset, which prevents downtime, etc.

This is a step-by-step implementation of Group Managed Service Accounts (gMSAs) for use as the service account for SQL Server 2016.

This implementation is done using Windows Server 2012 Active Directory domain controllers (DCs), all servers running Windows Server 2012 or Windows Server 2012 R2, and SQL Server 2016 CTP 3.2.

Note that SQL Server 2014 and SQL Server 2016 both support the use of gMSAs on Windows Server 2012 R2 and later for standalone instances, failover clustered instances and availability groups.

See the following two Books Online references for more information:

SQL Server 2014; go HERE and see the section under Group Managed Service Accounts.

SQL Server 2016; go HERE and see the section under Managed Service Accounts, Group Managed Service Accounts, and Virtual Accounts.

The procedure provided in this post was also successfully accomplished with SQL Server 2014.

Prerequisites

In order to utilize gMSA accounts, there must be at least one Windows Server 2012 (or R2) DC in the domain. There is no forest or domain functional level requirement.

• If deploying a Windows Server 2012 R2 DC, be aware of the following hotfix that resolves a problem with connecting after a password change: https://support.microsoft.com/en-us/kb/2998082
• There are more details on prerequisites located here: https://technet.microsoft.com/en-us/library/jj128431.aspx#BKMK_gMSA_Req

The Key Distribution Services (KDS) Root Key needs to be created before a gMSA can be created. This is done via a PowerShell command and requires Domain Administrator or Enterprise Administrator level privileges.

• See https://technet.microsoft.com/en-us/library/jj128430.aspx for details and steps
• Note that there is a 10 hour lag between the time the KDS root key is created and the time a gMSA can be created. This is to allow full replication between the Windows Server 2012 DC’s in order to allow password retrieval to work as necessary.
• There are steps at the above reference to allow the use of the root key immediately for testing purposes.
• This requires a 64-bit environment, but only has to be done on one Windows Server 2012 R2 DC.

gMSA Implementation for SQL Server 2016

This section was developed using the steps outlined in the following blog post: https://blogs.technet.com/b/askpfeplat/archive/2012/12/17/windows-server-2012-group-managed-service-accounts.aspx

It was not necessary to accomplish every step in the blog post and this section discusses these areas where necessary.

• This step is actually optional, but it allows for easier management of the necessary rights required to use the gMSA for the member servers.
• Note also that there will be a reboot requirement for the member servers added to this Security Group.

In Active Directory Users and Computers, under the domain where the gMSA is to be created, right click on Computers, New and Group. This will open the New Object – Group dialog:

• Enter a Group Name, Group scope should be Global and Group type is Security. In this demo, we will use SQLServers
• Open the newly created Security Group by double clicking on it and go to the Members tab. Or right click on the Security Group and go to Properties then the Members tab.

{Note - Click on Images to Expand}

• Click Add and add the domain member servers that will be hosting the SQL Server instances that will be using the gMSA. In this demo, I added all of the member servers that will be running SQL Server:

Note that these servers will require a reboot in order for their tokens to pick up membership in the group.

This group will be given specific rights to its members that will allow the member servers to retrieve the gMSA password.

• This must be done with a PowerShell script in a PowerShell session that also has the Windows Server 2012 AD cmdlets available (it does not need to be done on a DC). See https://technet.microsoft.com/en-us/library/dd378937(v=ws.10).aspx for guidance on installing the AD Powershell module.
• The command that creates the gMSA will also grant the right to retrieve the accounts password to the members of the Security Group created earlier
• This is the PowerShell command used:

New-ADServiceAccount -name gMSAsqlservice -DNSHostName gMSAsqlservice.contoso.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers

• This is a step that might not need to be done: the accounts may already be configured on the member servers when they were rebooted. However, the steps are quite quick and the validation of the account should be considered necessary. These commands also require the AD module for Powershell.
• To configure the gMSA account, run the following PowerShell command on the member server: Install-ADServiceAccount gMSAsqlservice
• To validate the gMSA account, run the following PowerShell command on the member server: Test-ADServiceAccount gMSAsqlservice
  • This should return True.

• Start SQL Server Configuration Manager
• Under SQL Server Services, right click the instance of SQL Server you want to assign the service account to and go to Properties
• In Log On tab, choose “This account”.
• In Account Name enter the domain account and include a “$” after the gMSA name:
  • contoso\gMSAsqlservice$
  • The “$” may automatically be added for you.
  • Do not enter a Password, it will be retrieved automatically from AD.
• Start or Restart the SQL Server service

Using gMSA accounts during SQL Server 2016 installation

We can also designate the gMSA account during the SQL Server 2016 setup process. Just enter the domain account, in our example contoso\gMSAsqlservice$, as the Account Name on the Service Accounts page of the setup process. The setup process does not prompt for a password as it checks with Active Directory for the correct authentication, etc. and setup completes as expected.

Returning to Non-gMSA Service Accounts

To return to using non-gMSA service accounts, just use the SQL Server Configuration Manager to set the new service account and password. A SQL Server service restart will be required.

Summary

With the release of SQL Server 2016, SQL Server service account management becomes much easier with Group Managed Service Accounts. Gone are the tedious planning and implementation phases of changing accounts and/or passwords, requiring SQL Server service restarts and then troubleshooting when things go wrong.

If your goal is to reduce management and administrative overhead while at the same time reducing security vulnerability, MSA and gMSA service accounts might well be worth the modest effort of implementation.

"Coppice" is a word that describes a growth of trees or shrubs that have been cut back periodically to stimulate growth and to harvest wood. Coppice is also used to describe the act of this periodic cutting. Coppicing continues to be practiced in many parts of the world for both gardening and commercial purposes.

View some coppiced woodlands

• Anonymous
  June 04, 2016
  Hello!Great to finally have gMSA working for SQL Server and thank you for a good post describing how to configure it.If you cannot follow the steps in "Grant the gMSA account the “Validated write to service principal name” permission", you need to turn on Advanced features in Active Directory Users and Computers. Right-click the domain and choose All Tasks - Advanced features.
• Anonymous
  June 27, 2016
  Hi, I follow your instructions, all work less spn registration: "2016-06-27 12:04:41.25 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/ ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered."