By: John Martin | 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
If there is not a valid KDS Root Key, you can use the following code to create one:
Add-KdsRootKey -EffectiveImmediately
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
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
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:
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:
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.
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.
- SQL Server Installation Best Practices
- How to Create Secure SQL Server Service Accounts
- Running SQL Server Agent with a least privilege service account
- Register a SPN for SQL Server Authentication with Kerberos
- Why a System Account is a bad idea for SQL Server Service Account
- SQL Server Service Account Privileges
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
