Configuring SQL Server to use Managed Service Accounts (in PowerShell)

When building out a new SQL Server environment, one consideration is the service account used to run SQL services. In SQL 2014 there are five options to choose from:

As long as there’s no need to access network resources then Virtual Accounts are best for new builds. They’re simpler than Built-In/Local accounts and provide the highest levels of separation. But if you do – for instance when using log shipping or database mirroring – then it’s better to use a low-privilege Domain Account or a Managed Service Account instead.

Domain Accounts have been around for a long time but using them correctly can be tricky:

  • How do you securely store the account passwords?
  • What about changing the password? How often? It is too easy to click the “Password Never Expires” box when creating the account
  • What is the process for reconfiguring services when the password is changed? Service Accounts are often shared across several systems. Forgetting to change the password somewhere means that service won’t come online
  • Did you remember to grant the account privileges to manage the ServicePrincipalName for Kerberos authentication?

But there is a better way!

Microsoft introduced Managed Service Accounts in Windows 2008 R2. SQL Server has supported them since 2008 R2 2012 (thanks Stephen!) and they make password management a breeze.

Managed Service Accounts have password management built in so administrators never have to change passwords themselves. AD will change the password automatically every 30 days and there’s no need to update the service configuration. MSAs also have the correct privileges in AD to manage ServicePrincipleNames. A MSA can only be linked to one computer at a time – they are so easy to use you wouldn’t want to share them anyway.

There are a couple of gotchas worth highlighting:

  • They require an AD Schema of 2008 R2 or later
  • To use all features the domain forest functional level must be 2008 R2 or later
  • MSAs cannot be used for SQL Server failover clusters (as all cluster nodes need to use the same service account)
  • If running Windows 2008 R2 or Windows 7 there is an important hotfix that fixes a problem with password changes not propagating – KB2494158

Using MSAs with SQL Server

The following PowerShell script creates a MSA in Active Directory for a SQL Server instance, then reconfigures the SQL Database Engine service account to use it.

Prerequisites/Notes:

  • The PowerShell script must be run as a Domain Administrator
  • It requires the PowerShell AD tools to be installed on your SQL server (although the script will install them for you if not already present)
  • Execute the script on the server running the SQL Server instance you want to configure

Cmdlet reference:

Granting Privileges to a MSA

Granting privileges to a MSA is very similar to granting privileges to a computer account. The account name of a MSA is in the format {Domain}\{MSAAccountName}$.

For instance, to grant a MSA access to a mirroring endpoint:

For shares/NTFS permissions be sure to select the “Service Account” object type before entering the account name as it is not enabled by default.

objectype

Notes

This post covers Managed Service Accounts – a new feature in Windows 2012 are Group Managed Service Accounts (gMSA). GMSAs can be shared across multiple servers so are perfect for clusters… except… they aren’t supported in SQL Server. Not that Microsoft make this particularly clear (you really have to hunt around to find an official statement).

This entry was posted in Administration, Automation, PowerShell, Scripting. Bookmark the permalink.

One Response to Configuring SQL Server to use Managed Service Accounts (in PowerShell)

  1. bala says:

    i am running sql agent with msa , however my sql jobs fail with an error saying unknown usernameor password , can you advise please

Leave a Reply

Your email address will not be published. Required fields are marked *