SQL Server Service Account & Security Policy Update (SQLFCI-10)

SQL Server Service Account: SQL Server account and required permissions required while configuring SQL failover cluster, also will show how to enable perform column maintenance security policy using GP Policy for our SQL FCI VM’s.

SQL Server Service Account

First, we will create SQL Svc account and also SQL Agent Svc account and we will give appropriate permissions on all the SQL server FCI nodes.

Step 1: Go to your DC and open users and computers and click on users >> click on new >> Users.

Step 2: Give a name and specify how is used to login to VM.

Step 3: Give password, make sure for this account password policy is no applied as it is service account.

Step 4: Once created, you have to create new account for SQL Server Agent Svc account in the same way, as you can I have created one and the account looks like the highlighted in the below image.

Step 5: Now we have to give local admin permission on the VM’s (SQLA, SQLB & SQLABFCI), I will show for one VM and you have to repeat the same of your SQL FCI VM’s.

Open local users and groups from your windows search bar, as shown below

Step 6: Click on groups, under groups click on “Administrators” >> Click on Add >> Give the SQL svc account and SQL Agent Svc Account name >> select the account from the list which we created, and you can see in the below image.

Step 7: Once the account is added click on apply and hit finish!

Note: Repeat the same steps for other SQL FCI nodes which is SQLA, SQLB, SQLABFCI.

Perform Column Maintenance Security Policy

For more why to perform this maintenance security policy please check this doc: Perform volume maintenance tasks security policy

There are 2-ways to do this, the process follows the same but one is done through domain controller group policy and other is with local groups policy. In this case I am going with DC GP policy, once done I don’t have to do manually for all my SQL FCI VM’s, instead done using GP update.

Note: Repeat the same for all your SQL FCI nodes if you are not following Domain Controller GP policy.

Step 1: Open GP Edit from your windows search as shown below.

Perform Column Maintenance Security Policy

Step 2: Follow steps below as shown in the below image.

Step 3: Click on Perform Volume Maintenance Task and click on next step.

Step 4: Click on Add Users or Groups and add both SQL account which we just created in the above steps.

Step 5: As you can see, I have browsed the SQL Server Account.

Step 6: Once added and you can see this below accounts mine, then you can hit apply and OK!

Step 7: Now we have to restart the VM’s to take this changed effect, or you can do GP Update form COMMAND. This should be done for all your SQL machine which are going to be part of SQL Failover Cluster.