Managed Service Accounts (MSA)

In Windows 2008 R2 onwards a new object type was created in Active Directory called msDS-ManagedServiceAccount, which is more commonly referred to as a Managed Service Account (MSA).  MSAs allow for a type of Active Directory (AD) account that is automatically managed.  MSAs are assigned to a single computer, so when accessing resources over the network, the computer account context will be used.

The main benefit of Managed Service Accounts (MSAs) is that password management is delegated to AD, which by default will cycle these complex 120 character passwords every 30 days.

Microsoft SQL Server 2012 supports MSAs, however this is limited by the restriction of being assigned to a single computer.

This script will create an account assigned to single computer. This is then installed and tested on the target computer.

PowerShell script:
New-ADServiceAccount -Name SQL_vWH11_MSA -RestrictToSingleComputer -Enabled $true
Add-ADComputerServiceAccount -Computer vWH11 -ServiceAccount SQL_vWH11_MSA

Add-WindowsFeature RSAT-AD-PowerShell
Import-Module ActiveDirectory
Install-ADServiceAccount -Identity SQL_vWH11_MSA
Test-ADServiceAccount -Identity SQL_vWH11_MSA

For more information about creating MSAs, please refer to https://technet.microsoft.com/en-us/library/dd391964.aspx

Group Managed Service Accounts (gMSA)

Once a Windows Server 2012 Domain Controller is added into the environment, Group Managed Service Accounts (gMSAs) can be configured.  While these accounts are like MSAs, the key difference is that they can be assigned to multiple computer accounts.

Microsoft SQL Server 2014 is the first version to supports gMSAs.

Implementation with Microsoft SQL Server

The following sections describe the process for configuring gMSAs.

Key Distribution Services (KDS)

A Key Distribution Services (KDS) root key is needed to support password generation for gMSAs. If a key already exists this can be used if it is valid.

A check for an existing key(s) is shown below.

PowerShell script:
$(Get-KdsRootKey) | Select KeyId, EffectiveTime

Alternatively, this can be configured graphically.

Open Active Directory Sites and Services, View and Show Services Node.

Assuming the user has the correct permissions, the key(s) will then be visible in Services, Group Key Distribution Service, Master Root Keys.

If the KDS root key is not present, it can be created using a PowerShell script.  It’s important to note that when the key is first created, the default effective date is set in the future to allow for all domain controllers to replicate.  This can be overridden or set to an earlier effective time.

For more information about creating the KDS Root Key, please refer to https://technet.microsoft.com/en-us/library/jj128430.aspx

The KDS can be added with this next script.

PowerShell script:
Add-KdsRootKey

Creating the account

The account created is associated with a security group that holds all the computer objects that will need to use the account.

A security group is created and the computers that will use the account are added.

PowerShell script:
New-ADGroup -Name SQLServers -SamAccountName SQLServers -GroupCategory Security -GroupScope Global -DisplayName "SQLServers" -Path "CN=Computers,DC=Contoso,DC=Coeo,DC=Com" -Description "Members of this group use gMSA"

Add-ADGroupMember -Identity SQLServers -Members $(Get-ADComputer -Filter 'Name -like "vWH*"') #Add all my servers to group.

The accounts are then created specifying the above security group.

PowerShell script:
New-ADServiceAccount -name SQLSRV01 -DNSHostName SQLSRV01.contoso.coeo.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers #CONTOSO\SQLSRV01$

New-ADServiceAccount -name SQLAGT01 -DNSHostName SQLAGT01.contoso.coeo.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers #CONTOSO\SQLAGT01$

New-ADServiceAccount -name SQLSRS01 -DNSHostName SQLSRS01.contoso.coeo.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers #CONTOSO\SQLSRS01$

New-ADServiceAccount -name SQLSAS01 -DNSHostName SQLSAS01.contoso.coeo.com -PrincipalsAllowedToRetrieveManagedPassword SQLServers #CONTOSO\SQLSAS01$

Removing the account

The accounts can also be uninstalled and removed if they are no longer needed.  For more information, please refer to https://technet.microsoft.com/en-us/library/ee617202.aspx

PowerShell script:
Uninstall-ADServiceAccount -Identity SQLSRV01

Altering the account

There are several options that can be changed on the account, for example the password change interval.  For more information, please refer to https://technet.microsoft.com/en-gb/library/ee617252.aspx

PowerShell script
Set-ADServiceAccount SQLSRV01 -ManagedPasswordIntervalInDays 60

Using the account

These accounts can then be used on all computers in the security group, without the need for specifying passwords.

Configuring Kerberos authentication

Service Principal Name (SPN) permissions can be configured against the active directory security group to allow for automatic registration.

Shell script:
dsacls "CN=SQLSRV01,CN=Managed Service Accounts,DC=CONTOSO,DC=COEO,DC=COM" /G "\SELF:RPWP;servicePrincipalName"

Alternatively, the SPNs can be configured manually using setspn.

Shell script:
setspn -s "HTTP/vWH11.CONTOSO.COEO.COM" "CONTOSO\SQLSRS01$"
setspn -Q MSSQLSvc/vWH11.contoso.coeo.com
setspn -L Contoso\SQLSRS01$

Trust Delegation

Trust delegation can be configured using the following syntax.

PowerShell script:
Set-ADServiceAccount -Identity SQLSRS01 -TrustedForDelegation $true

It may be necessary to purge existing Kerberos tickets, to do so, the following command can be executed on a command prompt.
klist purge

Article updated 2nd July 2017: SQL 2014 is the earliest version to support gMSAs.

https://msdn.microsoft.com/en-us/library/ms143504(v=sql.120).aspx

Don't miss out - sign up to recieve the latest technical articles, exclusive event invitations and more: