Managed Service Accounts (MSA) and SQL 2012: Practical Tips


One of the most common dilemmas for SQL Server administrators is whether they should use AD based domain users as the service accounts, or can they leverage the inbuilt accounts like Network Service etc. If your SQL Server instance is never going to participate in any cross-server contexts (such as availability Groups, Linked Servers, Log Shipping etc.) then you may very well be happy to use one of the inbuilt accounts or even better, in SQL 2012, the special ‘Virtual Account’ feature.

While these options relieve you from the overhead of periodically changing and syncing the service account password, they do impose the machine boundary and cause problems when you want to ‘jump’ across instances. So the classic solution has been to grit one’s teeth and ask for a domain user which will then be configured as the service account. However this brings the hassle of periodic password maintenance, and more importantly that causes downtime.

OR you can configure the SQL 2012 standalone instance to utilize the new Managed Service Accounts feature in Windows 2008 R2 and above. To do this, you follow the steps below.

Setup the MSA in Active Directory

First, create a new MSA in the AD using the PowerShell cmdlet. To do this, there are some simple considerations documented here, but the most important ones are to be an domain administrator, to be on Windows 2008 R2 or above and have the right PowerShell modules installed (Remote Server Administration Tools has a AD PowerShell module which you must install.)

Import-Module ActiveDirectory

New-ADServiceAccount –Name TestSQLMSA -Enabled $true

Next, associate the above MSA with the computer you wish to use it on. Note that a MSA in itself can only be used on one destination computer at a time. In this case, I will specify my lab computer named W2K8R2CN3.

Add-ADComputerServiceAccount -Identity W2K8R2CN3 -ServiceAccount TestSQLMSA

Install the MSA on the target server

Once this is done, you switch to the W2K8R2CN3 computer (which just happens to be a Windows 2008 R2 Core installation) and ‘install’ the MSA on the computer. Now, if you are on Windows 2008 R2 Core installation, to use the ActiveDirectory cmdlets you must effectively install the RSAT-AD-PowerShell feature, but directly using DISM:

DISM /online /enable-feature /featurename=ActiveDirectory-PowerShell

Once that is done, you can easily associate the MSA on the target computer:

Install-ADServiceAccount TestSQLMSA

Important: To do this correctly, you must be a domain administrator. If you run the Install-ADServiceAccount cmdlet and you are not a domain admin, it silently exits, but later when you try to change the service account and start SQL, you will receive an error:

“Error 1069: The service did not start due to a logon failure” and HRESULT 0x8007042d.

If you get those errors, have a domain admin logon to the target server and re-run Install-ADServiceAccount for you.

Change the SQL Service account

Last but not the least: you use SQL Configuration Manager to execute the service account change. An important note is that when specifying the ‘user name’ for the MSA, you must fully qualify it with domain name, and have a trailing $ sign as well. If you do not specify the $ you will receive the error message:

“Invalid parameter [0x80041008]”

As an example, in my test setup, here is how Configuration Manager looks:

image

Supportability questions

MSA and older SQL versions: Though you may be able to configure and / or use MSA with SQL 2008 R2 or older versions, it is officially not tested. If you notice, the documentation for SQL 2008 R2 service accounts is noticeably silent on this topic.

Group Managed Service Accounts (GMSA): As of Feb 2014, Group Managed Service Accounts are NOT officially supported with SQL 2012 Failover Clustered Instances. This is under review by the Product Group for future releases.

References

Please leave your questions, comments below! I’d be very happy to hear from you. Till next time, bye!

Comments (14)

  1. arvindsh says:

    A quick follow up on this. In Windows 2012, any attempt to install the MSA on the target computer by a non-domain admin fails with the error below. Much better than the silent failure in Windows 2008 R2:

    Install-ADServiceAccount : Cannot install service account. Error Message: '{Access Denied} A process has requested access to an object, but has not been granted those access rights.'.

  2. Chirag Roy says:

    Hi Arvind

    My company is investigating use of gMSA with SQL 2012 FCI and was wondering that since the connect item referenced is almost over a year old, is there any other Microsoft documentation which suggests that gMSA is NOT officially supported by Microsoft for SQL 2012 Failover clustered instances?

    An official citation from Microsoft will help to take back to management.

    Thanks

    Chirag

  3. arvindsh says:

    hi Chirag

    As we discussed offline, the status has not changed. The support policy for gMSA is still under evaluation (as of 17 Feb 2014). Till then, it remains officially unsupported.

  4. Jens Nielsen says:

    Just to clarify, can I use MSA if my SQL Server instance is configured for AlwaysOn Availability groups? The SQL Instances themselves are not clustered, but of course it runs on top of a Windows 2012 cluster.

  5. Managed Network Services says:

    Wonderful website. A lot of useful info here. I am sending it to a few friends ans also sharing in delicious. And obviously, thanks for your sweat!

  6. Managed Services Dallas says:

    Hey just wanted to give you a quick heads up and let you know a few of the images aren’t loading correctly. I’m not sure why but I think its a linking issue. I’ve tried it in two different browsers and both show the same outcome.

  7. Managed Services Dallas says:

    This is one technology that I would love to be able to use for myself. It’s  definitely a cut above the rest and I can’t wait until my provider has it. Your insight was what I needed. Thanks

    http://www.lgnetworksinc.com/managed-services

  8. Kevin George says:

    Hi Arvind,

    Excellent post, I was working with MSA today and came across a strange bug, the [Install-ADServiceAccount] will fail with names greater than15 Character limit. It maybe be worth mentioning it in your post.

    Thanks 🙂

  9. We also have been unable to use an MSA with a "$" embedded in the account name.  

  10. Wesley says:

    Any one has ever tried to change SQL Reporting Services Account to Managed Service Account?

  11. gravityusa says:

    I love this article. This is very well written. You have truly enriched me with some excellent knowledge.

  12. Dave says:

    What version/s of SQL do officially support Group Managed Service Accounts?

    1. John says:

      SQL 2016 is the latest and it also does not support them…. so the answer to your question is currently none.

    2. SQL Server 2016 does support gMSA for failover cluster instances. We will be updating our main documentation page on service accounts to reflect this, but for now you can refer to https://msdn.microsoft.com/en-us/library/bb510411.aspx (look under the section ‘High Availability Enhancements’ and under that the notes for CTP 2.0.