MS SQL AlwaysOn Availability Groups with Managed Service Accounts

This is a practical guide for configuration Microsoft SQL Server with AlwaysOn Availability Groups using Managed Service Accounts.

This is not a guide for installing Microsoft SQL Server as there are plenty of existing resources to do so. This guide assumes that you have a pair of fresh SQL Servers or that you have the knowledge to create and install a best practices SQL Server.

Requirements

  • Two or more ready SQL Servers.
  • Domain Administrator, or delegated privileges to create Computer Accounts, Managed Service Accounts, and Security Enabled Groups.
  • Domain Administrator, or delegated privileges to set Service Principal Names.
  • Local administrative privileges on the SQL Server operating system.
  • SA privileges on the SQL Server instances.

Summary Steps

  • Pre-Create Active Directory Resources.
    • Computer Account Objects
    • Managed Service Account Object
    • Service Principal Names
  • Configure the SQL Server operating system.
    • Local Firewall
    • Local Security Policy
    • Network Adapters
  • Create the Windows Server Failover Cluster instance.
  • Configure a File Share as a cluster witness.
  • Enable AlwaysOn functionality for MS SQL.
  • Configure the Managed Service Account for MS SQL.
  • Create the Availability Group.

Pre-Create Active Directory Resources

Computer Account Objects

The first step is to pre-create the required Active Directory objects that will be used by the clustering service and by Microsoft SQL Server.

Create two computer accounts. The first will be for Windows Server Failover Cluster object and the second will be the SQL AlwaysOn Availability Group object. This guide uses the following resources:

WS\WSNOCMSSQL00$   Windows Server Failover Cluster Instance
WS\WSNOCMSSQL01$   Microsoft SQL AlwaysOn AG Cluster
WS\WSNOCMSSQL02$   Microsoft SQL Server
WS\WSNOCMSSQL03$   Microsoft SQL Server

Note: Corresponding DNS records must exist for all four objects. In a healthy Active Directory replicated DNS zone, these forward and reverse records will be created and maintained for you automatically.

Next, assign the needed security permissions to each object. This guide assumes that you will not be running the installation as a Domain Administrator, or as any other user with global privileges in Active Directory.

The user that will be performing the server configuration must be granted Full Control to the WSFC Cluster and SQL AlwaysOn AG computer accounts.The WSFC Cluster computer account must be granted Full Control to the SQL AlwaysOn AG computer account.

WSNOCMSSQL00$, the Failover Cluster Instance. WSNOCMSSQL01$, the AlwaysOn Cluster object. WSNOCMSSQL01$, the AlwaysOn Cluster object.

Group Managed Service Account Object

Pre-create the Group Managed Service Account that will be used for running the SQL Server process on each node. All nodes in the same cluster must use the same Group Managed Service Account for Kerberos Authentication to succeed.

See my other guide for an example to create group Managed Service Accounts

After you have created the Group Managed Service Account, entitle the Computer Accounts for the two MS SQL Servers to it, by adding the computer objects as members to the Security Group tied to your new Group Managed Service Account.

This guide uses the following resources:

WS\msa.mssql00$               Managed Service Account
WS\GMSA-MSSQL-WSNOCMSSQL00    Security Group

Service Principal Names

Finally, assign the required Service Principal Names to the Group Managed Service Account for later use. The Service Principal Names should associate ‘MSSQLSvc’ with the FQDN for your AlwaysOn AG cluster account as well as the individual computer accounts participating in the cluster.

In this scenario, we need to assign Service Principal Names for our SQL Availability Group computer account (wsnocmssql01) as well as each cluster node (wsnocmssql02 and wsnocmssql03) to our group Managed Service Account msa.mssql00.

setspn -A MSSQLSvc/wsnocmssql01.lan.wolfspirit.net WS\msa.mssql00$
setspn -A MSSQLSvc/wsnocmssql01.lan.wolfspirit.net:1433 WS\msa.mssql00$
setspn -A MSSQLSvc/wsnocmssql02.lan.wolfspirit.net WS\msa.mssql00$
setspn -A MSSQLSvc/wsnocmssql02.lan.wolfspirit.net:1433 WS\msa.mssql00$
setspn -A MSSQLSvc/wsnocmssql03.lan.wolfspirit.net WS\msa.mssql00$
setspn -A MSSQLSvc/wsnocmssql03.lan.wolfspirit.net:1433 WS\msa.mssql00$

Here’s an image of the result as viewed through the Managed Service Account properties.

Configure the SQL Server operating system

The underlying operating system that will be running the MS SQL Server needs some checks before we proceed.

Local Firewall

Ensure the required ports are open in the firewall. By default, you must open ports 1433, 1434, and 5022 in the local firewall for access to the database as well as AlwaysOn replication between nodes. Port 5022 only needs to be reachable from other MS SQL Servers in the cluster and should not be accessible to clients connecting to the database..

Local Security Policy

Ensure the Local Security Policy “Deny access to this computer from the network” does not contain entries that would deny the local ‘CLIUSR’ account, including any groups that the account may be a member of. By default no such entries exist and the policy setting is empty, but some are commonly added if the environment is built to be compliant with DoD STIGs or other security baselines. If the ‘CLIUSR’ account doesn’t exist, it will be created later by the Failover Clustering wizard automatically.

Network Adapters

(Recommended) Configure a secondary network adapter for the failover subnet. The failover subnet does not need to be routable and the servers should not specify a gateway on the secondary adapter.

Pages: 1 2