SQL Service Accounts

Previous DBA Role Accounts	Software Install Account Next

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.

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, but it is recommended that a strong password of at least 30 characters is used.

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 does not apply to Windows 2012

• 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.

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.

A Local Virtual Account and a Group Managed Service Account (gMSA) do not have a 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.

gMSA Pre-Requisites

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 have been created using

Add-KDSRootKey -EffectiveImmediately

• The computer accounts of the servers that will use a given set of gMSA accounts must be added to a Windows Group created for that specific purpose

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 server and gMSA accounts
accountdescription	Short description of account usage

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

Example command:

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

After you have created the gMSA accounts there are post-requisites that must be completed:

• Add the gMSA accounts to the same Windows Group used to hold the computer accounts

• 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 needed for a gMSA.

Copyright FineBuild Team © 2013 - 2018. License and Acknowledgements

Previous DBA Role Accounts	Top	Software Install Account Next