PowerShell and Group Managed Service Accounts (gMSA)

This is one article in a series that cover how to use PowerShell scripts to automatically install SQL Server. The full script set can be found here.

Service Accounts are a requirement for installing and running a SQL Server. For many years Microsoft has recommended that each SQL Server service be run as a separate low-rights Windows account. Where possible, the current recommendation is to use Managed Service Accounts (MSA) or Group Managed Service Accounts (gMSA). Both account types are ones where the account password is managed by the Domain Controller. The primary difference being that MSA are used for standalone SQL instances, whereas clustered SQL instances require gMSA. In this post, we’re going to use PowerShell to create Group Managed Service Accounts, and then deploy them for use on multiple SQL servers that will be hosting an Availability Group.

To create the service account(s) in Active Directory using PowerShell, the PowerShell Remote Server Administration Tools for Active Directory (Windows 10 or Server 2016) needs to be installed on the computer running the PowerShell script. Once the RSAT tools have been installed, the Active Directory management module can be imported into the current session using the Import-Module ActiveDirectory command.

# Import the AD tools into the current session
Import-Module ActiveDirectory; 

The domain name will also be needed to create the service accounts. This can be found using the Get-ADDomain commandlet.

# Get Domain Name
$DomainName = (Get-ADDomain).DNSRoot;

In order to create the service accounts in the domain, an account with Domain Admin permissions is needed. The current user account probably doesn’t have these permissions, so we’ll prompt for RunAs (Admin) account credentials.

# Get admin credential to run scripts
Write-Output “Enter RunAs (Admin) credentials.”
$pscred = Get-Credential;

In a previous post, we covered how to use a JSON file to provide parameter values to a PowerShell script. Next, we’ll import a JSON file that contains a list of our servers & service account information, into a PowerShell variable ($Config).

[string]$ConfigJSON = “C:\Install\Config.json”;
# Load JSON Config File
$Config = ConvertFrom-Json -InputObject (Get-Content -Path “$ConfigJSON” -Raw);

Now we can store the server names & account information into their own variables.

# Get the server names 
[array]$ServerList = (($Config).Servers).ServerName
# Get gMSA names
$gMSANames = ((($Config).Accounts).Services).Where{$_.AccountType -eq “gMSA”};

Next, we can use a ForEach loop to go through the list of service accounts and use the New-ADServiceAccount commandlet to: create the accounts ($Acct), associate them with the list of servers that are allowed to use them ($gMSA_HostNames), and (optionally) enable the accounts for delegation.

ForEach ($Name in $gMSANames) {
      $Acct = $Name.Username;
      $AcctDNS = “$Acct.$DomainName”
      $gMSA_HostNames = $ServerList | ForEach-Object { Get-ADComputer -Identity $_ };
      # Create new gMSA
      New-ADServiceAccount -Name $Acct -DNSHostName $AcctDNS -PrincipalsAllowedToRetrieveManagedPassword $gMSA_HostNames;
      Write-Output “gMSA Created: $Acct”;
      Set-ADServiceAccount -Identity $Acct -TrustedForDelegation $true;
        }

Enabling delegation does create a potential security issue. StealthBits has a good article about this at: https://blog.stealthbits.com/unconstrained-delegation-permissions/. For our situation, we are enabling Delegation for use later in configuring the SQL servers and will disable it when we’re done.

Once the service accounts have been created in AD, we need to install them locally on each of the servers where they will be used. To install the service accounts onto each server, the Install-ADServiceAccount commandlet needs to be run locally on each server. To accomplish this, we’ll use a script block that will add/import the RSAT tools and Active Directory module, and then install the account.

Add-WindowsFeature RSAT-AD-Powershell;
Import-Module ActiveDirectory; 
Write-Output “Add gMSA: $gMSA”;
Install-ADServiceAccount $gMSA;

To install multiple service accounts on multiple SQL servers, we’ll use two nested ForEach loops. The outer loop will cycle through the list of servers and the inner loop will install each service account on that server. The script block (above) will be sent to each server using an Invoke-Command statement, along with the Admin credentials ($pscred) that were previously prompted for.

ForEach ($Server in $ServerList) {
Write-Output “Executing on server: $Server”;
ForEach ($Name in $gMSANames) {
$Acct = ($Name).Username;
$MSAblock = @”
Add-WindowsFeature RSAT-AD-Powershell;
Import-Module ActiveDirectory; 
Write-Output “Add gMSA: $Acct”;
Install-ADServiceAccount $Acct;
“@;
write-output “Add gMSA: $Acct”;
# Install gMSA on the SQL Server
Invoke-Command -ComputerName $Server -ScriptBlock {$MSAblock} -Credential $pscred;
}
}

Once the service accounts have been created and installed, we can confirm that they are ready by running: Test-ADServiceAccount <account name> on each server.

Here’s the full script:

Import-Module ActiveDirectory; 
# Get Domain Name
$DomainName = (Get-ADDomain).DNSRoot;

# Get admin credential to run scripts
Write-Output “Enter RunAs (Admin) credentials.”
$pscred = Get-Credential;

[string]$ConfigJSON = “C:\Install\Config.json”;
# Load JSON Config File
$Config = ConvertFrom-Json -InputObject (Get-Content -Path “$ConfigJSON” -Raw);

# Get the server names 
[array]$ServerList = (($Config).Servers).ServerName
# Get gMSA names
$gMSANames = ((($Config).Accounts).Services).Where{$_.AccountType -eq “gMSA”};

# Create the AD service accounts
ForEach ($Name in $gMSANames) {
      $Acct = $Name.Username;
      $AcctDNS = “$Acct.$DomainName”
      $gMSA_HostNames = $ServerList | ForEach-Object { Get-ADComputer -Identity $_ };
      # Create new gMSA
      New-ADServiceAccount -Name $Acct -DNSHostName $AcctDNS -PrincipalsAllowedToRetrieveManagedPassword $gMSA_HostNames;
      Write-Output “gMSA Created: $Acct”;
      Set-ADServiceAccount -Identity $Acct -TrustedForDelegation $true;
        }

ForEach ($Server in $ServerList) {
Write-Output “Executing on server: $Server”;
ForEach ($Name in $gMSANames) {
$Acct = ($Name).Username;
$MSAblock = @”
Add-WindowsFeature RSAT-AD-Powershell;
Import-Module ActiveDirectory; 
Write-Output “Add gMSA: $Acct”;
Install-ADServiceAccount $Acct;
“@;
    write-output “Add gMSA: $Acct”;
    # Install gMSA on the SQL Server
    Invoke-Command -ComputerName $Server -ScriptBlock {$MSAblock} -Credential $pscred;
    }
}