Updating an existing SQL AlwaysOn cluster to Group Managed Service Accounts
Resources:
- https://www.mssqltips.com/sqlservertip/5340/using-group-managed-service-accounts-with-sql-server/
- https://blog.sqlauthority.com/2018/06/01/sql-server-always-on-replica-disconnected-after-changing-sq...
It is basically a 3-step process to implement group managed service accounts on existing SQL AlwaysOn replicas if you want it to go smoothly.
- Enable your KdsRootKey if it doesn't exist and create group managed service account and group in Active Directory.
- Prepare each replica node by adding group managed service account and permissions.
- Changing the SQL Service and Agent accounts on each node.
STEP 1: On Domain Controller |
- Check and see if key exists
| Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyId |
- If key doesn't exist, create it on DC
| Add-KdsRootKey -EffectiveImmediately |
- Create new AD Group and add SQL servers in SQL Server AlwaysOn group to it
| $gMSAgrp = 'gMSA01SqlPrd' $gMSAAct = 'gMSA01' $gMSADNSHostName = 'gMSA01.azure.thojouno.com' New-ADGroup -Name $gMSAgrp -Description “Security group for Production SQL Servers” -GroupCategory Security -GroupScope Global Add-ADGroupMember -Identity $gMSAgrp -Members sqln1$, sqln2$, sqln3-dr$ Get-ADGroupMember -Identity $gMSAgrp |
- Create new managed service account on DC
| $gMSAgrp = 'gMSA01SqlPrd' $gMSAAct = 'gMSA01' $gMSADNSHostName = 'gMSA01.azure.thojouno.com' New-ADServiceAccount -Name $gMSAAct -PrincipalsAllowedToRetrieveManagedPassword $gMSAgrp -Enabled:$true -DNSHostName $gMSADNSHostName -SamAccountName $gMSAAct -ManagedPasswordIntervalInDays 30 |
Step 2: On each SQL AlwaysOn Replica (repeat for each node) |
1. Reboot each replica node so that group membership takes affect | |
2. Install group managed account on node and add managed service account group to local administrators group | $gMSAgrp = 'gMSA01SqlPrd' $gMSAAct = 'gMSA01' $gMSAUsr = 'Azure\gMSA01$' Install-WindowsFeature -Name RSAT-AD-PowerShell Install-ADServiceAccount -Identity $gMSAAct Test-ADServiceAccount -Identity $gMSAAct Add-LocalGroupMember -Group "Administrators" -Member $gMSAgrp |
3. Grant managed service account connect permissions to the SQL endpoint | USE [master] GO CREATE LOGIN [Azure\gMSA01$] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [Azure\gMSA01$] GO ALTER ENDPOINT hadr_endpoint STATE=STOPPED ALTER ENDPOINT hadr_endpoint STATE=STARTED |
STEP 3: Update SQL Service and Agent accounts on each replica |
1. Using dbatools, update SQL service and SQL agent account passwords | #if you don't have internet connectivity, you can always update the SQL service account # via the SQL Server Configuration utility Install-Module dbatools
$gMSAAct = 'gMSA01' $gMSAUsr = 'Azure\gMSA01$' Import-Module dbatools Update-DbaServiceAccount -ServiceName MSSQLSERVER -Username $gMSAUsr Update-DbaServiceAccount -ServiceName SQLSERVERAGENT -Username $gMSAUsr Restart-Service MSSQLSERVER |
