Update SQL Server Service and Agent accounts with the gMSA

We will be updating the SQL Server service and agent accounts with the new Group Managed Service Account. We will be using the dbatools module to do this remotely

  1. Within the jumpserver, and with Powershell ISE still up, open up a new tab by hitting CTRL+N, and entering the below script to update the service accounts remotely:
$nodes = "EC2SQL1", "EC2SQL2" #Defines the servers 
[string]$DomainName = (Get-WmiObject win32_computersystem).domain #extracts the domain name via PS
$DomainPre = $DomainName.split('.')[0]; #extracts just the name
$gMSAUsr = 'gMSA01' #This will be the Group Managed Service Account Name
$DomainGMSA = $DomainPre + '\' + $gMSAUsr + '$'; #generates the proper account name format to be used for the service account

Update-DbaServiceAccount -ComputerName $nodes -ServiceName 'MSSQLSERVER','SQLSERVERAGENT' -Username $DomainGMSA #dbatools command to remotely change SQL related service accounts
Start-Sleep -s 10

#The commands below will clean up duplicate/invalid service principal names
$EC2SQL1Spn = ' -D MSSQLSvc/EC2SQL1.' + $DomainName + ':1433 EC2SQL1';
$EC2SQL2Spn = ' -D MSSQLSvc/EC2SQL2.' + $DomainName + ':1433 EC2SQL2';
$Prms1 = $EC2SQL1Spn.Split(" ")
$Prms2 = $EC2SQL2Spn.Split(" ")
& "SETSPN"  $Prms1 | Out-String
& "SETSPN"  $Prms2 | Out-String
Start-Sleep -s 10
#will restart servers to take full-effect
Restart-Computer -ComputerName $nodes -Wait -For Wmi -Force -Protocol WSMan
Start-Sleep -s 10
ipconfig /flushdns
  1. Once executed, you should see an output similar to below
  2. To validate, RDP into each of the DB servers, go to SQL Configuration Manager, and check the services running with the new gMSA: