Using Managed Service Accounts with SQL Server

By: John Martin | Updated: 2018-02-13 | Comments (3) | Related: > Security

Problem

Managing SQL Server service accounts appropriately is coming sharply into focus with ever more stringent compliance requirements. Regularly rotating account passwords as well as ensuring the accounts are correctly configured to avoid misuse of these accounts can be complex and time consuming. Read on to discover how easy it can be to solve these headaches.

Solution

With Windows Server 2008 R2, Microsoft introduced a technology called Managed Service Accounts (MSA). These allow for Windows services to be run with as an Active Directory user account, but with the benefits of it being locked down and able to automatically negotiate password updates with minimal administration overhead.

Managed Service Accounts are not like normal Active Directory user accounts; they can only be created and managed via PowerShell. Additionally, they do not permit interactive login, are intrinsically linked to a specific computer account, and use a similar mechanism to Active Directory computer accounts for password management. This makes them inherently safer in all regards.

Managed Service Accounts Prerequisites

To be able to make use of Managed Service Accounts with SQL Server, there are certain prerequisites that need to be met:

Domain Functional Level of Windows Server 2008 R2 or higher
SQL Server 2012 or Higher
Active Directory PowerShell module for management

Additionally, if you are using Windows Server 2008 R2 or Windows 7 with Managed Service Accounts, it is important to ensure that KB 2494158 is installed.

If you are using Windows Server 2012 domain controllers, then you will need to have a KDS Root Key in place to be able to create Managed Service Accounts. Checking for and creating the KDS Root key are done via PowerShell with the following cmdlets:

Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyId

Testing for KDS Root Key - Description: Check to see if KDS Root Key exists.

If there is not a valid KDS Root Key, you can use the following code to create one:

Add-KdsRootKey -EffectiveImmediately

Add-KDSRootKey - Description: If no KDS Root Key exits, add one.

Be aware, however, that even with the EffectiveImmediately switch, it can still take up to ten hours for the key to become fully functional. It is best to check this well in advance of wanting to create Managed Service Accounts.

Creating Managed Service Accounts

Creation of MSAs will differ depending on the version of Windows Server you are using. Microsoft added more capabilities in Windows Server 2012, so the process is slightly different. In both cases the New-ADServiceAccount cmdlet is used.

Windows Server 2008R2

The following PowerShell command can be used with Windows Server 2008R2 to create the MSA:

New-ADServiceAccount -Name msa01 -Enabled $true -Description "Managed Service Account for SQL Server winSQL-0" -DisplayName "MSA 1 – winSQL-0"

Windows Server 2012 and Above

The following PowerShell command can be used with Windows Server 2012 and above to create the MSA, note that this is the same as that of 2008R2 with the addition of the RestrictToSingleComputer switch.

New-ADServiceAccount -Name msa01 -Enabled $true -Description "Managed Service Account for SQL Server winSQL-0" -DisplayName "MSA 1 – winSQL-0" -RestrictToSingleComputer

Installing and Using Managed Service Accounts

Once the MSA has been created, it needs to be installed on the server that it will be used on. To do this, the Active Directory PowerShell module will need to be installed on the SQL Servers. This can be completed by using the Install-WindowsFeature cmdlet and specifying the appropriate module:

Install-WindowsFeature RSAT-AD-PowerShell

Install AD PowerShell - Description: Installation of AD PowerShell RSAT Module.

Note: It is worth remembering that Install-WindowsFeature allows you to target remote machines, so that you can install components like this from anywhere.

Once the AD PowerShell cmdlets are in place, you will need to log in to the computer and then install the MSA, using the following cmdlet;

Install-ADServiceAccount -Identity msa01

Once this has been installed, you can verify that it is working as planned by using the built-in test cmdlet:

Test-ADServiceAccount -Identity msa01

Install MSA on Target Computer - Description: Installing the MSA onto the computer where it will be used.

Managed Service Accounts can only be active and linked to a single computer at any one time. Should you attempt to install the same account onto another computer, you will be asked for confirmation:

Trying to Install on another computer - Description: Warning generated when trying to install an MSA onto a computer where it is already being used elsewhere.

Once the MSA is set up and linked to the computer, it is time to use this as the service account for SQL Server. There are two options for updating the service account details. The first is to use the SQL Server Configuration Manager tool. In this instance you will need to make sure that the “Service Accounts” object type is selected for account validation:

Select Service Accounts object type - Description: Selecting the Service Accounts object type is needed in order to do name validation via the Service Configuration Management tool.

The other option, and the one that I prefer, is to make use of the Update-DbaSqlServiceAccount cmdlet from the DBATools PowerShell module. By making use of the DBATools module, you can easily script the entire process – from MSA creation all the way through to implementation.

Setting Service Account with PowerShell - Description: Using DBATools PowerShell module to set service account properties.

When using this cmdlet, it is important to remember to qualify the account with the domain name, as well as appending the account name with a $ (which denotes it as an MSA).

Summary

Here we have looked at how to set up SQL Server to use an MSA as its service account and be able to take advantage of the capabilities that come with it. This includes easy password rotation using the Reset-ADServiceAccountPassword, managing SPNs, and ensuring it is restricted to the server that it is linked to.

It is important to remember, however, there are some limitations, notably that SQL Server 2012 or higher is required to support the use of MSAs. Additionally, as there is a one-to-one relationship between computer and MSA when they are installed, this means that they are not supported for use with Failover Cluster Instances. If you are using SQL Server 2014 or above, then you can make use of group Managed Service Accounts (gMSA), which I will cover in my next tip.

Next Steps

When setting up SQL Server to make use of Managed Service Accounts you should check out these additional tips that cover a range of recommended practices.

About the author

John Martin is a Data Platform Engineer working in the financial sector as well as Director-at-Large for the PASS organisation and currently a Microsoft Data Platform MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-02-13

Friday, July 16, 2021 - 5:13:59 AM - jul	Back To Top (89013)
Hello, Thanks for the tutorial, it really helped me! I was stalled on the "Install-ADServiceAccount" with the error "The server has rejected the client credentials". For those who get this error: the command Get-"wmiobject -class win32_useraccount" helped me figuring out my computer (though freshly deployed) wasn't communicating with the AD correctly... (even though after mutiple reboots)