Updating an existing SQL AlwaysOn cluster to Group Managed Service Accounts

%3CLINGO-SUB%20id%3D%22lingo-sub-1527398%22%20slang%3D%22en-US%22%3EUpdating%20an%20existing%20SQL%20AlwaysOn%20cluster%20to%20Group%20Managed%20Service%20Accounts%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1527398%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EUpdating%20an%20existing%20SQL%20AlwaysOn%20cluster%20to%20Group%20Managed%20Service%20Accounts%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mssqltips.com%2Fsqlservertip%2F5340%2Fusing-group-managed-service-accounts-with-sql-server%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EResources%3A%20%3C%2FA%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mssqltips.com%2Fsqlservertip%2F5340%2Fusing-group-managed-service-accounts-with-sql-server%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mssqltips.com%2Fsqlservertip%2F5340%2Fusing-group-managed-service-accounts-with-sql-server%2F%3C%2FA%3E%3C%2FLI%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fblog.sqlauthority.com%2F2018%2F06%2F01%2Fsql-server-always-on-replica-disconnected-after-changing-sql-server-service-account%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fblog.sqlauthority.com%2F2018%2F06%2F01%2Fsql-server-always-on-replica-disconnected-after-changing-sql-server-service-account%2F%3C%2FA%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIt%20is%20basically%20a%203-step%20process%20to%20implement%20group%20managed%20service%20accounts%20on%20existing%20SQL%20AlwaysOn%20replicas%20if%20you%20want%20it%20to%20go%20smoothly.%3C%2FP%3E%3COL%3E%3CLI%3EEnable%20your%20KdsRootKey%20if%20it%20doesn't%20exist%20and%20create%20group%20managed%20service%20account%20and%20group%20in%20Active%20Directory.%3C%2FLI%3E%3CLI%3EPrepare%20each%20replica%20node%20by%20adding%20group%20managed%20service%20account%20and%20permissions.%3C%2FLI%3E%3CLI%3EChanging%20the%20SQL%20Service%20and%20Agent%20accounts%20on%20each%20node.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2299%25%22%3E%3CH2%20id%3D%22toc-hId--1299891031%22%20id%3D%22toc-hId--1299891031%22%3ESTEP%201%3A%20On%20Domain%20Controller%3C%2FH2%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3COL%3E%3CLI%3ECheck%20and%20see%20if%20key%20exists%3C%2FLI%3E%3C%2FOL%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3ETest-KdsRootKey%20-KeyId%20(Get-KdsRootKey).KeyId%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3COL%3E%3CLI%3EIf%20key%20doesn't%20exist%2C%20create%20it%20on%20DC%3C%2FLI%3E%3C%2FOL%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3EAdd-KdsRootKey%20-EffectiveImmediately%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3COL%3E%3CLI%3ECreate%20new%20AD%20Group%20and%20add%20SQL%20servers%20in%20SQL%20Server%20AlwaysOn%20group%20to%20it%3C%2FLI%3E%3C%2FOL%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3E%24gMSAgrp%20%3D%20'gMSA01SqlPrd'%3C%2FP%3E%3CP%3E%24gMSAAct%20%3D%20'gMSA01'%3C%2FP%3E%3CP%3E%24gMSADNSHostName%20%3D%20'%3CA%20href%3D%22http%3A%2F%2FgMSA01.azure.thojouno.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EgMSA01.azure.thojouno.com%3C%2FA%3E'%3C%2FP%3E%3CP%3ENew-ADGroup%20-Name%20%24gMSAgrp%20-Description%20%E2%80%9CSecurity%20group%20for%20Production%20SQL%20Servers%E2%80%9D%20-GroupCategory%20Security%20-GroupScope%20Global%3C%2FP%3E%3CP%3EAdd-ADGroupMember%20-Identity%20%24gMSAgrp%20-Members%20sqln1%24%2C%20sqln2%24%2C%20sqln3-dr%24%3C%2FP%3E%3CP%3EGet-ADGroupMember%20-Identity%20%24gMSAgrp%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3COL%3E%3CLI%3ECreate%20new%20managed%20service%20account%20on%20DC%3C%2FLI%3E%3C%2FOL%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3E%24gMSAgrp%20%3D%20'gMSA01SqlPrd'%3C%2FP%3E%3CP%3E%24gMSAAct%20%3D%20'gMSA01'%3C%2FP%3E%3CP%3E%24gMSADNSHostName%20%3D%20'-ERR%3AREF-NOT-FOUND-gMSA01.azure.thojouno.com'%3C%2FP%3E%3CP%3ENew-ADServiceAccount%20-Name%20%24gMSAAct%20-PrincipalsAllowedToRetrieveManagedPassword%20%24gMSAgrp%20-Enabled%3A%24true%20-DNSHostName%20%24gMSADNSHostName%20-SamAccountName%20%24gMSAAct%20-ManagedPasswordIntervalInDays%2030%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2299%25%22%3E%3CH2%20id%3D%22toc-hId-1187621802%22%20id%3D%22toc-hId-1187621802%22%3EStep%202%3A%20On%20each%20SQL%20AlwaysOn%20Replica%20(repeat%20for%20each%20node)%3C%2FH2%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3CP%3E1.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Reboot%20each%20replica%20node%20so%20that%20group%20membership%20takes%20affect%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3CP%3E2.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Install%20group%20managed%20account%20on%20node%20and%20add%20managed%20service%20account%20group%20to%20local%20administrators%20group%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3E%24gMSAgrp%20%3D%20'gMSA01SqlPrd'%3C%2FP%3E%3CP%3E%24gMSAAct%20%3D%20'gMSA01'%3C%2FP%3E%3CP%3E%24gMSAUsr%20%3D%20'Azure%5CgMSA01%24'%3C%2FP%3E%3CP%3EInstall-WindowsFeature%20-Name%20RSAT-AD-PowerShell%3C%2FP%3E%3CP%3EInstall-ADServiceAccount%20-Identity%20%24gMSAAct%3C%2FP%3E%3CP%3ETest-ADServiceAccount%20-Identity%20%24gMSAAct%3C%2FP%3E%3CP%3EAdd-LocalGroupMember%20-Group%20%22Administrators%22%20-Member%20%24gMSAgrp%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3CP%3E3.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Grant%20managed%20service%20account%20connect%20permissions%20to%20the%20SQL%20endpoint%26nbsp%3B%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3EUSE%20%5Bmaster%5D%3C%2FP%3E%3CP%3EGO%3C%2FP%3E%3CP%3ECREATE%20LOGIN%20%5BAzure%5CgMSA01%24%5D%20FROM%20WINDOWS%20WITH%20DEFAULT_DATABASE%3D%5Bmaster%5D%3C%2FP%3E%3CP%3EGO%3C%2FP%3E%3CP%3EGRANT%20CONNECT%20ON%20ENDPOINT%3A%3Ahadr_endpoint%20TO%20%5BAzure%5CgMSA01%24%5D%3C%2FP%3E%3CP%3EGO%3C%2FP%3E%3CP%3EALTER%20ENDPOINT%20hadr_endpoint%20STATE%3DSTOPPED%3C%2FP%3E%3CP%3EALTER%20ENDPOINT%20hadr_endpoint%20STATE%3DSTARTED%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2299%25%22%3E%3CH2%20id%3D%22toc-hId--619832661%22%20id%3D%22toc-hId--619832661%22%3ESTEP%203%3A%20Update%20SQL%20Service%20and%20Agent%20accounts%20on%20each%20replica%3C%2FH2%3E%3CH2%20id%3D%22toc-hId-1867680172%22%20id%3D%22toc-hId-1867680172%22%3E%26nbsp%3B%3C%2FH2%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233%25%22%3E%3CP%3E1.%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Using%20dbatools%2C%20update%20SQL%20service%20and%20SQL%20agent%20account%20passwords%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2265%25%22%3E%3CP%3E%23if%20you%20don't%20have%20internet%20connectivity%2C%20you%20can%20always%20update%20the%20SQL%20service%20account%3C%2FP%3E%3CP%3E%23%20via%20the%20SQL%20Server%20Configuration%20utility%3C%2FP%3E%3CP%3EInstall-Module%20dbatools%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%24gMSAAct%20%3D%20'gMSA01'%3C%2FP%3E%3CP%3E%24gMSAUsr%20%3D%20'Azure%5CgMSA01%24'%3C%2FP%3E%3CP%3EImport-Module%20dbatools%3C%2FP%3E%3CP%3EUpdate-DbaServiceAccount%20-ServiceName%20MSSQLSERVER%20-Username%20%24gMSAUsr%3C%2FP%3E%3CP%3EUpdate-DbaServiceAccount%20-ServiceName%20SQLSERVERAGENT%20-Username%20%24gMSAUsr%3C%2FP%3E%3CP%3ERestart-Service%20MSSQLSERVER%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

 

Updating an existing SQL AlwaysOn cluster to Group Managed Service Accounts

Resources:

  1. https://www.mssqltips.com/sqlservertip/5340/using-group-managed-service-accounts-with-sql-server/
  2. 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.

  1. Enable your KdsRootKey if it doesn't exist and create group managed service account and group in Active Directory.
  2. Prepare each replica node by adding group managed service account and permissions.
  3. Changing the SQL Service and Agent accounts on each node.

 

STEP 1: On Domain Controller

  1. Check and see if key exists

Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyId

  1. If key doesn't exist, create it on DC

Add-KdsRootKey -EffectiveImmediately

  1. 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

  1. 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

 

 

 

 

 

 

0 Replies