Migrating Traditional SQL Server Service Accounts to Group Managed Service Accounts(gMSA)- Part 2

by Deepthi Goguri, posted in Solutions

This post is about the Failover cluster Instances and SQL Servers with high availability groups enabled.

Source

A Failover Cluster Instance (FCI) is a SQL Server high-availability solution where multiple servers share the same storage. If the active node fails, SQL Server automatically fails over to another node with minimal downtime.

You need to follow all the steps mentioned in the first blog post, which you can find here.

Steps to Change Service Account with FCI or Servers with HA to gMSA Service accounts

  1. In Failover Cluster Manager (or PowerShell): Get-ClusterGroup on open it
  2. Find the cluster group for your SQL instance: e.g. SQL Server (MSSQLSERVER).
  3. Note which node is OwnerNode (active).
  4. On the active node (the one currently owning the SQL FCI resources):
  5. Open SQL Server Configuration Manager.
  6. Right-click SQL Server (MSSQLSERVER) select Properties.
  7. Click Log On tab.
  8. Change Account Name to: domain\gms{func}{env}sql$, including the $ at the end of the account. NOTE: Include the $ at the end. That tells Windows it’s a gMSA.
  9. Repeat process for SQL Server Agent (MSSQLSERVER)
  10. NOTE: Do not enter a password, leave it blank. gMSA handles passwords automatically
  11. Restart SQL services
  12. Open the Cluster Manager > Right-click the SQL Server role > Stop Role
  13. Right click the FCI SQL Role again, select Start Role
  14. Open the SQL Sevrer Configuration Manager
  15. Verify the SQL Server and Agent services started successfully under the gMSA and the SQL instance comes online.
  16. Note: For the FCI, you must change the service account once in the active node server. The changes will be replicated over to the other nodes (active or not) automatically. That’s the magic of gMSA.

Failover Testing (Safe Procedure)

Once verified on one node, test failover to ensure the gMSA works cluster-wide.

  1. Open Failover Cluster Manager
  2. Right-click your SQL Server role (e.g. SQL Server (MSSQLSERVER))
  3. Click Move, then click Select Node…
  4. Choose the passive node.
  5. Watch the role come online on that node.
  6. Verify SQL services start automatically under the same gMSA on the new node. No manual intervention needed (that’s the gMSA magic).

If successful, fail back to the original node.

Validation Checks

  1. Open SQL Server Management Studio. You should be able to connect to SQL Server locally on the Server and remotely.
  2. Run SELECT servicename, service_account FROM sys.dm_server_services;
  3. Confirm both SQL and Agent show correctrly
  4. Open Event Viewer
  5. Click Application log
  6. Search for for Event ID 17162 (SQL Server) startup success.
  7. Confirm there were no login or service control errors.
  8. Test logins, linked servers, backups, and Agent jobs.

Rollback Plan

Should testing fail, or services not come back online:

  1. Request Server engineering team to revert the SPN’s back to the previous service account from the gMSA account.
  2. Change the gMSA SQL Server and SQL Server agent service accounts back to the regular service account.
  3. Restart the SQL Services.

References: Manage group Managed Service Accounts

Thank you for reading!

Leave a comment