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.
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]"
Optional: Create constrained delegation
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.
Update the service account in SQL Server Configuration Manager to use the gMSA
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!
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?