SQL Server 2016 – availability groups and gMSAs

This blog post will probably not concern customers where either password policy change rules are not defined for service accounts or Kerberos authentication is not used. But I’m sure that the number of these customers has decreased over at least the last decade. By the way, this is what I can notice at different customer places.

But before beginning with group managed services let’s introduce managed services … Why using managed services (MSAs) against traditional or “legacy” service accounts? Well, I think that system administrators may imagine how important this account type is.

Let’s set the scene: as a domain administrator, you’re in charge to create a domain account for one or maybe several servers. In most cases, you will have to provide a domain user name and a password as well. After some times, because your security policy requires to change all of the domain account passwords in your active directory every month, the service account you’ve created previously expires and no luck … the concerned application doesn’t work anymore. What are the alternatives in this case? Change the password manually? Maybe but each month you’ll have to execute the same task and coordinate with the application team in order to avoid the application failure. According to me, this not a good solution. Another way may be to exclude this service account from your security policy with no expiration password for your domain account. Great but what about password attacks from hackers?

This is where MSAs may be very useful in this context. System administrators don’t have any more to manage the password by themselves. The concerned server will refresh automatically the password in monthly-basis by default (note that a system administrator may still refresh the password manually).

Another improvement for system administrators is the simplified management of service principal names (SPNs) with Kerberos authentication. With “legacy service accounts”, you will generally provide at least privilege domain account for SQL Server according to the security best practices but in this context the domain account will be unable to configure or update automatically the corresponding SPN for the SQL Server instance. Once again, this is a task that must be performed by the system administrators for each SQL Server that will use this domain account and Kerberos authentication. Fortunately, when you use MSAs with SQL Server, the corresponding SPN is automatically managed by the system.

However the main problem of using MSAs is their limited scope because they cannot be shared across multiple hosts. If you use AlwaysOn availability groups, it means that you will have to setup as many managed service accounts as you have replicas in your infrastructure. Fortunately, Windows 2012 came to the rescue and has introduced the group managed service accounts (gMSAs) that extends the capabilities of the managed service accounts feature. A single gMSA can be shared across multiple hosts … very interesting for our availability group environment. But unfortunately this feature is only supported from SQL Server 2016.

The most of work for configuring gMSAs concerns the active directory for which I’m not an expert, so the following task was performed blindly according to the Microsoft documentation.

First, you have to create the KDS root key from a domain controller. Indeed, password management is now performed by the key distribution service (KDS) and member hosts that wish to use the gMSA simply query the DC to get the password. Usage of the gMSA is restricted to only granted computers specified in the msDS-GroupMSAMembership security descriptor. The DC will also determine if the password must be changed and uses a pre-determined algorithm to compute the password (120 characters).

So let’s create the KDS root key by using the following PowerShell command:

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10));

The last part of this script is a trick for using immediately the KDS root key but don’t use it in production!

Let’s continue and let’s create our gMSA by using the following PowerShell command:

I created a group named sql_always with several possible Kerberos encryption types (RC4, AES128 and AES256). In addition, domain controllers and computers that are members of the AlwaysOnMSA group are only authorized to use the sql_always group managed service account.

Get-ADserviceAccount -Identity sql_alwayson;

Ok, my gMSA has been created correctly. You can notice the new object class msDS-GroupManagedServiceAccount …

Next let’s use this gMSA with the SQL Engine services on each replica as shown below:

As MSA, you don’t have to enter the password because it is directly managed by the system. Let’s talk about the concept of “Automatic SPN management”. According to NedPyle (MSFT) here, it refers to an application that inherently understands Kerberos and how to register itself with SPN’s. If that application supports writing its own SPN’s, *and* you use 2008 R2 DC’s, MSA’s will work for automatic SPN management. However in my case, the gMSA that I’ve created previously didn’t manage automatically the SPN with SQL Server. In fact, I had to add the “Read servicePrincipalName” to the gMSA before it works properly. Did I miss or misunderstand something? I will update this blog post accordingly.

Let’s take a look at the SQL Server error log of each replica:

…

Each replica (respectively WIN20161 and WIN20162) has successfully registered their SPN. However, this is another story for the listener because it is not used directly as a service. We must add manually the corresponding SPN to the gMSA by using the following script: