One of the really enjoyable things about my current job, is being able to work on some of the latest technology as it comes out. My team owns the infrastructure build processes, so I get to do fun things like building proof of concept environments, SAN testing, and working with new versions of things. We’ve been spending a lot of time working with some of the new features of Windows Server 2012, in an effort to get it ready for production rollout. We’re ready to go, but we are still waiting on our antivirus team.
For those of you who work in organizations with any sort of regulatory concern (which is pretty much any public corporation), you know that managing the accounts that own SQL Server services can be a bit of nightmare. You have to maintain passwords, and if the password changes at any point in time, it’s a very manual process to change it on each of your servers. Its best security practice to change these passwords on a regular basis, and particularly if a member of the DBA team leaves. However changing this password requires touching every server in some fashion—you can automate this process, but it’s still kind of painful. Also, if that account ever gets locked out—it can be really painful.
Starting with Windows Server 2008 R2 the concept of Managed Service Accounts came into play. These were accounts with Active Directory internal passwords—set to change on a regular basis, but controlled by the system. The best part of this was that AD did the password change and propagated it out to the server. The problem with this for large environment was that there was a 1:1 ratio of accounts to servers—which mean we could never use it on a SQL Cluster, and it wasn’t necessarily practical (though a best security practice) to have a Domain account for each SQL Server in your environment.
With Windows Server 2012, this concept has been expanded to Group Managed Service Accounts (gMSAs)—which have one account that can be used by multiple servers. So this makes it suitable for clusters* (the * is for AlwaysOn Availability Groups—more on that later), which is very important in my organization, as we are heavily clustered. Note—this process includes a number of steps which will require your Active Directory team. The initial steps actually have to take place on a Domain Controller. I highly recommend showing this post to your Windows Admin—it was an excellent resource.
The prerequisites for this are as follows:
- The Active Directory schema in the gMSA domain’s forest has to be updated to Windows Server 2012 in order to create a gMSA (note this can be done on a 2008/2008R2 Domain Controller). Send your Admin here.
- The Key Distribution Services KDS root key needs to be created. A domain admin needs to do this on a domain controller. See here.
- You have to wait 10 hours after creating this key—really you do. You can advance the time in your domain for testing, but don’t do that.
- The target servers for the gMSA need to be running Windows Server 2012.
So after you domain admin has created the key, then it’s time to create the gMSA. Run the following PowerShell command as admin (may need to be run by your domain admin):
New-ADServiceAccount gmsa1 –DNSHostName membersrv1.domain.tld –PrinicipalsAllowedToRetrieiveManagedPassword ShortGroupName
Now you have your Group Managed Service Account:
Here’s the AD Information on it:
One thing I didn’t mention before is setting up groups. This is going to depend on the size of your environment and your management philosophy. In our case, I’m thinking about creating three groups—production, non-production, and mission critical. Some shops may create one for each cluster or server, it really depends on your needs.
Also, by default, a gMSA will change the password every 30 days—this is modifiable, but since it’s a system managed password, I generally don’t want to mess with it. The beautiful thing about this, is that when AD changes the password, since it is system managed, you don’t have to touch your SQL Servers at all. It just works.
One interesting note, you will need to add the Active Directory Domain Services role to your SQL Servers. This will enable the PowerShell command you need to use to install the service account on each of your servers. You can do this by issuing the following PowerShell command:
Install-WindowsFeature –Name AD-Domain-Services –ComputerName $servername –IncludeManagementTools
After this, you need to install the account on each server, once again using PowerShell:
Install-ADServiceAccount gMSA1
After that, when installing SQL Server just search for the account in Active Directory. You need to make sure the “Service Account” object is checked off.
I’ve selected the Service Account—note that the password box is greyed out. You don’t enter the password (since only AD knows what it is).
I know a password shows up in Configuration Manager—don’t mess with this—it’s managed exclusive by Active Directory.
I have tested this configuration in standalone and SQL Server Failover Cluster instances (only in SQL 2012, however), and it works fine. AlwaysOn Availability Groups, however act a little weird, I will follow up with another blog post on this, but initially everything configured fine. However, when I tried to failover, I got two really ugly SSPI errors, but then it just worked. I haven’t seen a statement from Microsoft on support for this feature, so stay tuned.