I don't want to know the password – Using group Managed Service Accounts for Microsoft SQL Server

A few days ago, I changed the service account of the SQL Server instances in my lab environment for Always On Availability Groups and now use a group Managed Service Account (gMSA). In the following, I present the necessary adjustments.

Which service account do we recommend?

As a service account for Microsoft SQL Server, we now generally recommend the use of local service accounts. In this case, no passwords need to be managed and access to resources in the network is provided via the computer account. This can then, for example, be authorized to file shares in order to be able to write a backup there.

However, when setting up high availability solutions such as Always On Availability Groups, a shared account between all SQL Server instances is recommended, as access between the instances can be set up very easily here. Many of our customers still use a classic domain user account with password. The solution we recommend, however, is the use of group Managed Service Accounts, because the operating system is able to take care of password management.

How to create a group Managed Service Account?

A necessary prerequisite is the creation of a Microsoft Key Distribution Service (KDS) root key. The key is needed by domain controllers to generate gMSA passwords. To make the key usable in my lab right away, I skip the 10-hour waiting period. This is to ensure that all domain controllers have received the key before the first use. I only use one key in my lab and therefore use the following code:

if (-not (Get-KdsRootKey)) {
    $null = Add-KdsRootKey -EffectiveTime ([datetime]::Now).AddHours(-10)
}
 

When creating the service account, it is already specified which computers are allowed to access the password via their computer accounts. The computers on which the SQL Server instances are later installed must be specified here. In my lab, I use a dynamic query on the Active Directory to determine all computers whose names begin with “SQL”. The entry can be adjusted later if the account is to be used on other computers. The other necessary properties of the account are also determined dynamically by my code.

It is crucial for Kerberos to work correctly to provide the permission to manage the own Service Principal Name (SPN) to the service account, which I set up with the utility “dsacls”. You can find more information about this in the blog article “SQL Server and Kerberos: Please don't forget the Service Principal Names”.

To avoid granting permissions directly to service accounts, I also create a suitable group and add the service account as a member there. Finally, the target computers must be restarted so that they can use the new service account directly.

Here is the complete code that can be run on any computer that has the ActiveDirectory PowerShell module installed:

$serviceAccountName         = 'gMSA-SQLServer'
$serviceAccountDescription  = 'Group-managed service account for SQL Server'

$computerName               = (Get-ADComputer -Filter 'Name -like "SQL*"').Name
$computerAccountName        = $computerName | ForEach-Object { $_ + '$' }
$serviceAccountDNSHostName  = "$serviceAccountName.$((Get-ADDomain).DNSRoot)"
$serviceAccountBenutzername = "$((Get-ADDomain).NetBIOSName.ToUpper())\$serviceAccountName" + '$'

$adServiceAccountParams = @{
	Path                                       = $sqlUserOU.DistinguishedName
	Name                                       = $serviceAccountName
	Description                                = $serviceAccountDescription
	DNSHostName                                = $serviceAccountDNSHostName
	PrincipalsAllowedToRetrieveManagedPassword = $computerAccountName
	Enabled                                    = $true
}

$serviceAcccount = New-ADServiceAccount @adServiceAccountParams -PassThru
$null = dsacls $serviceAcccount.DistinguishedName /G "SELF:RPWP;servicePrincipalName"

New-ADGroup -Name SQLServiceAccounts -GroupCategory Security -GroupScope Global -Path $sqlUserOU.DistinguishedName
Add-ADGroupMember -Identity SQLServiceAccounts -Members (Get-ADServiceAccount -Identity $serviceAccountName)

Restart-Computer -ComputerName $computerName -Force
 

How to grant access to file shares?

Since I also want to backup and restore my databases in the lab to the secondary replicas, I need a file share that can be used by the SQL Server instances with the group Managed Service Account.

Previously, I used a domain user account as the service account and a non-hardened Windows server as the file server. With this, all directories were automatically usable by all domain users to store files, I only had to authorize the service account on the share.

Now with the group Managed Service Account, the NTFS permissions also need to be set. I use the following code for this (slightly modified here for better presentation):

$Path = 'C:\FileServer\Backup'
$AccountName = 'ORDIX\SQLServiceAccounts'
$AccessRight = 'Modify'
$accessRule = [System.Security.AccessControl.FileSystemAccessRule]::new(
	$AccountName,
	$AccessRight,
	[System.Security.AccessControl.InheritanceFlags]::ContainerInherit + [System.Security.AccessControl.InheritanceFlags]::ObjectInherit,
	[System.Security.AccessControl.PropagationFlags]::None,
	'Allow'
)
$acl = Get-Acl -Path $Path
$acl.SetAccessRule($accessRule)
Set-Acl -Path $Path -AclObject $acl
 

If you need to set NTFS permissions frequently, I recommend using the PowerShell module “NTFSSecurity” by Microsoft employee Raimund Andrée.

The setup and authorization of the file share itself is done as before with the following code (again, slightly modified for better presentation):

$Path = 'C:\FileServer\Backup'
$ShareName = 'Backup'
$AccountName = 'ORDIX\SQLServiceAccounts'
$AccessRight = 'Change'
$null = New-SmbShare -Path $Path -Name $ShareName
$null = Grant-SmbShareAccess -Name $ShareName -AccountName $AccountName -AccessRight $AccessRight -Force
 

What changes with the installation of the SQL Server instance?

Here, the changes are very minor. As before, a credential object is created for the service account and given to the Install-DbaInstance command. The only thing to note is the $ character at the end of the username and the empty password. The code looks like this:

$DomainName = 'ORDIX'
$SQLServerServiceAccount = 'gMSA-SQLServer'
$sqlServerCredential = [PSCredential]::new("$DomainName\$SQLServerServiceAccount$", [SecureString]::new())
 

What changes when setting up the Availability Groups?

Nothing at all. Yes, in fact, you are welcome to view the changes on GitHub here. For the operation of the SQL Server instance and also for the setup of the Availability Groups, the type of account used makes no difference, as long as the same account is used for all instances involved.

Conclusion

Setting up the first group Managed Service Account requires some new code and, for file servers, perhaps some additional configuration of NTFS permissions. But from then on it is quite simple, and you won't notice any difference in day-to-day operations. The only noticeable difference is that now there is no chance of accidentally expired or compromised passwords.

If you have any further questions or need assistance with the changeover to group Managed Service Accounts, please contact us. We are happy to help.