Group Managed Service Accounts (gMSA) and SQL Server 2016


This 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

There are two main drivers behind the development of Group Managed Service Accounts (gMSA’s) for services such as SQL Server:

  1. They remove the need to manage the service accounts with respect to the overhead of service account password management.
  2. Service Principal Names (SPNs) registration can be done automatically.

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.

Likewise the overhead of SPN registration and management is tangible. SPNs can only be registered by accounts with Domain Admin level permissions – although it is possible to delegate the specific permission required. Many environments are not keen on delegating this permission, especially when there are hundreds to thousands of domain accounts involved.

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.  There is also no longer a need to restart the SQL Server service after a service account password reset, which prevents downtime, etc.
  2. By delegating the SPN registration permission to the gMSA, there is no vulnerability associated with a human being using the service account to cause problems by registering duplicate or even bogus SPN’s, 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.

 

 

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.

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: http://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.

 

Create a Global Security Group in Active Directory Users and Computers.

  • 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:

NewObjectGroup 2

 

 

  • 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}

 

 

 

UsersAndComputers 2

 

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

 

 

 

 

 

Create the gMSA account

  • 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

 

Grant the gMSA account the “Validated write to service principal name” permission

  • One of the main benefits of MSA/gMSA is that these service accounts have the ability to register and deregister the SPN’s for the services that use them. This is normally done with Domain Admin rights.
  • In order for the gMSA to be able to register/deregister SPN’s, it will need the “Validated write to service principal name” permission granted to it.
  • This is an optional step for using gMSA service accounts. If Kerberos authentication is not going to be used, or if delegating this permission is not possible, then the steps below are not necessary.
    • In Active Directory Users and Computers, right click on the domain and go to Properties/Security.
    • Click Advanced and on the Permissions tab, click Add
    • At the top, click the “Select a principal” link to open the Select User, Computer, Service Account, or Group dialog box.
    • Click Object Types…, ensure Service Accounts is checked and hit OK.
    • Enter the gMSA account in Enter the object name to select and then Check Names, then click OK.
    • In Applies to: choose Descendent Computer objects.
    • Under Permissions, locate the “Validated write to service principle name” option, check the box.

PermissionsEntry

 

 

 

 

  • Click OK three times to close all dialog boxes.

 

 

 

Configure and validate the gMSA service account on the member servers.

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

Configure SQL Server to use the gMSA service account

  • 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

 

We can check that the SPN was correctly registered using the following command:

Setspn –L gMSAsqlservice

This should return two registered SPN’s for the instance:

MSSQLSvc/W12R2-C3N1-S16.contoso.com:49514

MSSQLSvc/W12R2-C3N1-S16.contoso.com:I01

 

 

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.

Note that this can, and likely will, unregister any SPN for the instance in AD. So if Kerberos authentication is required, the SPN will need to be re-registered.

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 effort of evaluation.

 

Completely Off Topic

HazelCoppice“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

 

Comments (2)

  1. Vemund Haga says:

    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.

  2. Jose Antonio says:

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

Skip to main content