SQL 2014 Always-on AG Pt. 3: Service Accounts

This is Part 3 of the SQL 2014 Always-On Availability group series where we setup two service accounts and a security group. One account is for the database engine and the other is for the SQL agent. In order for Kerberos to work properly the database engine account must be Active Directory based. We will also be observing the rule of least privilege. The less privileges the accounts have the more secure you are. So these accounts won’t be given local administrator rights. You don’t want your SQL service running with admin rights!

New to my SQL install series is the usage of a gMSAs (group managed service account) which are new to Windows Server 2012.  For AlwaysOn Availability Groups I use a ‘regular’ service account for the database engine and a gMSA for the SQL agent. Using regular service accounts would work for the SQL Agent, but I like using new OS features. Plus this means you have one less password to change since the gMSA automatically updates the password on a periodic basis. For those of you not familiar with gMSAs, they are special type of account which Windows automatically manages the password for and changes on a periodic basis. You need at least one 2012 domain controller, and use 2012 or 2012 R2 member servers.

Blog Series

SQL 2014 Always-on AG Pt. 1: Introduction
SQL 2014 Always-on AG Pt. 2: VM Deployment
SQL 2014 Always-on AG Pt. 3.: Service Accounts
SQL 2014 Always-On AG Pt 4.: Node A Install
SQL 2014 Always-on AG Pt. 5: Unattended Node B
SQL 2014 Always-on AG Pt. 6: Cluster Configuration
SQL 2014 Always-on AG Pt. 7: TempDB
SQL 2014 Always-on AG Pt. 8: Max Mem & Email
SQL 2014 Always-on AG Pt. 9: SQL Maintenance
SQL 2014 Always-On AG Pt. 10: AAG Setup
SQL 2014 Always-On AG Pt. 11: File Share Witness

Database Engine Account

1. Using ADUC create a service account using your naming convention. I’ll call mine SVC-D002SQL03-DBE. Set a complex password and make sure it does not expire.

2. Security is always at the top of the list, so let’s enable AES encryption for Kerberos. I’d also set an account description, so that two years from now you know what the service account is for.

Agent gMSA Configuration

1. On your SQL server enter the following PowerShell command:

Add-WindowsFeature -Name “RSAT-AD-PowerShell” –IncludeAllSubFeature

2. Download the free Managed Service Account GUI from here. Install it using all defaults.

3. If you have never used a gMSA before, enter the following PowerShell command on your 2012 domain controller:

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10))

3. Create a new service account. The account name MUST be equal to or less than 14 characters long. Select “Group MSA.”

4. When prompted to assign the new service account, say Yes. Enter the two new server names of what will be your SQL AlwaysOn Group.

User Rights

1. In order to support large page tables and instant database initialization we need to configure a couple of user rights on each node configured in the Local Security Policy. Add to the Lock Pages in Memory and Perform Volume maintenance tasks user rights the database engine service account. If you want SQL to write to the windows security log, then also add the DBE account to Generate Security Audits and Manage auditing and security log.

The lock pages in memory option is somewhat controversial. Some people to say only use it for VMs with 16GB+ RAM, others say don’t use it at all. Later on you will see that I configure the SQL Max memory parameter, which greatly minimizes the potential problems from SQL page locking. The trend seems to be you SHOULD configure it (along with SQL max memory), thus my recommendation to do so. The combination of large pages and locking pages in memory prevents the SQL server buffer pool from being paged out by Windows. This also inhibits the balloon driver, which we don’t want activated since it can tank SQL performance.

You can go ahead and configure this user right now, since large page tables have to be enabled in SQL via a trace flag.

 

3. Reboot both nodes after you configure the user rights, or they will not take effect.

4. Do NOT, I repeat, do NOT add either service account to the local administrators group on the SQL server. This is a security risk, and the SQL service should not be elevated. The SQL server will make the needed permission changes.

5. Lastly, we will create an AD security group that will be assigned SQL administrator rights. This is far better than assigning a specific user account. I called my group SQL_Sysadmin. Add your administrator account to this group.

Summary

This is a very short and simple installment in the SQL 2014 AlwaysOn Availability group series. We created two service accounts, gave them the proper user rights, and created a security group.  Next up in Part 4 we finally get to start installing the SQL server services on the first node.