Secure AND Easy Service Account Management

[SqlBenjamin]

Like most companies, we have various automations and tools we use to do everything it takes to run Microsoft Endpoint Manager Configuration Manager (a.k.a. MEMCM, MECM, SCCM, System Center Configuration Manager, or even SMS but we’ll refer to it as MECM) and Intune. To have those run well we follow the common best practice of using dedicated service accounts.

One example of that is for running the SQL services, which is the back end for MECM. For many years, the best practice was to have a service account for each SQL service – and technically for each instance of SQL. We used separate accounts but not for each server because the management was painful enough as it was. We had to perform password rotations on each server/service regularly – and store those passwords securely somewhere. While I’d like to think that we did a great job of keeping all this secure, the truth is the passwords had to be copied somewhere for people to perform the password rotation. And I’m sure at times passwords were shared in unsecure ways. So, to improve our security and make our lives easier at the same time, we decided to switch to using a Group Managed Service Account (gMSA).

The remarkable thing about a gMSA is that Active Directory (AD) manages the password for you. That’s right, you don’t have to create, store, or update the password! So there’s no chance that it’ll get compromised. Additionally, the SQL services don’t require a restart when the password is updated, which is required when the password changes for a service account. Pretty great right? You can do your own reading and research on gMSA here: https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-manage...

Before I continue, I’d like to mention some of my friends who work at Tata Consultancy Services who did most of the work to make all this happen: Rakesh Silam, RamPratap Yadav, Varun Bejugam, and Dhanraj Rajendraodayar. These are some of the best admins you could ever work with.

Alright, let’s get into the implementation info...here is what we did to get our SQL Server services running with a gMSA. Note: this is not meant to be a step by step guide.

Pre-Requisites

First make sure to look through the documentation mentioned above for the pre-reqs to ensure you meet all of them. For AD to generate passwords for gMSAs, a KDS root key needs to be created. You can follow the information at this location if you need to do that: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2012-R2-and-2012/jj...

Implementation

For simplicity sake we wanted to be able to use one gMSA for all our SQL Servers. This may help explain why we went with the following steps

1. Create a security group for the servers on which the gMSA will run
2. Add the servers on which the gMSA will run into the security group
3. Create a gMSA account
   1. This needs to be done via PowerShell, the command New-ADServiceAccount is what you use.
4. Create Service Principal Names (SPNs) for the SQL Service and gMSA
   1. See https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-... for an overview of SPNs.
   2. As a sanity check, you can run “setspn -l [nameofyourgmsa]” to ensure that the SPNs exist and to ensure that they are set correctly. You should see the entries in the following format:  "MSSQLSvc/[Server Name - with/without FQDN]:[port number]"
5. Optional: Create constrained delegation
   1. We make use of linked server queries from time to time so in order to avoid a double hop issue we enable constrained delegation for the SQL Servers. In fact, we use bi-directional constrained delegation to avoid issues that could arise based on from which server the query is started.
   2. For more information about constrained delegation see: https://docs.microsoft.com/en-us/windows-server/security/kerberos/kerberos-constrained-delegation-ov...
6. Update the service account in SQL Server Configuration Manager to use the gMSA
   1. When you add the gMSA you do not need to fill the password in, just add the account and apply. AD takes care of the password for you!

Conclusion

With all that completed all our SQL Server services are running under the gMSA. We no longer worry about password management/rotation and we have increased security. Will you be moving your services to run under a gMSA?