Migrate SQL Server Always ON Availability Group (AG) to use Group Managed Service Account (gMSA)
- George Lin
- Mar 14, 2022
- 8 min read
Updated: Mar 24, 2022
For detailed introduction about Group Managed Service Account, click here.
Key concepts help understand gMSA:
A Standalone Managed Service Account (sMSA): is a managed domain account that provides automatic password management, simplified service principal name (SPN) management and the ability to delegate the management to other administrators. It has one-to-one relationships between sMSA and computer. Similar to Azure System Managed Identity
A Group Managed Service Account (gMSA): as an extension of sMSA, provides the same functionality within the domain but also extends that functionality over multiple servers through Active Directory Security Groups. It allows a one-to-many relationship between an gMSA and the computers in the same farm. When connecting to SQL Server instances hosted on a Windows cluster through Availability Group Listener Solution, the authentication protocols require that all instances of SQL Server use the same principal. When a gMSA is used as service principals, the Windows operating system manages the password for the account instead of relying on DBA to rotate and sync the password.
The Microsoft Key Distribution Service (kdssvc.dll): provides the solution to securely create, save and obtain the keys with a key identifier for an Active Directory account. When creating a gMSA, the domain controller require a root key provided by the KDS to begin generating gMSA passwords. Member computers obtain the current and preceding password values of gMSA by contacting a domain controller.
Requirements for creating gMSA:
The domain forest schema has been updated to Windows Server 2012 or above
The Key Distribution Service Root Key, serve as master key for Active Directory, has been created
At least one Windows Server 2012 (or above) Domain Controller in the domain in which the gMSA will be created
Your account has membership in Domain Admins, Account Operators, or ability to create msDS-GroupManagedServiceAccount objects
Note: Only SQL Server 2014 and above supports group Managed Service Accounts when running on Windows Server 2012 R2 and above.
Consideration about changing Database Engine startup service account:
Beginning with SQL Server 2012, as one of the security enhancements, SQL Server enables per-service SID for each of its services to provide service isolation and defense in depth. The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for the Database Engine service might be NT Service\MSSQL$<InstanceName>. Service isolation enables access to specific objects without the need to run a high-privilege account or weaken the security protection of the object. By using an access control entry that contains a service SID, a SQL Server service can restrict access to its resources.
For most components SQL Server configures the ACL for the per-service account, not the startup domain service account, directly, so changing the service account, which should only have very minimum privileges, can be done without having to repeat the resource ACL process.
The account assigned to start a service needs the Start, stop and pause permission for the service. The SQL Server Setup program or Configuration Manager tool automatically assigns this.
To change the service startup account for the Database Engine and SQL Server Agent, the SQL Server service must be restarted for the change to take effect. When the service is restarted, all databases associated with that instance of SQL Server will be unavailable until the service successfully restarts.
Step 1: Create the Key Distribution Service (KDS) Root Key
On the Windows Server 2012 or later domain controller, Start a Windows PowerShell session and run the scripts below:
To check if the KDS root already exists:
Get-KdsRootKeyTo create the rook key:
Add-KdsRootKey -EffectiveImmediatelyThe output should be like this:
Guid
----
6f7703ff-3648-8ced-3981-1fd748f6f26cBe aware that even with the Effective Immediately configuration switch, the domain controllers will wait up to 10 hours from time of creation to allow all DCs to converge their AD replication before the key to be active to allow the creation of a gMSA.
To create the KDS root key for immediate effectiveness in a test environment with only one DC, run this:
Add-KdsRootKey -EffectiveTime ((get-date).addhours(-10))To test the root key:
Test-KdsRootKey -KeyId (Get-KdsRootKey).KeyIdThe output should be like this:
TrueTo show the root key info:
Get-KdsRootKeyThe output should be like this:
AttributeOfWrongFormat :
KeyValue : {23, 177, 199, 200…}
EffectiveTime : 3/8/2022 7:25:14 PM
CreationTime : 3/8/2022 7:25:14 PM
IsFormatValid : True
DomainController : CN=MYDC01,OU=Domain Controllers, DC=MyTestDomain, DC=azure
ServerConfiguration : Microsoft.KeyDistributionService.Cmdlets.KdsServerConfiguration
KeyId : 6f7703ff-3648-8ced-3981-1fd748f6f26c
VersionNumber : 1Step 2: Install Active Directory PowerShell module on ALL SQL Server machines (AG replicas)
Install-WindowsFeature -Name RSAT-AD-POWERSHELLTo confirm the feature installation:
Get-WindowsFeature -Name RSAT-AD-POWERSHELLThe output should look like this:
Display Name Name Install State
------------ ---- -------------
RSAT-AD-PowerShell InstalledStep 3: Create an AD security group and add all computer members (AG cluster nodes)
New-ADGroup -Name gsg_gMSA01 -Description "Security group for gMSA01 computers" -GroupCategory Security -GroupScope Global
Add-ADGroupMember -Identity gsg_gMSA01 -Members sqlserver-0$,sqlserver-1$,sqlserver-2$,sqlserver-3$,sqlserver-4$The first command creates a new AD global security group named "gsg_gMSA01". The second command adds the following domain computer accounts to the new security group:
sqlserver-0
sqlserver-1
sqlserver-2
sqlserver-3
sqlserver-4These computers are Windows failover cluster nodes running SQL Server Always On Availability Group replicas. Please notice that when adding the computer objects to the security group, the name of the computer account needs to be suffixed with a $.
To verify the security group members:
Get-ADGroupMember -Identity gsg_gMSA01The output should look like this:
distinguishedName : CN=sqlserver-0,CN=Computers,DC=MyTestDomain,DC=azure
name : sqlserver-0
objectClass : computer
objectGUID : 419aa7cc-a399-4bcc-9de4-d74c55bcb66d
SamAccountName : SQLSERVER-0$
SID : S-1-5-21-1074903847-1173234034-1927541698-17608
distinguishedName : CN=sqlserver-1,CN=Computers,DC=MyTestDomain,DC=azure
name : sqlserver-1
objectClass : computer
objectGUID : ea02d020-8469-4549-b736-27269fbcf504
SamAccountName : SQLSERVER-1$
SID : S-1-5-21-1074903847-1173234034-1927541698-17609
distinguishedName : CN=sqlserver-2,CN=Computers,DC=MyTestDomain,DC=azure
name : sqlserver-2
objectClass : computer
objectGUID : 4d982283-92b9-4b7f-bf43-8b6bd994bbf2
SamAccountName : SQLSERVER-2$
SID : S-1-5-21-1074903847-1173234034-1927541698-18604
distinguishedName : CN=sqlserver-3,CN=Computers,DC=MyTestDomain,DC=azure
name : sqlserver-3
objectClass : computer
objectGUID : c015eac9-7749-4e81-aff2-6f4b27951dfb
SamAccountName : SQLSERVER-3$
SID : S-1-5-21-1074903847-1173234034-1927541698-22604
distinguishedName : CN=sqlserver-4,CN=Computers,DC=MyTestDomain,DC=azure
name : sqlserver-4
objectClass : computer
objectGUID : 7238e742-6569-4740-8042-1e2d65f69f76
SamAccountName : SQLSERVER-4$
SID : S-1-5-21-1074903847-1173234034-1927541698-23104Step 4: Rolling restart all member SQL Server computers (AG cluster nodes)
The member computers need to be restarted in order to force the group membership to be picked up in the domain.
Step 5: Create Group Managed Service Account
New-ADServiceAccount -Name gMSA01 -PrincipalsAllowedToRetrieveManagedPassword gsg_gMSA01 -Enabled:$true -DNSHostName gMSA01.MyTestDomain.azure -SamAccountName gMSA01 -ManagedPasswordIntervalInDays 30The command above creates a new gMSA named "gMSA01". The group created in Step 3 is specified for the parameter PrincipalsAllowedToRetrieveManagedPassword. The password rotation frequency is configured to be 30 days as well through the parameter ManagedPasswordIntervalInDays. The password change interval can only be set during creation. If you need to change the interval, you must create a new gMSA and set it at creation time.
If the New-ADServiceAccount command throws this error:
New-ADServiceAccount: Key does not exist.This might be due to the fact that the KDS root key hasn't been replicated to all domain controllers. Wait a bit longer, then try it again.
To show the information about the gMSA:
Get-ADServiceAccount -Identity gMSA01The output should be like this:
DistinguishedName : CN=gMSA01,CN=Managed Service Accounts, DC=MyTestDomain,DC=azure
Enabled : True
Name : gMSA01
ObjectClass : msDS-GroupManagedServiceAccount
ObjectGUID : f53dbfcb-387d-4b06-bc6f-0ef89c1c34f0
SamAccountName : gMSA01$
SID : S-1-5-21-1074903847-1173234034-1927541698-28604
UserPrincipalName :Step 6: Open inbound port TCP/9389 on all the domain controllers
The PowerShell command used in next steps is from AD module which depends on Active Directory Web Services on DC to communicate with ADDS. The TCP port 9389 on the domain controller must be accessible from all member computers to communicate properly with ADWS.
Make sure both Windows Server local firewall and Azure Network Security Group (NSG) are configured to allow inbound traffic on port TCP/9389 to all the DCs in the domain
Step 7: Install the gMSA on All member SQL Server computers (AG cluster nodes)
To install gMSA:
Install-ADServiceAccount -Identity gMSA01To very gMSA
Test-ADServiceAccount -Identity gMSA01The output should be like:
TrueStep 8: Create and Configure SQL Server login for the gMSA on all SQL Server instances (AG replicas)
Run T-SQL script below in a query window connecting to the member SQL Server instances
USE [master]
GO
CREATE LOGIN [MyTestDomain\gMSA01$] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [MyTestDomain\gMSA01$]
GOTo verify the SQL Server permissions granted to the SQL Server service account login
SELECT * FROM fn_my_permissions('[MyTestDomain\gMSA01$]', 'login');
GO
SELECT pri.name, per.* FROM sys.server_permissions per
INNER JOIN sys.server_principals pri
ON per.grantee_principal_id = pri.principal_id
WHERE per.class_desc = 'EndPoint'
AND pri.name = 'MyTestDomain\gMSA01$';
GO
Step 9: Configure File System Permissions for Database Engine Access (Optional) on all AG cluster nodes
The Database Engine service must have permission of the Windows file system to access the file folder where database files are stored. Permission to the default location is configured during setup. If the database files are in a different location, you must grant the Database Engine the full control permission to that location. It is the per-service SID, not the startup service account, that must be assigned the access permission on the file location.
After switching the service account, If you noticed some databases are not online, for example, the database state is "recovery pending", It's most likely because the Database Engine per service SID (NT SERVICE\MSSQLSERVER for a default instance, or NT SERVICE\MSSQL$InstanceName for a named instance) doesn't have enough privilege accessing the database files, but why it was OK before switching the service account? it might be due to the fact that the old service account is member of the built-in local security group Administrators which makes the Database Engine SID inherent the full control access on any files in any folder.
To configure File System Permission to the Per-service SID, you can use Windows File Explorer, or use the PowerShell script below (In a Run as Administrator PS session)::
$Paths = "G:\Data", "I:\data", "H:\log", "J:\Log", "K:\Log"
# To show the currently ACL
foreach ($Path in $Paths) {
(Get-Acl -Path $Path).Access | Format-Table
}
# Set properties
$identity = "NT SERVICE\MSSQLSERVER"
#$fileSystemRights = "Read,Write"
$fileSystemRights = "FullControl"
$InheritanceFlags = 1,2
$PropagationFlags = 0
$type = "Allow"
# Create new rule
$fileSystemAccessRuleArgumentList = $identity, $fileSystemRights,$InheritanceFlags,$PropagationFlags, $type
$fileSystemAccessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $fileSystemAccessRuleArgumentList
# Apply new rule
foreach ($Path in $Paths) {
$NewAcl = Get-Acl -Path $Path
$NewAcl.SetAccessRule($fileSystemAccessRule)
Set-Acl -Path $Path -AclObject $NewAcl
(Get-Acl -Path $Path).Access | Format-Table
}
#For Azure SQLVM, If the SQL Server tempdb files are on D: drive,
# Add [NT SERVICE\MSSQLSERVER] to the local Admin group so that Database
# Engine has permission to create tempdbs after the Azure VM restart
Add-LocalGroupMember -Group Administrators -Member "NT SERVICE\MSSQLSERVER" -Confirm:$false
# To remove the account from ACL
# foreach ($Path in $Paths) {
# $NewAcl = Get-Acl -Path $Path
# $NewAcl.RemoveAccessRule($fileSystemAccessRule)
# Set-Acl -Path $Path -AclObject $NewAcl
# (Get-Acl -Path $Path).Access | Format-Table
# }Configure permission on the network shared folder
If the SQL Server needs to put the database backup files in a shared folder, grant the proper permission on the folder to the gMSA
Step 10. Enable the Lock Pages in Memory Option
Use the Windows Group Policy tool (gpedit.msc) to enable the lock pages in memory option for the new gMSA used by SQL Server.
On the Start menu, click Run. In the Open box, type gpedit.msc.
On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder. The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Setting - Lock pages in memory dialog box, click Add User or Group.
In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account "MyTestDomain\gMSA01$".
Restart the SQL Server Service for this setting to take effect.
Step 11: Change SQL Server service account to gMSA on all member SQL Server computers (AG cluster nodes)
Now it is time to use the new gMSA as the service account for SQL Server. There are two options for updating the service account details. The first is to use the SQL Server Configuration Manager tool. Make sure that the “Service Accounts” object type is selected for account validation:
The other option is to make use of the Update-DbaSqlServiceAccount cmdlet from the DBATools PowerShell module.
For example, on sqlserver-1,
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Find-Module DBATools | Install-Module -Force
Update-DbaServiceAccount -Server sqlserver-1 -ServiceName MSSQLSERVER -Username 'MyTestDomain\gMSA01$'The output is like this:
ComputerName : SQLSERVER-1
ServiceName : MSSQLSERVER
State : Running
StartName : MyTestDomain\gMSA01$
Status : Successful
Message : The login account for the service has been successfully set.Step 12: Reenable SQL Server Agent on all the member SQL Servers (AG cluster nodes)
When changing the service startup account for the Database Engine. the SQL Server service (the Database Engine) must be restarted for the change to take effect. Restarting the Database Engine may not shutdown SQL Server Agent, but when the service is stopped, the SQL Server Agent service is automatically stopped and needs to be brought back online manually
Step 13: Verify the state of SQL Server Always ON Availability Group
Check the databases state on all replicas
Check the execution status of all the SQL Server Agent jobs
Review the SQL Server error log for any abnormal messages
Test failover/fail back
Step 14: Test creating some new databases and add them to the AG
This is to make sure after changing the startup service account , the Database Engine is still able to create database files in all configured folders
Step 15: Clean up the old SQL Server service account
Disable the old/original SQL Server service login on all AG replicas
Wait for a week
Remove the old SQL Server service login from all AG replica
Remove the old SQL Server service account from AD
コメント