SQL 2012 Failover Cluster Pt. 5: Service Accounts

This is Part 5 of the SQL 2012 failover clustering series where we setup two service accounts. One is for the database engine and the other is for the SQL agent. In order for Kerberos to work properly, and for the cluster to work at all, they must be Active Directory based accounts. With service accounts less is more. What do I mean? The less privileges the accounts have the more secure you are. So these accounts won’t be given any special rights, including local administrator.

Do I hear you asking about MSAs and gMSAs? MSA (managed service account) were featured in WS2008 R2 and gMSAs (group managed service account) are new to Windows Server 2012. Due to complications with clusters and Kerberos, I don’t use MSAs or gMSAs for failover clusters. For AlwaysOn Availability Groups I use a ‘regular’ service account for the database engine and a gMSA for the SQL agent. But we aren’t doing an AlwaysOn cluster here, so no managed service accounts are needed.

Blog Series

SQL 2012 Failover Cluster Pt. 1: Introduction
SQL 2012 Failover Cluster Pt. 2: VM Deployment
SQL 2012 Failover Cluster Pt. 3: iSCSI Configuration
SQL 2012 Failover Cluster Pt. 4: Cluster Creation
SQL 2012 Failover Cluster Pt. 5: Service Accounts
SQL 2012 Failover Cluster Pt. 6: Node A SQL Install 
SQL 2012 Failover Cluster Pt. 7: Node B SQL Install
SQL 2012 Failover Cluster Pt. 8: Windows Firewall
SQL 2012 Failover Cluster Pt. 9: TempDB
SQL 2012 Failover Cluster Pt. 10: Email & RAM
SQL 2012 Failover Cluster Pt. 11: Jobs n More
SQL 2012 Failover Cluster Pt. 12: Kerberos n SSL

Service Accounts

1. As mentioned in the intro I always create two service account for a SQL server instance. One for the database engine and another for the SQL agent. These just need to be regular Active Directory user accounts. I’ll call mine SVC-D001SQL03-DBE and SVC-D001SQL03-AG.

2. 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, so consult a professional DBA. 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 from my reading, 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.

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 2012 Failover Cluster series. We created two service accounts, gave them the proper user rights, created a security group, and then got a cup of coffee. Next up in Part 6 we finally get to start installing the SQL server services on a cluster node A.