Previous DBA Role Accounts Software Install Account Next

SQL Service Accounts

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 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 @ SQL Browser Service Account
/BrowserSVCPassword: (none) Password for SQL Browser Service Account
/FTSVCAccount: NETWORK SERVICE @ 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 @ SQL Server Service Account
/SQLSVCPassword: (none) Password for SQL Server Service Account

@ 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: 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.

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
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 substtutions should be made:
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 - 2016. License and Acknowledgements
Previous DBA Role Accounts Top Software Install Account Next

Last edited Nov 11, 2016 at 10:29 AM by EdVassie, version 5