Kaloyan Kosev

Using Group Managed Service Accounts with SQL Server

March 14, 2017 by

Standalone Managed Service Accounts, introduced a long ago with Windows Server 2008 R2, were a ray of hope for the database administrators. They promised to provide automatic password management and simplified SPN management, meaning that the time-consuming task of maintaining passwords would be a thing of the past (not to mention the required downtime for this).

It also meant that we could finally forget another scenario – having the same domain service account we use for all of our SQL Servers with very high privileges not only within the AD but among numerous Windows Servers which password we have not changed since it was created. The problem with the standard Managed Service Accounts is that they can only be used in a one to one ratio as a single account can be used on only one host / computer.

With Windows Server 2012 the Group Managed Service Accounts were introduced, it provides the same functionality within the domain, but also provides the possibility to use it over multiple servers.

They can be used for standalone and clustered installations of SQL Server. AlwaysOn Availability Groups are also an option, although it requires additional configurations. It is as simple as it sounds – once configured the Active Directory takes care for the password change every 30 days (default) without the need of administrator’s intervention or SQL Server service restart.

The password for the gMSAs (Group Managed Service Accounts) are generated and maintained by the Key Distribution Service (KDS, kdssvc.dll) on the Active Directory Domain controllers. This allows multiple Windows Servers to use the same gMSA account, the usage is, of course, restricted and only the computer objects assigned can query the password.

Group Managed Service Accounts can only be configured and administered on hosts running Windows Server 2012 and are not applicable on to other Windows operating systems prior to Windows Server 2012, the other requirements are as follows:

  • The AD forest functional Level should be updated to Windows Server 2012 (you can still have DCs running Windows Server 2008/R2);
  • You need to create a Key Distribution Service root key and you need to wait 10 hours for it to be replicated on all domain controllers;
  • You can only use Group Managed Service Accounts on Windows Server 2012 hosts;
  • The full list of requirements can be seen here – Getting Started with Group Managed Service Accounts;

Let us configure and setup the usage of Group Managed Service accounts, note that some, if not the most of the actions require elevated permissions within the Active Directory domain.

  1. Create the KDS root key

    If not already created, a KDS root key is required. We can add it using the PowerShell cmdlet “Add-KdsRootKey”. Once the key is created we must wait for 10 hours in order for it to be fully synchronized between all AD domain controllers. In a test environment we can set it to be effective immediately using the “-EffectiveImmediately” parameter.

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

  2. Configure the AD

    We will create a new AD group that will contain all our SQL Servers that will be using the Group Managed Service Account. Although an account can be linked directly with multiple hosts it is much easier for administrating it using an AD container.

    Once the group, in our scenario “ProductionSQLServers” is created we need to add the computer objects of the servers where we will use the gMSA account.

    The computer object “MIA-SQL” is now member of the newly created group. Note that a reboot is required for the hosts once we move them in the new group.

  3. Creating the Group Managed Service Account

    The creation of the account requires the PowerShell cmdlets available, the creation can be done on a DC or on another server.

    You will need the AD PowerShell cmdlets later in order to install and validate the account on the hosts, for more information check “How to add the AD PowerShell cmdlets” at end of the article;

    New-ADServiceAccount -name PrdSQLgMSAsvc -DNSHostName PrdSQLgMSAsvc.adventureworks.msft -PrincipalsAllowedToRetrieveManagedPassword “ProductionSQLServers”

    The Group Managed Service Account is now created, using the parameter “-PrincipalsAllowedToRetrieveManagedPassword” we have granted the rights to query the account password to the security group “ProductionSQLServers” that we created in the previous step.

    The account will now also be visible under “Managed Service Accounts” within the AD Administrative Center:

  4. Configure the gMSA on the target hosts

    Although the following can be considered optional it is rather important to confirm that all prerequisites are met and the gMSA is working correctly.

    The “Install-ADServiceAccount” cmdlet also makes the required changes locally so that the service account password can be periodically reset by the computer without requiring additional actions by the administrator.

    To configure and validate the gMSA account we will use the following PowerShell commands on the target host:

    Install-ADServiceAccount PrdSQLgMSAsvc
    Test-ADServiceAccount PrdSQLgMSAsvc

    The expected result is “True” – meaning the Group Managed Service Account is now configured and ready for use.

    We can use the account for new installations of SQL Server or for the already existing instances.

    When installing a new SQL Server true the Setup Wizard using Group Managed Service Account requires only to specify the account, in our scenario – “ADVENTUREWORKS\PrdSQLgMSAsvc$”. The wizard will not prompt you for password when using gMSA account as it checks the account type within the AD directory. If you miss adding the “$” to the account name it will be automatically added for you.

    I order to change the service account for an existing SQL Server locate the service within the SQL Server Configuration Manager, and go into the Properties.

    Under the logon section choose “Browse” in order to change the account. In similar fashion like in the setup wizard, we can choose the “ADVENTUREWORKS\PrdSQLgMSAsvc$” account. Once we have specified the gMSA account the password fields are blank and available for edit – do not enter password, it will be automatically obtained from the AD.

    Start the service if you have stopped it or restart it – the SQL Server is now configured to run under a Group Managed Service Account.

    If we are installing SQL Server from the command line we can skip the password parameter and only provide information about the account –
    SQLSVCACCOUNT=”ADVENTUREWORKS\PrdSQLgMSAsvc$”.

    Next I’ll show how to add the AD PowerShell cmdlets:

    First, within the Server Manager console open the “Add roles and Features Wizard” and locate the “AD DS Snap-Ins and Command-Line Tools”, “AD LDS Snap-Ins and Command-Line Tools” and “Active Directory module for Windows PowerShell”. The feature installation does not require restart.

    Then import the PowerShell module as follows:

Kaloyan Kosev
Latest posts by Kaloyan Kosev (see all)

Related posts:

  1. How to secure Reporting Services with Group Managed Service Accounts (GMSA)
  2. Domain Controller and Domain Client Node setup for AlwaysON High Availability
  3. Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
  4. Top 50 PowerShell bloggers of 2018
  5. Top SQL Server LinkedIn groups
168 Views