Introduction

DBA uses services accounts to run the various SQL Services. Usually, we should use a separate service account for an individual server SQL Services.

• You should run SQL services having the least permissions
• You should use a complex password and store it in a secure place
• Its password should never expire
• You should also change the password regularly, depending upon your organization’s security policy

If you maintain a large inventory of SQL Servers, you might think it is a cumbersome task to change and maintain the passwords for these servers. Once you change the service account password using SQL Server Configuration Manager, it also requires the restart of SQL Services. It might be a challenging task as well to get downtime for highly transactional applications.

We can leverage Group Managed Service Accounts (gMSA) in these cases. Let’s explore it in the subsequent section.

Prerequisites

1. You should follow the article series, Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups and configure the following

• The virtual machine acts as a domain controller and active directory
• Three SQL nodes with SQL Server 2019 installed
• You should configure SQL Server Always On Availability Groups for these three nodes in synchronized mode

1. Windows PowerShell with active directory module installed. We installed it as a part of an active directory configuration

An Overview of Managed Service Accounts

We have two kinds of managed service accounts in an active directory configuration.

• Standalone Managed Service accounts(SMSA) provides the functionality of automatic password management. In simplified terms, users do not manage credentials for these users. It automatically changes the password and synchronizes with the services as per the active directory policy. We can use a standalone managed service account for a single server
• Group Managed Service accounts (gMSA) extend the functionality of SMSA. You can use gMSA for multiple servers. We define an AD group and provide permissions for all required servers that can use the credentials of the specified gMSA

To summarize, you get the following benefits using gMSA as the service account for SQL Services.

• Automatic password management
• Administrators do not require to store the passwords in a password vault
• It uses a very complex password(120 characters), and it is not known to administrators as well. It avoids the risk of circulating the password unknowingly as well
• Automatic SPN registration
• You can use them across multiple servers and services
• You can use gMSA on standalone servers or services that run on top of a failover cluster service such as Windows service, app pool, scheduled task

Let’s start configurations of the Group Managed Service accounts (GMSA) for SQL Server Always On availability groups.

Configuration of gMSA for SQL Services

We can configure and use the gMSA service accounts for Windows Server 2012 or later. In this article, we will work with Windows Server 2016.

Step 1: Create a Security Group for gMSA

Take an RDP of the active directory server and Launch active directory (AD) using DSA.MSC command.

Right-click on the domain name and choose New -> Group. Specify a group name as per your requirement or naming convention.

Click Ok, and it creates the AD group. Open this group and enter a description. It helps you to identify the security group and its purpose efficiently.

Click on Members. In the members, add the failover cluster nodes. I have SQLNode1, SQLNode2, and SQLNode3 virtual machines configured in a SQL Server Always On availability group.

#alwayson availability groups #installation #setup and configuration #sql