This is Part 3 of the SQL 2017 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!
The service account that most people are familiar with are a standard Active Directory user, with a complex password, that is used for a specific application such as SQL. Active Directory service accounts are required if you want to use Kerberos authentication. However, these standard service accounts have some disadvantages. ​The primary issue is the lack of automatic password management. An administrator manually sets the password, and should periodically change it for security reasons. Restarting the SQL service is needed after the password is changed.
A few years ago Microsoft introduced "Group Managed Service Accounts". The gMSA accounts are a unique object type in AD that has automated password management and the account can be used on more than one server. You can read a bit more about gMSA accounts here. Particularly noteworthy is that the password is automatically changed every 30 days, the accounts cannot be used for interactive logins, and the account can't be locked out.
There's a little extra configuration up front for a gMSA, but then you no longer have to worry about password changes for that application. This SQL 2017 installation series will use gMSAs so you can see how to configure them. 'Standard' AD based service accounts will work as well, but will have manual password management.
​Domain Controller Configuration
​In order to use a gMSA you need at least one domain controller that is running Windows Server 2012. If you have never used a gMSA then it's likely you will need run a command to enable gMSA accounts within the domain. This only needs to be run once, so skip if your domain has already been configured. Open an elevated PowerShell on a domain controller and run this command:
Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10))
It may take up to 10 hours for the change to take effect. So if you have issues creating a gMSA, wait and try again.
​gMSA Account Creation
​There are two primary ways to create a gMSA account. The first and most cumbersome is using various PowerShell commands. It's very tedious and error prone. Or, the quick and easy method via a free GUI tool. ​You can download the free tool here​. Install the tool on your first SQL server using all defaults.
I recommend using two gMSAs for each SQL AAG instance. One is for the database engine and the other is for the agent service. Use a logical naming standard for the accounts, and make sure the account name is 14 or less characters.
1. Launch the free gMSA tool and and click on New. Enter the appropriate 14 character or less account name for the agent service account. Add a useful description. Select the appropriate AD container.
​2. When you are prompted to assign computers to the gMSA click Yes. Add the two SQL servers to the gMSA, validating each one. After you add them and click OK​ and the home page of the tool should now show one gMSA and the assigned computers.
​3. Repeat this procedure for the database engine gMSA account. I called this account MSA-SQL01-D. The GUI tool should now show two gMSA accounts on the home page.
SQL Server Preparation
1. On both of the SQL servers run the following elevated Powershell command to enable the usage of a gMSA.
Enable-WindowsOptionalFeature -FeatureName ActiveDirectory-Powershell -Online –All
​2. On both of the SQL servers run the following elevated Powershell command to 'install' the gMSA account on the server, using your service account names. Run for both of the gMSAs (database engine and agent service).
Install-AdServiceAccount MSA-SQL01-A​
Install-AdServiceAccount MSA-SQL01-D
​​3. You should now test the service accounts via Powershell to make sure they work. The command should return 'true' for both accounts.
Test-ADServiceAccount -Identity MSA-SQL01-A
Test-ADServiceAccount -Identity MSA-SQL01-D
​User Rights
​In order to support large page tables and instant database initialization we need to configure a couple of user rights in the Local Security Policy of each node. Add to the Lock Pages in Memory and Perform Volume maintenance tasks user rights the database engine service account. ​Do this on both SQL VMs, and reboot.
Do NOT add either of the SQL service accounts to the local administrators group. This is very bad for security, and not required for proper SQL operation.
​SQL Administrator Group
​I recommend configuring an Active Directory security group that will contain all of the SQL administrators. This makes adding/removing of SQL administrators much easier. If you already have an appropriate security group in AD, use it. For this exercise I will use SQL-Admins. Add the appropriate users to this group before proceeding.
​Summary
​In this post we created two group managed service accounts (gMSA) that we will be using for SQL 2017. These accounts require a bit more up front work to configure, but have the advantage of never needing to change the password. If you do not wish to use gMSA accounts, feel free to use the 'standard' AD user object for the service accounts. This guide is based on gMSA accounts,
​Next up in Part 4, we will install SQL 2017 on the first node. We will capture this configuration, and use it as a template for an unattended installation of SQL on the second node.
​SQL 2017 Installation Series Index
​SQL 2017 Always-on AG Pt. 1: Introduction​
SQL 2017 Always-on AG Pt. 2: VM deployment
SQL 2017 Always-on AG Pt. 3: Service Accounts​
SQL 2017 Always-on AG Pt. 4: Node A SQL Install​
SQL 2017 Always-on AG Pt. 5: Node B SQL Install​
SQL 2017 Always-on AG Pt. 6: Cluster Configuration​
SQL 2017 Always-on AG Pt. 7: File Share Witness​
​SQL 2017 Always-on AG Pt. 8: ​AAG Setup
​​​​SQL 2017 Always-on AG Pt. 9: Kerberos (Coming)
​SQL 2017 Always-on AG Pt. 10: SSL Certificates (​Coming)
​SQL 2017 Always-on AG Pt. 11: Max Mem & Email Alerts (Coming)
SQL 2017 Always-on AG Pt. 12: Maintenance Jobs (Coming)
Hi Derek, Sanjay here. Hey, I just stopped by to say that I think that over the years, you’ve done a great job in sharing your knowledge for the benefit of everyone, and I for one appreciate that. Last week, I had penned a little blog post titled A Few Notables Names in Active Directory and you’re on the list. I appreciate all the effort you put in Derek, and I wish you well!
Thanks Sanjay!