Standalone Managed Service Accounts, introduced in Windows Server 2008 R2, are managed domain accounts that provide automatic password management and simplified SPN management, including delegation of management to other administrators. Group Managed Service Accounts (gMSAs), introduced in Windows Server 2012, provide the same functionality within the domain but also extend that functionality over multiple servers.
Best practices for account management in SQL Server include the following requirements:
- To prevent the compromise of all services using the same service account, each service should be using a different service account.
- Each service account should have its passwords managed in accordance with domain account policies (changing every 90 days for example).
While working with numerous clients across many diverse industries, we frequently encounter shortcuts to account and password management within SQL Server. Instead of following best practices, it is common for the domain service accounts and their passwords to be known by multiple individuals, creating a security vulnerability. Often, a single domain account is used as the service account for all SQL Server instances, and, worse, shared by the applications that consume data. Some administrators relax password management policies for service accounts; passwords are refreshed every 12 months rather than every 90 days, for instance.
Group MSA’s can help address these issues. Password management is automated within Active Directory. gMSA Passwords are complex, cryptographically random and 240 bytes long. By default, Active Directory updates gMSA passwords every 30 days. gMSAs cannot be used interactively by a user to logon, nor can they be locked out. There is no requirement to restart the SQL Server database engine service following a service account password reset.
Many blogs have been written which detail the creation of gMSAs in Active Directory and the implementation of gMSAs on new installations of SQL Server. This post is designed for those administering Microsoft SQL Server databases, with an emphasis on how to implement gMSAs on existing SQL Server installations. This example was implemented on two servers participating in a Windows Server Failover Cluster (WSFC) with an AlwaysOn High Availability Group.
Implementing gMSAs on SQL Server Services
There are potentially three steps involved in the implementation of gMSA's on a SQL Server service:
- Add the existing gMSA to the server
- Enable the gMSA for SQL Server services that control AlwaysOn Availability Groups
- Updating the service to use the gMSA
Steps 1-3 are required if the gMSA is going to be used as the account for the SQL Server database engine service that controls an AlwaysOn Availability Group. If, for example, the gMSA is being used as the service account for the SQL Server Agent, then step 2 is not required.
Add The Existing gMSA to The Server
Connect to the server using Microsoft SQL Server Management Studio (SSMS). Expand the server, Security, and Logins folders.
Right click on Logins and select New Login
Click Search. Click on the Locations button and select Entire Directory. This organization has a large and complex Active Directory structure.
Click Object Types.
Check the box to include service accounts and click OK. Paste the gMSA into the bottom box of the Select User, Service Account or Group window. Click Check Names. When the account is found, the name becomes underlined. Click OK. The login name will appear in the following format: DOMAIN_NAME\gMSA_Name$
Enable the gMSA for SQL Server services that control AlwaysOn Availability Groups
When configuration gMSAs for the SQL database engine service of a server that participates in an AlwaysOn Availability Group, grant the connect permission on the HADR endpoint. Otherwise, the following instructions are not required.
Click on Securables.
Click on Search. In the Add Objects box, select the radio button for All objects of the types…
Click OK. Check the box for Endpoints and click OK.
In the Securables pane, select the Hadr_endpoint. In the Permissions for Hadr_endpoint pane, check the box to Grant Connect.
Click OK to add the gMSA to the SQL Server.
Updating Existing SQL Services to Use gMSAs
These instructions can be used to update any SQL Server service (e.g. database engine service, SQL Server Agent) to use a gMSA. For servers that participate in a Windows Server Failover Cluster (WSFC) and maintain a replica of a common AlwaysOn Availability Group, perform the account update on each server.
Open SQL Server Configuration Manager and click on SQL Server Services in the left pane. Double click on SQL Server (MSSQLSERVER) – or right click on it and select Properties.
In the properties window, click Browse.
As outlined previously, search and find the gMSA on your domain.
Leave the password field blank - the password will be provided through Active Directory. Click OK. A message will appear, warning that this action will cause the service to be restarted. Click Yes.
In SQL Server Management Studio, verify that all of the databases participating in an Availability Group are synchronized. If the SQL Database Engine service was switched to use the gMSA on the primary replica, you may notice that it has failed over to become the secondary replica (and the secondary has become the primary). Perform the same steps above on the server hosting the secondary replica. Test the failover of one replica to another; make sure that all of the databases remain in a synchronized state.