Notes From A SQL 2017 Azure Install – Using Group Managed Service Accounts

One of the new features that I’m excited about implementing on my Windows 2016 SQL Server 2017 servers is the ability to use Group Managed Service Accounts, or “gMSA” for short.  This isn’t really a new feature, I believe it was first made available for SQL Server 2014 running on Windows 2012 R2.  But since I’m upgrading from a Windows 2008 R2 environment, this a “new” feature for me.

For those that aren’t familiar, a gMSA is a special type of domain account created and managed by the domain controller, that can be used across multiple servers.  You cannot use a gMSA to log into a computer, but a computer can use a gMSA to run a Windows service such as SQL Server.  The password is managed automatically by the domain controller, and can be updated without the need to restart the service using it.  Passwords are very complex and changed automatically every 30 days.  This is great because it improves security, while also removing the need to manage the SQL service accounts.  Who doesn’t need stronger security with less work?

Below is an excellent tutorial I used to get my SQL Server services using a gMSA, followed by a few of my own notes regarding issues I ran into.

Group Managed Service Accounts (gMSA) and SQL Server 2016

Notes:

• As specified in the above tutorial, a Key Distribution Services (KDS) Root Key needs to be created before a gMSA can be created.  And when it says you have to wait 10 hours after creating the KDS, you really do have to wait 10 hours.  If you don’t, you will receive a “Key does not exist” error when trying to create the gMSA.
• When using a gMSA with SQL Server Availability Groups (or anything else that uses a Hadr_endpoint endpoint), you need to make sure to grant CONNECT rights for the gMSA on the endpoint on every SQL Server in your Availability Group.  Otherwise you will receive this error:
  

  Msg 35250 The connection to the primary replica is not active. The command cannot be processed.

  To do this, you’ll need to create a login for the gMSA and then grant the rights:

  USE MASTER GO CREATE LOGIN [YourDomain\gMSAname$] FROM WINDOWS; GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [YourDomain\gMSAname$];

  1 2 3 4 5 6 7 8

  USE MASTER GO   CREATE LOGIN [YourDomain\gMSAname$] FROM WINDOWS;   GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [YourDomain\gMSAname$];

• When verifying the gMSA on each SQL Server (Step 3 in the above tutorial), you may receive this error:
  

  Install-ADServiceAccount : The term ‘Install-ADServiceAccount’ is not recognized as the name of a cmdlet, function, script file, or operable program.

  This means you need to install the ActiveDirectory module in PowerShell.  You do this by running the Import-Module ActiveDirectory cmdlet, which will most likely lead to this error:

  Import-Module : The specified module ‘ActiveDirectory’ was not loaded because no valid module file was found in any module directory.

  This means you are missing some prerequisites for the ActiveDirectory module. You can install them following these instructions, which are found in the first part of this tutorial.

  1. First, run the Import-Module ServerManager cmdlet.
  2. Then run the Add-WindowsFeature RSAT-AD-PowerShell cmdlet to install the Active Directory module
  3. Close and re-open PowerShell
  4. You should now be able to run the Import-Module ActiveDirectory cmdlet
  5. You should now be able to run the validations listed in step 3 of the tutorial above.

Even though I ran into a few problems getting everything set up, I feel like Group Managed Service Accounts are a really great feature, and I’m looking forward to having a more secure service account running SQL Server, without having to worry about password updates.

This post is part of an ongoing series of blog posts related to my Azure SQL Server 2017 Migration.