SQL Service Accounts

Previous Software Install Account	SQL Server Proxy Accounts Next

This section contains the following:

• Service Account Overview
• Managed Service Accounts
  • Prepare for gMSA Accounts
  • Plan gMSA Group Requirements
  • Creating gMSA Accounts
  • Post-Create Actions
• Legacy Service Accounts

Service Account Overview

SQL Server can be installed using either a domain or a local Service Account. The Best Practice for which type of account depends on how SQL Server will be used.

A Service Account is no more than a normal account, but one that is used only to provide the credentials for a Windows Service. The security applied to each service account can be tailored to provide the minimum rights required for the service, and the account should definitely not be given local Administrator authority. SQL FineBuild will automatically apply the rights needed for each SQL Server service account.

SQL FineBuild can use either local or domain accounts for services. For Windows 2012 and above with SQL Server 2012 and above, FineBuild can also use Group Managed Service Accounts.

The parameters used for Service Accounts within SQL FineBuild are listed below:

Parameter	Default Value	Note	Description
/AGTSVCAccount:	/SQLSVCAccount: value		SQL Agent Service Account
/AGTSVCPassword:	/SQLSVCPassword: value		Password for SQL Agent Service Account
/ASSVCAccount:	/SQLSVCAccount: value		Analysis Services Service Account
/ASSVCPassword:	/SQLSVCPassword: value		Password for Analysis Services Service Account
/BrowserSVCAccount:	NETWORK SERVICE	A	SQL Browser Service Account
/BrowserSVCPassword:	(none)		Password for SQL Browser Service Account
/FTSVCAccount:	NETWORK SERVICE	A	SQL Full Text Indexing Service Account
/FTSVCPassword:	(none)		Password for SQL Full Text Indexing Service Account
/ISSVCAccount:	/SQLSVCAccount: value		Integration Services Service Account
/ISSVCPassword:	/SQLSVCPassword: value		Password for Integration Services Service Account
/RSSVCAccount:	/SQLSVCAccount: value		Reporting Services Service Account
/RSSVCPassword:	/SQLSVCPassword: value		Password for Reporting Services Service Account
/SQLSVCAccount:	NETWORK SERVICE	A	SQL Server Service Account
/SQLSVCPassword:	(none)		Password for SQL Server Service Account

Note A: For SQL Server 2012 or above installed on Windows 2008 R2 or above, a Local Virtual Account will be used.

Top

---

Managed Service Accounts

This covers the optimal type of account to use for SQL Server 2012 and above when installed on Windows 2012 and above.

The optimum type of managed Service Account is shown below:

Use	Location	Account Type
No remote queries	Workgroup	Local Virtual Account
Remote queries	Workgroup	Local Virtual Account
No remote queries	Domain	Local Virtual Account
Remote queries	Domain	Domain Group Managed Service Account
SQL Server Cluster	Domain	Domain Group Managed Service Account

The Microsoft documentation about Virtual Accounts and Group Managed Service Accounts (gMSA) is fragmented across a number of pages:

• Service Accounts Step-by-Step Guide

  Ignore information about Managed Service Accounts, it applies only to Windows 2008 R2

• Windows 2012 Group Managed Service Accounts

  Ingore the claim that gMSA accounts do not work with SQL 2012. They do provided the account ends with a $

• Configure Windows Service Accounts and Permissions.

Where a gMSA account is used then all Services of the same type can use the same account. For example, all SQL DB services can share the same account, and all Analysis services can share a different account. It is safe to do this because the password for a gMSA account is not human-readable so there is no risk of all services being compromised as there is no known way to breach the security of a gMSA account.

If you need to get an account created, follow your site standards to do this. Typically, the process takes about 2 days to complete.

A Local Virtual Account and a Group Managed Service Account (gMSA) do not have a human-readable password. If Local Virtual Accounts are used, FineBuild will create these automatically, but Group Managed Service Accounts must be created using New-ADServiceAccount before running SQL FineBuild.

