Managed Service Accounts with Microsoft SQL Server 2012

Managed Service Accounts (MSA) which came with Microsoft Windows Server 2008 R2 and Windows 7 allow you to create domain account which is tied to a specific computer. The account itself is a hybrid of User and Computer account and is not affected by domain password policy. Why? Because it’s managed! By who? Active Directory Controller and the trusted client! What will be managed? The passwords! By default password is refreshed every 30 days and always is complex enough. Isn’t it wonderful?

Nowadays it’s common practice that service accounts have not so tight password policy as accounts used for interactive logon. Their passwords never expire because you would not do anything else than changing your service accounts regardless of necessary downtime during service restart.

What will we need to run Managed Service Accounts?

• PowerShell, Active Directory module for Windows PowerShell, .Net 3.5 or newer at client computers
• Use Windows Server 2008 R2 Active Directory schema
• Windows Server 2008 R2, Windows 7 or newer
• SERVICE WHICH SUPPORT MANAGED SERVICE ACCOUNT – fe. Currently Microsoft SQL Server 2012

Let’s get started:

At Active Directory Controller install Active Directory module for Windows PowerShell – you may already suspect that there is no GUI for this action available yet. Relax .. there is not 🙂

Launch PowerShell with elevated permissions. If you launch Active Directory module for Windows PowerShell you might get following error during execution of commands:

New-ADServiceAccount : A parameter cannot be found that matches parameter name ‘MSA_TEST’.
At line:1 char:31
+ New-ADServiceAccount -MSA_TEST <<<< -Path “cn=Managed Service Accounts, dc=tomcat,dc=local” -enabled $true
+ CategoryInfo : InvalidArgument: (:) [New-ADServiceAccount], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.ActiveDirectory.Management.Commands.NewADServiceAccount

At PowerShell window

Import-Module ActiveDirectory

Add new AD Service Account

New-ADServiceAccount -Name SQL2012STsvc -Path "cn=Managed Service Accounts, dc=tomcat,dc=local" -enabled $true

In my case SQL2012STsvc is name of my service account, cn defines where in AD tree the account will be created, 2x dc specifies domain and enable simply enables the account. If you would define Managed Service Account’s name longer that 15 characters you would receive this Access Denied error:

New-ADServiceAccount : Access is denied
At line:1 char:21
+ New-ADServiceAccount <<<< -Name MSA_TEST_TEST_TEST -Path “cn=Managed Service Accounts, dc=tomcat,dc=local” -enabled $true
+ CategoryInfo : PermissionDenied: (CN=MSA_TEST_TES…tomcat,dc=local:String) [New-ADServiceAccount], UnauthorizedAccessException
+ FullyQualifiedErrorId : Access is denied,Microsoft.ActiveDirectory.Management.Commands.NewADServiceAccount

Create Computer Service account and tie it with AD Service account

Add-AdComputerServiceAccount -Identity SQL2012ST -ServiceAccount SQL2012STsvc

At remote computer where (in my case Microsoft SQL Server) will run open PowerShell and import AD Module with

Import-Module ActiveDirectory

And install the account

Install-ADServiceAccount -Identity SQL2012STsvc

.. and that’s it!

During Microsoft SQL Server 2012 installation enter your created accounts – by best practice one account for each service – and keep password blank.

After installation you can use one of the new DMV introduced in Microsoft SQL Server 2012

select * from sys.dm_server_services

.. and yes it works!