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.


  • 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:

    SQL Server Managed Service Account Configuration Script
    Author: Robin Watkins 
# AD Domain Name
$DomainName = "2012DOM"

# Name of Managed Service Account to create
$MSAccountName = "AZURESQL2MSA"

#Hostname of server running the SQL Instance
$SQLServerName = "AZURESQL2"

#SQL Instance Name to reconfigure to use the MSA
$SQLInstanceName = "MSSQLSERVER"

#If not installed add the PowerShell AD features
if ((Get-WindowsFeature RSAT-AD-PowerShell).InstallState -ne 'Installed') {
	Add-WindowsFeature RSAT-AD-PowerShell

#Create the Managed Service Account for the SQL Service
New-ADServiceAccount -Name $MSAccountName -Enabled $true -RestrictToSingleComputer

#Add the newly-created MSA to the server running SQL Server
Add-ADComputerServiceAccount -Identity $SQLServerName -ServiceAccount $MSAccountName

#Install the MSA on this local server - configures the local server to manage the password itself
Install-ADServiceAccount $MSAccountName

#Reconfigure SQL Server to use the new MSA
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') "localhost"           

$ChangeService=$SMOWmiserver.Services | where {$ -eq $SQLInstanceName}


$ChangeService.SetServiceAccount($UName, $PWord)

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:

GRANT CONNECT ON ENDPOINT::MirrorEndpoint to [2012DOM\MSA123$]

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.



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)

Leave a Reply

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