SQL Server Failover Cluster with Managed Service Account (MSA) – Step by step walk-through and troubleshooting until successful

I have built a Domain controller (SHERBAZ.COM) on Windows 2012 Datacenter edition evaluation version with DNS role enabled. Also I use a trial version Starwind virtual SAN to provision shared storage for the cluster. Two windows 2012 standard evaluation edition is also installed to setup failover clustering. Setting up the environment is beyond the scope of this session. The windows failover cluster is also considered pre-built before this demo.

Here I am showing a demo on how to setup a SQL Server failover cluster using Managed Service Account instead of normal service accounts. Managed service account(MSA) will have it’s password automatically managed by the Active directory. Admins and users will have no control on the passwords and hence more secure.

Here are the permissions granted to different accounts in short.

DBA Installer account (dba@sherbaz.com)
- Added to windows Local Administrator group on Node1 and Node2
- Read/write permission on SQL Server Cluster Virtual Name Computer object in AD (sqlclus)
Active Directory Managed Service Account (sqlclumsa$@sherbaz.com)
- No access granted anywhere
Windows cluster computer object account (winclus1$@sherbaz.com)
- Full control permission on SQL Server Cluster Virtual Name Computer object in AD (sqlclus)
- Full control permission on SQL Server Cluster Virtual Name DNS record
SQL Server Failover cluster computer object in AD (sqlclus)
- Permission to retrieve password for MSA (sqlclumsa$) from AD
Cluster nodes 1 and 2
- Permission to retrieve password for MSA (sqlclumsa$) from AD

Continue reading for details steps…

1. SQL Virtual Network Name: Lets start by adding DNS record for the new SQL Server instance virtual network name sqlclus.sherbaz.com

2. Create AD computer object for SQL virtual network name: Open Start > Run > dsa.msc on DC.

Right click on Computers folder under your domain (SHERBAZ.COM) and select New > Computer

2.1. Give read/write permissions to DBA installer user account on the SQL computer object sqlclus:

Right click the newly created computer object in **AD Administrative Center** and select **Properties**

Add **dba@sherbaz.com** and grant Read/Write permission and click OK

2.2 Give Full Control permission to Windows Cluster Computer object account “winclus1” on SQL object “sqlclus“

Click **Add**, click **Object Types** button to include **Computers**

Search for windows cluster computer object. For me its **winclus1** here.

Grant Full control access to **winclus1**

If this permission is not granted, we would get below error in the end of SQL Server failover cluster installation wizard.

If Full control permission is not granted to Windows cluster computer object, we would get the error saying “The cluster resource could not be brought online due to an error bring the dependency resource ‘<Network name>’ online”. And in the cluster event logs, you will see below.

Cluster network name resource ‘SQL Network Name (sqlclus)’ failed to create its associated computer object in domain ‘sherbaz.com’ for the following reason: Resource online.
The associated error code is: -1073741790
Please work with your domain administrator to ensure that:
The cluster identity ‘WINCLUS1$’ can create computer objects. By default all computer objects are created in the ‘Computers’ container; consult the domain administrator if this location has been changed.
The quota for computer objects has not been reached.
If there is an existing computer object, verify the Cluster Identity ‘WINCLUS1$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

3. Create MSA: On Domain control server (DC), open powershell as “Run as Administrator” and execute below to create Managed Service Account named sqlclumsa. I thought DNSHostName can be SQL Cluster Virtual Network Name logically and gave it. It didn’t give any problem.

Import-Module ActiveDirectory
New-ADServiceAccount -Name sqlclumsa -Enabled $true -Description "SQL Cluster MSA" -DisplayName "SQL MSA" -DNSHostName sqlclus

Creating MSA **sqlclumsa** in powershell

Open Active Directory Administrative Center and verify **sqlclumsa** is created

3.1. Grant permissions to MSA: Grant permissions to all computer objects on the newly created MSA.
> node1 – node1$ – First node of failover cluster
> node2 – node2$ – Second node of failover cluster
> winclus1 – winclus1$ – Windows failover cluster virtual network name computer object in AD
> sqlclus – sqlclus$ – SQL virtual network name computer object in AD

Set-ADServiceAccount -Identity sqlclumsa -PrincipalsAllowedToRetrieveManagedPassword node1$ node2$ winclus1$ sqlclus$

If you forget to create SQL computer object before this step, you will see below error

“Cannot find an object with identity: ‘sqlclus$’ ” I forgot to create computer object for SQL virtual network name at first.

