SQL 2012 Failover Cluster Pt. 5: Service Accounts

9-22-2013 6-37-30 PMThis 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.

9-21-2013 4-04-45 PM

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.

9-21-2013 4-17-09 PM

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.

Print Friendly, PDF & Email

Related Posts

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors
aun Recent comment authors
  Subscribe  
newest oldest most voted
Notify of
aun
Guest
aun

we have two node cluster installed in the virtualized environment with RDM mapping in single esxi host, when we are trying to validated the cluster am getting error, its stating “validate scsi -3 persistent reservation failed” So what we have done is we added the data and log disk in the MSDTC resource, after that validation is success but am getting some of the warnings we skipped and installed the sql cluster. is ths correct way to configure and it will make any problem moving to prodcution Configuration details:- Quorum disk, network ip MSDTC Disk, Network ip Data Disk under… Read more »

Scroll to Top