Configure Managed Service Accounts for SQL Server Always On Availability Groups

Configure Managed Service Accounts for SQL Server Always On Availability Groups

This article gives an overview of Configuring Managed Service Accounts for SQL Server Always On Availability Groups.

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.

Create a Security Group for GMSA

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

New AD group

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.

AD group properties

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

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts

This article gives an overview of SQL Server Always On Availability Group features. We configured the SQL Server Always On Availability Group from the beginning of building virtual machines.

Install SQL Server 2019 on Windows Server 2016 with SQL Server

In this article, we will discuss configuring SQL Server Always On Availability Groups and perform failover validations. You should go through the following articles and build the infrastructure before proceeding with this article.

An overview of distributed SQL Server Always On Availability Groups

SQL Server 2016 provides a new feature Distributed Availability Group for disaster recovery purposes. It is a particular type of availability group that helps access the multiple failover clusters. In this article’s series, we configured a traditional Always On group. It has the following requirements.

SQL Server Always On Availability Group on the domain-independent Failover Cluster

This article configures SQL Server Always On Availability Groups on the domain independent Windows Failover Clusters. In the previous article, Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups, we learned the new capability in Windows Server 2016 to configure a domain-independent Windows failover cluster.