4. Add Storage: Provision a shared storage for both nodes node1 and node2 for SQL installation. Here I used trial version Starwind virtual SAN software.

Any name for the virtual disk. This is invisible to our SQL cluster

Continue the wizard with all default options

4.1 Add storage to individual nodes: Login to any node and Start > Run > Compmgmt.msc to open Computer Management. Expand Storage and select Disk management. This has to be done only on one node since the disk is same on both and shared.

Right click on the newly seen Unknown Offline drive

Right click again and click **Intialize Disk**

Right click again and select **New Simple Volume.**

Continue the wizard with default options except this. We need to select 64K allocation unit size for SQL Drives

4.2. Add Disk to Cluster: Open Start > Run > cluadmin.msc to open Failover Cluster Administrator

Expand storage sub tree and select **Disks**. Click **Add Disk** on right side of screen

Select the automatically identified Cluster Disk

Cluster Disk 1 is added successfully as Available Storage

5. Install MSA on each node: Through powershell, the MSA sqlclumsa we created has to be installed on both the nodes for successful authentication.

Right click powershell icon and select **Run as Administator**

Execute below command to install windows feature RSAT-AD-PowerShell

Install-WindowsFeature RSAT-AD-PowerShell

Test if the node has access to MSA with below command

Test-ADServiceAccount -Identity sqlclumsa

We get this error if the node do not have permission

If the node has access, the test would return **True**. Now you can install the service account

Execute below command to install service account.

Install-ADServiceAccount sqlclumsa

6. SQL Server Installation: Mount the SQL Server Installation DVD ISO and fire Setup.exe. As a pre-requisite, ensure the DBA installer account has local administrator rights on both the nodes.

Add SHERBAZ\dba under Administrators group on both nodes.

Click **New SQL Server Failover Cluster installation**

Continue normally until this stage. Enter the SQL instance virtual network name **sqlclus**

Select a Cluster Group Name from the Drop down

Select the **Cluster Disk 1** we added through cluster administrator a while ago

Enter virtual network IP address **192.168.1.25** for SQL virtual network name

Type sqlclumsa(MSA) and click Check Names and OK

We could see that the password field got hidden automatically

Some basics.. Don’t forget to add your current login as administrator. But no worries, we could also add it later by restarting SQL service in limited single user mode

Final summary where we could see the MSA **SHERBAZ\sqlclumsa$** used for both SQL engine an SQL agent.

SQL failover cluster installation completed successfully

6.1 Add second node: Add node 2 to SQL cluster we installed above. Mount the install DVD ISO on node 2 and fire Setup.exe

Select **Add node to a SQL Server failover cluster**

Continue wizard as usual. Select the instance we installed with MSA on node1

Service accounts are automatically selected and greyed out

After the installation review the cluster events for any errors. If you see below error in cluster events saying “Cluster network name resource ‘SQL Network Name (sqlclus)’ failed registration of one or more associated DNS names(s) for following reason: DNS operation refused”

After installation. If you see this Error in Cluster events, we will have to grant permission on **sqlclus.sherbaz.com** DNS record to windows cluster computer object account **SHERBAZ\winclus1$**

Right click **sqlclus.sherbaz.com** DNS record and select **Properties**

Under **Security** tab click Add, include Object Type **Computers** and Check for name **WINCLUS1** (The windows failover cluster computer object name in Active Directory)

Grant Full control to **SHERBAZ\winclus1$**

Error will be gone. You can continue testing failover to both nodes

We are done with the SQL Server Failover cluster installation using an Active Directory Managed Service Account

References

https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/getting-started-with-group-managed-service-accounts
http://www.rebeladmin.com/2018/02/step-step-guide-work-group-managed-service-accounts-gmsa-powershell-guide/
https://medium.com/tech-jobs-academy/creating-and-associating-a-group-managed-service-account-19843050a6a6
https://www.mssqltips.com/sqlservertip/5334/using-managed-service-accounts-with-sql-server/
https://techcommunity.microsoft.com/t5/ask-the-directory-services-team/managed-service-accounts-understanding-implementing-best/ba-p/397009

S	M	T	W	T	F	S
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

SQL Server Failover Cluster with Managed Service Account (MSA) – Step by step walk-through and troubleshooting until successful

References

Related

Published by Sherbaz

Leave a Reply Cancel reply

References

Share this:

Related

Published by Sherbaz

Leave a Reply Cancel reply