Prepare for gMSA Accounts

There are some pre-requisites that must be completed before a gMSA account can be created:

• The Domain Functional Level must be at least Windows 2012

• The root encryption key must be created. Use the command below then wait 24 hours for the Root Key to become operative:

  Add-KDSRootKey -EffectiveImmediately

Plan gMSA Group Requirements

In order to use a gMSA account, it must have a Windows Group assigned in the PrincipalsAllowedToRetrieveManagedPassword parameter of the New-ADServiceAccount. It is important to plan a strategy for these group names.

The recommended approach is to have a dedicated group for each SQL Server environment (Development, Acceptance, Production, etc) in your environment, and to use that group for all gMSA accounts intended for that environment. If your environment has specific security policies for a set of servers you may want to use a separate group for each set of servers, but most organisations are likely to find that a single group for each environment is sufficient. All Primary site and Disaster Recovery site installations for a given set of servers should normally use the same group.

All the gMSA acounts and servers that will use a given gMSA account must be a member of the group specified for the account. SQL FineBuild will automatically assign any memberships that are missing.

If the gMSA account is to be used for a cluster build, also see Delegation of Control as this will need to be set up manually for the group.

Creating gMSA Accounts

The following syntax for New-ADServiceAccount is recommended as this will allow the account to exploit Kerberos, making them the most secure way possible in Windows to run a Service. The following substitutions should be made:

Parameter	Value
accountname	Name of the gMSA account. Must be 15 characters or less and prior to SQL 2016 it must end with $
fdqndomain	The fully qualified name of your domain
groupname	Name of the group used to host the gMSA accounts and the servers that will use tham
accountdescription	Short description of the account

New-ADServiceAccount -Name accountname -DNSHostName accountname.fdqndomain -PrincipalsAllowedToRetrieveManagedPassword groupname -TrustedForDelegation $true -Description 'accountdescription'

Example command:

New-ADServiceAccount -Name ServGB_SQLDB_1$ -DNSHostName ServGB_SQLDB_1$.ROOT.local -PrincipalsAllowedToRetrieveManagedPassword ServGB_SQL -TrustedForDelegation $true -Description 'SQL DB'

Post-Create Actions

After you have created the gMSA accounts there are post-requisites that must be completed. SQL FineBuild will do these tasks automatically or you can perform them manually:

• Add the gMSA accounts to the Windows Group specified in -PrincipalsAllowedToRetrieveManagedPassword

• Add the Computer Account for each server that will use the gMSA account to the Windows Group specified in -PrincipalsAllowedToRetrieveManagedPassword

• Reboot the servers in the group, because the group membership information is only read by the server at boot time

The advice seen on some pages that Install-ADServiceAccount must be run on the server that will use the gMSA is obsolete as it applies only to Windows 2008 R2 simple managed service accounts and is not relevant for a gMSA.

Top

---

Legacy Service Accounts

This covers the optimal type of account to use for SQL Server 2008 R2 and below, or any version of SQL Server running on Windows 2008 R2 or below.

The optimum type of legacy Service Account is shown below:

Use	Location	Account Type
No remote queries	Workgroup	Local account or local Network Service
Remote queries	Workgroup	Local Network Service
No remote queries	Domain	Domain account or local Network Service
Remote queries	Domain	Domain Account
SQL Server Cluster	Domain	Domain Account

If Local Accounts or Domain Accounts are used, each SQL service should use a separate service account. This is to avoid the risk of all services being compromised if the security of a single service account is breached. Although it is not best practice, SQL FineBuild does support all services being installed using a single account if required.

If you need to get an account created, follow your site standards to do this. Typically, the process takes about 2 days to complete. For accounts that have passwords, the supplied password should immediately be changed and recorded in the DBA Password Store. The password must comply with site standards for service accounts, and it is recommended that a strong password of at least 30 characters is used.

Copyright FineBuild Team © 2013 - 2021. License and Acknowledgements

Previous Software Install Account	Top	SQL Server Proxy Accounts Next