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!

Advertisements

About Jiří Hubáček

Experienced consultant and blogger for Microsoft SQL Server and related products. Likes cats, workouts and delicious food. Enjoys sailing, traveling, time spent with wife and friends.
This entry was posted in Managed Service Accounts, Microsoft SQL Server and tagged , , , , , , , , , . Bookmark the permalink.

41 Responses to Managed Service Accounts with Microsoft SQL Server 2012

  1. bhavikmerchant says:

    Do these MSAs work correctly in a Kerberos environment with a Sharepoint farm i.e. multiple application servers, and SQL BI services spread over different machines?

  2. Hello Bhavik,
    SPN for MSAs are automatically maintained. They are linked to cumputer and yes they support delegation. Currently only Microsoft SQL Server 2012 supports MSAs. MSAs cannot be used for cluster installation because of its dependency to computer account.

  3. Very nice article. Is it possible to assign NTFS permissions to the MSA?

  4. Ian Trainor says:

    Thanks for the walk thru, but Im having issue with getting error message “The specified credentials for the “SQL Server service are not valid, please provide valid xxxxxxxxxxx”

    Ive followed everything, made sure that the service name has $ at the end of it. Should the MSA be viewable when you browse thru AD?

    • Hello Ian,
      Where did the error pop up? During instance installation or in SQL Server Config Manager?
      Were you successful with creating each account and pairing it with Computer Account? By using my scripts your MSA should be displayed at Managed Service Account leaf at Active Directory Users and Computers tool. Or you can use this PowerShell script to list your MSAccounts (with ActiveDirectory module already loaded – see article):

      Get-ADServiceAccount -Filter ‘Name -like “*”‘

      Result for environment used in article:

      DistinguishedName : CN=SQL2012STsvc,CN=Managed Service Accounts,DC=tomcat,DC=local
      Enabled : True
      HostComputers : {CN=SQL2012ST,CN=Computers,DC=tomcat,DC=local}
      Name : SQL2012STsvc
      ObjectClass : msDS-ManagedServiceAccount
      ObjectGUID : 571354e3-0142-4511-87b0-cd5045666e4a
      SamAccountName : SQL2012STsvc$
      SID : S-1-5-21-715837455-762803272-3734026643-1133
      UserPrincipalName :

      DistinguishedName : CN=SQL2012STAGsvc,CN=Managed Service Accounts,DC=tomcat,DC=local
      Enabled : True
      HostComputers : {CN=SQL2012ST,CN=Computers,DC=tomcat,DC=local}
      Name : SQL2012STAGsvc
      ObjectClass : msDS-ManagedServiceAccount
      ObjectGUID : 514749e4-6629-452e-bdca-0148bae23260
      SamAccountName : SQL2012STAGsvc$
      SID : S-1-5-21-715837455-762803272-3734026643-1134
      UserPrincipalName :

      DistinguishedName : CN=SQL2012STASsvc,CN=Managed Service Accounts,DC=tomcat,DC=local
      Enabled : True
      HostComputers : {CN=SQL2012ST,CN=Computers,DC=tomcat,DC=local}
      Name : SQL2012STASsvc
      ObjectClass : msDS-ManagedServiceAccount
      ObjectGUID : a0c5b57c-be1c-482b-abc2-5feab4904284
      SamAccountName : SQL2012STASsvc$
      SID : S-1-5-21-715837455-762803272-3734026643-1135
      UserPrincipalName :

      DistinguishedName : CN=SQL2012STRSsvc,CN=Managed Service Accounts,DC=tomcat,DC=local
      Enabled : True
      HostComputers : {CN=SQL2012ST,CN=Computers,DC=tomcat,DC=local}
      Name : SQL2012STRSsvc
      ObjectClass : msDS-ManagedServiceAccount
      ObjectGUID : 50262aeb-2811-4524-a110-cd84a2a7150b
      SamAccountName : SQL2012STRSsvc$
      SID : S-1-5-21-715837455-762803272-3734026643-1136
      UserPrincipalName :

      DistinguishedName : CN=SQL2012STISsvc,CN=Managed Service Accounts,DC=tomcat,DC=local
      Enabled : True
      HostComputers : {CN=SQL2012ST,CN=Computers,DC=tomcat,DC=local}
      Name : SQL2012STISsvc
      ObjectClass : msDS-ManagedServiceAccount
      ObjectGUID : 32b146fc-1933-4867-b82c-12edbc5b111b
      SamAccountName : SQL2012STISsvc$
      SID : S-1-5-21-715837455-762803272-3734026643-1137
      UserPrincipalName :

      Thank you for question and have a nice day!

      • Ian Trainor says:

        The error is happening at the Server Configuration where I configure the service accounts.

        I ran a version of get-adserviceaccount

        PS C:\Users\Administrator.COLAISTEEANNA> Get-ADServiceAccount -Identity Crackle_
        SQL_SA

        DistinguishedName : CN=Crackle_SQL_SA,CN=Managed Service Accounts,DC=network,DC
        =xxxxx,DC=ie
        Enabled : True
        HostComputers : {CN=CRACKLE,OU=Servers,OU=AllSchool,DC=network,DC=xxxxx,DC=ie}
        Name : Crackle_SQL_SA
        ObjectClass : msDS-ManagedServiceAccount
        ObjectGUID : 67603df4-a499-4a4f-a319-c85a3a33f3f0
        SamAccountName : Crackle_SQL_SA$
        SID : S-1-5-21-9250183-1043154600-3504677263-7605
        UserPrincipalName :

        Crackle is the name of the to be SQL server. All of the MSA’s are listed in active directory users and computers.

        When I go to specifiy the logon account for the service account and I select <> I can’t find the accounts, I have to type them in manually. I am adding the $ at the end

      • Hello Ian,
        I’m sorry but I was not able to reproduce issue you described. Even when I only created new MSA by the first command the account is viewable when you browse Active Directory. Try to go thought the scripts once again or try GUI by cjwdev utility at my newest blog post.

    • Sam says:

      Thanks for the article Jiri. I got this error as well. For me it could have been a few things. The service should be created without a $, but then referenced with a $ in configuration manager. When searching for the account, you need to select ‘service accounts’ where you would normally see ‘users, groups, etc’. The account also needed to be installed on the computer. Once all these things were done, it worked.

  5. cjwdev says:

    For anyone that doesn’t want to use Powershell, I’ve very nearly finished my GUI tool for creating and editing Managed Service Accounts (which will be completely free). You can find more info and screenshots here: http://cjwdev.wordpress.com/2012/06/27/managed-service-accounts-gui/

  6. Pingback: Managed Service Account (MSA) GUI Tool by cjwdev – Windows Server 2008R2 schema | SQL Mate Blog

  7. Ken Powers says:

    Thanks for the article Jiri. Could these accounts be used with AlwaysOn Availability Groups? I’m going to implement Availability Groups and I’d like to see if this new security model will work with them.

    • Hello Ken, you are welcome. Yes you can use MSAs for Availability Groups. It’s the same as a standalone instance which in fact availability group replicas are except they are part of Windows Server Fail-over Cluster with just virtual ip address, virtual hostname and Availability Group resources.

      Either Managed Service Account or Group Managed Service Account (Windows Server 2012 Ad Schema) cannot be used for SQL Server fail-over instances (WSFC).

  8. Alex M says:

    Thanks, Jiri for a very clear and straightforward article!

    Is it possible MSA won’t work with the Developer SKU SQL2012 with SP1 slipstreamed in? I followed all your steps, and got ‘Specified User does not exist’ 0x84BB0001 in setup.rll as it attempted to start SQL using the MSA. It had accepted my service account, and I appended the $ after the name during setup. Attempting to change to the MSA with the Configuration Manager seem to fail as well.

    Thanks for any suggestions,
    Alex

  9. Ken says:

    Are there any good sources that explain how to setup an MSA for Kerberos Delegation? There is no “delegation” tab like there are on regular accounts and computer objects.

  10. Alex M says:

    My issue had to do with haphazard cloning of VMs and SID collision; got it all working, doesn’t seem to be SKU-specific. Curious if you would know the minimum Forest Functional Level to employ MSA and Virtual Service Accounts, assume it would be 2008 R2?

  11. Pingback: Managed Service Accounts - technostropic.com

  12. carlos quintana says:

    Hi, Question.. can i use this account in more than one sql server ?
    Thanks

  13. Pingback: Ryan's Happy Place

  14. Pingback: Managed Service Accounts | IdM for Real

  15. Pingback: Using Group Managed Service Accounts - Dragos MadarasanDragos Madarasan

  16. Arlie says:

    I really like it when people get together and share thoughts.

    Great website, stick with it!

  17. Great goods from you, man. I’ve understand your stuff previous to and you’re just
    extremely wonderful. I really like what you have acquired
    here, certainly like what you are saying and the way
    in which you say it. You make it enjoyable and you still take care of to keep it sensible.
    I can not wait to read far more from you. This is really a terrific site.

  18. Ila says:

    I was wondering if you ever thought of changing the page layout of your site?
    Its very well written; I love what youve got to say.
    But maybe you could a little more in the way of content so people could
    connect with it better. Youve got an awful lot of text for only having 1 or
    2 images. Maybe you could space it out better?

  19. Click Here says:

    Appreciate it for sharing your neat website.

  20. A person essentially help to make critically articles I
    would state. That is the first time I frequented your website page
    and up to now? I surprised with the research you made to create this particular
    publish extraordinary. Magnificent process!

  21. Glinda says:

    Greetings from Florida! I’m bored at work so I decided to browse your site on my iphone during lunch break. I enjoy the info you provide here and can’t
    wait to take a look when I get home. I’m surprised at how fast your blog loaded on my phone .. I’m not even
    using WIFI, just 3G .. Anyhow, fantastic blog!

  22. payday loan says:

    Hi there! This post couldn’t be written any better! Reading this post reminds me of my previous room mate! He always kept chatting about this. I will forward this post to him. Fairly certain he will have a good read. Many thanks for sharing!

    http://northdownriver.com/post/2012/03/10/Posting-of-stuff-in-march.aspx

  23. David Gurl says:

    FWIW, I had to install the “Active Directory module for Windows Powershell” feature on the TARGET COMPUTER before I could use the Install-ADServiceAccount command.

  24. Pingback: Add-KdsRootKey - Error “The Process cannot access the file because it is being used by another process…” - Shit my brain says and forgetsShit my brain says and forgets

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s