Group Managed Service Accounts

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.

Vendors, Again—8 Things To Do When Delivering a Technical Sales Presentation

In the last two days, I’ve sat through some of the most horrific sales presentations I’ve ever done—this was worse than the time share in Florida. If you happen to be a vendor and reading (especially if you are database vendor—don’t worry it wasn’t you), I hope this helps you craft better sales messages. In one of these presentations, the vendor has a really compelling product that I still have interest in, but was really put off by bad sales form.

I’ll be honest, I’ve never been in sales—I’ve thought about it a couple times, and still would consider it if the right opportunity came along, but I present, a lot. Most of these things apply to technical presentations as well as sales presentations. So here goes.

The top 8 things to do when delivering a sales presentation:

  1. Arrive Early—ask the meeting host to book your room a half hour early and let you in. This way you can get your connectivity going, and everything started before the meeting actually starts, wasting the attendee’s valuable time, and more importantly cutting into your time to deliver your sales message. Also starting on time allows you to respect your attendees’ schedules on the back end of the presentation.
  2. Bring Your Own Connectivity—if you need to connect to the internet (and if you have remote attendees, you do) bring your own connectivity. Mobile hotspots are widely available, and if you are in sales you are out of the office most of the time anyway, consider it a good investment.
  3. Understand Your Presentation Technology—please understand how to start a WebEx and share your presentation. If you have a Mac have any adapters you need to connect to video. If you want to use PowerPoint presentation mode (great feature by the way) make sure the audience doesn’t see the presenter view, and sees your slides. Not being able to do this is completely inexcusable.
  4. Understand Who Your Audience Is—if you are presenting to very Senior Infrastructure architects in a large firm, you probably don’t need to explain why solid state drives are faster than spinning disks. Craft your message to your intended audience, especially if it has the potential to be a big account. Also, if you know you are going to have remote attendees don’t plan on whiteboarding anything unless you have access to some electronic means to do so. You are alienating half of your audience.
  5. Don’t Tell Me Who Your Customers Are—I really don’t care that 10 Wall St banks use your software/hardware/widget. I think vendors all get that same slide from somewhere. Here’s a dirty little secret—large companies have so many divisions/partners/filing cabinets that we probably do own 90% of all available software products. It could be in one branch office that some manager paid for, but yeah technically we own it.
  6. I Don’t Care Who You Worked For—While I know it may have been a big decision to leave MegaCoolTechCorp for SmallCrappyStorageVendor, Inc., I don’t really care that you worked for MegaCoolTechCorp. If you mention it once, I can deal with it, but if you keep dropping the name it starts to get annoying and distracting.
  7. Get on Message Quickly—don’t waste a bunch of time telling me about marketing, especially when you go back to point #4—knowing your audience. If you are presenting to a bunch of engineers, they want to know about the guys of your product, not what your company’s earnings were. Like I mentioned above, one of the vendors I’ve seen recently has a really cool product, which I’m still interested in, but they didn’t start telling me about the product differentiation until 48 minutes into a 60 minute presentation.
  8. Complex Technical Concepts Need Pictures—this is a big thing with me. I do a lot of high availability and disaster recovery presentations—I take real pride in crafting nice PowerPoint graphics that take a complex concept like clustering and simplify it so I can show how it works to anyone. Today’s vendor was explaining their technology, and I was pretty familiar with the technology stack, yet I got really lost because there were no diagrams to follow. Good pictures make complex technical concepts easy to understand.

I hope some vendors read this and learn something. A lot of vendors have pretty compelling products, but fail to deliver the sales message which is costing them money. I don’t mind listening to a sales presentation, even for a vendor I may not buy something from, but I do really hate sitting through a lousy presentation that distracts me from the product.

SQL Saturday #200 – Philadelphia 2013

It is with great pride that I announce the 200th SQL Saturday today. It’s with even more exuberance that I announce that it will be my user group’s event in Philadelphia (well Malvern to be specific). June 1st 2013, at Microsoft, where we had the event last year.

The webpage is here.

I had a great time running last year’s event, and was really happy with how it went, and I look forward to putting on another great event. We will do something special, since this is #200 and we are in the bicentennial city.

%d bloggers like this: