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.
New-ADServiceAccount -Name SQL_vWH11_MSA -RestrictToSingleComputer -Enabled $true
Add-ADComputerServiceAccount -Computer vWH11 -ServiceAccount SQL_vWH11_MSA
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.
$(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.
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.
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.
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
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
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.
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.
setspn -s "HTTP/vWH11.CONTOSO.COEO.COM" "CONTOSO\SQLSRS01$"
setspn -Q MSSQLSvc/vWH11.contoso.coeo.com
setspn -L Contoso\SQLSRS01$
Trust delegation can be configured using the following syntax.
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.
Article updated 2nd July 2017: SQL 2014 is the earliest version to support gMSAs.
Don't miss out - sign up to recieve the latest technical articles, exclusive event invitations and more: