Group managed Service accounts are designed to work in applications like SQL Server, IIS Application Pools, scheduled tasks – any windows service that runs on multiple nodes. A Best Practice is to secure running services on a computer with windows user accounts. The account is then provided with the appropriate level of security to run the service. One downside to this is that the account can be locked out and the service will fail to start.A Group Managed Service Account (GMSA) will prevent this inadvertent lockout because passwords are maintained automatically by Windows. A GMSA is a cross between a computer and user account. The password is automatically renewed like a computer account. It can be used as a user account but you cant use it to logon to a computer interactively. Passwords automatically renewed, can’t lock out a service, can’t use them to logon to a server – what’s not to like?

Prerequisite

As stated, Group Managed Service Accounts (GMSA) work in applications distributed across multiple nodes on a Windows 2012 and Windows 2012 R2 platform. The application itself, such as SQL Server must be 2012 as well. GMSA work  in a domain at the Windows 2008, Windows 2008R2 and Windows 2012 functional level. A domain at the Windows 2008 level must have at least one Windows 2012 Domain Controller. Adding the DC to the domain will upgrade the domain schema to be able to support GMSA.
The take-away is that you need at least one Windows 2012 domain controller to enable this technology.

Create the Root key

This is a one-time only operation to create the Key Distribution Services Root Key. It is performed on the Windows 2012 DC. Any DC will be able support password renewal and authentication; however, the Windows 2012 DC is best suited for managing the accounts. All account management shown are performed on a Windows 2012 R2 DC.

You must be a member of Domain Admins or Enterprise Admins.

Open a Powershell as Administrator.

Execute the command: Add-KdsRootKey –EffectiveImmediately.

The operation will add the root key to the DC where it is run; however, it can take at least 10 hours for the changes to replicate throughout the domain.

Create Security Group for managed servers.

Each GMSA account has to be associated to a computer. The computer works with the ADS key service to maintain the passwords of the GMSA accounts on the computer. Since the GMSA account will be installed on more than one computer, it makes sense to place the computer objects in a security group. That’s the purpose of this step.

Use Active Directory Users and Computers to create a security group. Ex, ggSQL12Cluster. Add the computer object to the security group.

 Create GMSA Account

After creating a security group for the computer objects, create the GMSA account for use by a service on the computers.

Login to the Windows 2012 DC.

Open a PowerShell window as Administrator.

Execute the commands:

Import-Module Active Directory

Note: this may be required the first time you attempt the command. Since Active Directory module is baked in to a DC, you shouldn’t need to do this again.

New-ADServiceAccount

-Name gMSA_SQL12Svc

-DNSHostName gMSA_SQL12Svc.orion.lcl

-PrincipalsAllowedToRetrieveManagedPassword ggSQL12Cluster

-Enabled $True

The takeaway here is that the PrincipalsAllowedToRetrieveManagedPassword parameter gives the computers in the ggSQL12Cluster security group the ability to maintain the password refresh process.

Add the GMSA to the host computer

Install RSAT

The first step is to add the Active Directory Windows RSAT tools to the computer. There has got to be a better way to add the ActiveDirectory PowerShell module to the computer than this. The downside, iss that you have all the AD DS tools on the server.

Open Server Manager.

Run the Add Roles and Features Wizard.

Check the AD DS Tools of the Remote Server Administration Tools Feature.

Validate a GMSA

In PowerShell on the DC, execute the command: Get-AdServiceAccount –Identity gmsa_SQL12Svc

Install the GMSA

Logon to each server.

Open a PowerShell window as Administrator.

Execute the following commands:

Import-module activedirectory

Install-ADServiceAccount -Identity gMSA_SQL12Svc

Verify that the account is installed on the computer.

Execute the command: Test-ADServiceAccount -Identity gMSA_SQL12Svc

Assign the GMSA account to the SQL service

The following illustrates adding the GMSA to a windows service. The illustration uses SQL server in a two node Always-on Availability Group. GMSA can be used on other applications including IIS app pools, SharePoint, Exchange and scheduled tasks. Note that when setting up an AAG, you do not use the SQL installation. The SQL installer has no concept of this. Use the default or some other account that will get you through the install and setup of the AAG.

Run SQL Configuration Manager.

Select the Engine component –> Properties.

Change the Account Name to the GMSA account.

SQL Engine – Before

SQL Engine – After

That’s it!

SQL Server Access

What other sites don’t tell you is that you need to make a few changes in SQL Server for an Automatic Availability Group to fail over to the nodes in the Windows Cluster. According to some best practices, you should assign domain user accounts to the SQL Service and SQL Agent services. Note that you can’t use gMSA accounts during SQL Server installation. Instead, just use local System account and change it later to use the gMSA account using SQL Configuration tool.

Here’s what you need to do:

Add the account as a SQL Login

• Properties:
• Server Roles –public
• User Mapping: (All databases) Database role membership for <database> – public. For the Agent account , SysAdmin

Grant permission to the gMSA account for the SQL Service connect to the AAG endpoint. Use a SQL Query  “grant connect on endpoint::Hadr_endpoint to [domain\gMSA_Account$]”

Since all SQL Jobs run under the Agent account, you need to add that account to the ACL of where ever you may be copying files. As an example, if I have a share \\OPUSFS\SQLFiles, I need to add the gMSA Agent account to the ACL list for the folder.

That about does it.

Some other postings that are worth referencing:

• How to from Field Engineering –http://blogs.technet.com/b/askpfeplat/archive/2012/12/17/windows-server-2012-group-managed-service-accounts.aspx
• Technet KDS key –http://technet.microsoft.com/en-us/library/jj128430.aspx
• AD Replication Status Tool (..how easily called away…) – http://www.microsoft.com/en-us/download/details.aspx?id=30005
• Technet Service Accounts – http://technet.microsoft.com/en-us/library/dd548356.aspx
• Technet MSA FAQ – http://technet.microsoft.com/en-us/library/ff641729(v=ws.10).aspx
• GMSA and SQL 2012 – http://joedantoni.wordpress.com/2012/12/14/group-managed-service-accounts/
• Service Principal Names(SPN) – http://technet.microsoft.com/library/Cc961723

This posting is provided “as is” with no warranties, guarantees or rights whatsoever.