Infrastructure at your Service

David Barbarin

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:

New-ADServiceAccount sql_alwayson `
   -DNSHostName dc.dbi-services-test `
   -PrincipalsAllowedToRetrieveManagedPassword "Domain Controllers", "CN=AlwaysOnMSA,OU=Clusters,DC=dbi-services,DC=test" `
   -KerberosEncryptionType RC4, AES128, AES256

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;

blog 72- 1 - GetADServiceAccount

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

Get-ADServiceAccount -Identity sql_alwayson –Properties PrincipalsAllowedToRetrieveManagedPassword;

blog 72- 2 - GetADServiceAccountMembers

… And computers authorized to use it. In my case, the WIN20161 and WIN20162 replicas and my availability group listener LST-20161 as well.

Now it’s time to configure the gMSA on each replica (WIN20161 and WIN20162) by using these PowerShell commands:

Enable-WindowsOptionalFeature -FeatureName ActiveDirectory-Powershell -Online –All;

Install-AdServiceAccount sql_alwayson;

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

blog 72- 3 - SQLService Account

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:

blog 72- 4 - startupwin20161

blog 72- 5 -  startupwin20162

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:

Set-ADServiceAccount -Identity sql_alwayson `
-ServicePrincipalNames @{Add="MSSQLSvc/LST-2016.dbi-services.test:1433"}

Let’s take a look at the SPNs associated with our gMSA :

blog 72- 6 - spn

And finally let’s verify if we are able to connect with Kerberos authentication.

I ran a PowerShell script that tests the connections to the replica and the listener as well.

blog 72- 7 - powershell program

Let’s verify the protocol type used from the SQL Server side:

select
	c.session_id,
	c.net_transport,
	c.protocol_type,
	c.auth_scheme,
	s.program_name
from 
	sys.dm_exec_connections c
join 
	sys.dm_exec_sessions s
		on c.session_id = s.session_id
where
	s.program_name like 'Powershell program%';
go

blog 72- 8 - SQL Server kerberos

Et voilà!
 

Leave a Reply


3 − = one

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader