Each service in SQL Server is a service running on Windows under the hood and is associated with a specific user account, subject to the standard OS-level authentication and privileges. Management of these services and service accounts is an important aspect when installing and maintaining SQL Server. We have to ensure that each service has sufficient privileges to run smoothly, but best-practices would have us restrict these privileges to the bare-minimum.
SQL Server Default Service Accounts
An out-of-the-box installation of SQL Server will generally marry up the following service / service accounts:
|SQL Server Database Engine||NT Service\MSSQLSERVER|
|SQL Server Agent||NT AUTHORITY\System|
|SQL Server Analysis Services||NT Service\MSSQLServerOLAPService|
|SQL Server Integration Services||NT Service\MsDtsServer110|
|SQL Server Reporting Services||NT Service\ReportServer|
The service account that runs each of these services controls the resources, privileges and allowed actions for the services. For example, the default SQL Server Agent acount, NT AUTHORITY\System, is a built-in account with local administrator privileges, which gives full access to the local environment but not to remote systems. Similarly, the default service account for the database engine is fine for every-day query tasks and database management but has some limitations, for example: will not let you backup your databases outside of the default file directories.
In the real world, it is very uncommon to have stand-alone installations of SQL Server. More commonly, instances of SQL Server are shared networked services, support remote connections, integrate with network filesystems and domain or network users. Microsoft’s best-practices suggest using Managed Service Accounts (MSA) to configure database services. If you do use a MSA, this must be setup before installing SQL Server.
If you are going to use non-default service accounts for your SQL Server installation, you should consider the requirements for the environment and try to restrict privileges as much as possible. Generally, it is better to create a dedicated domain account for SQL Server Services, rather than tying these services to a specific user. By using a domain-level account, privileges and rights can be assigned via Active Directory and replicated across the network, removing the need for specific accounts on each host. It is not uncommon to see SQL Server running under a named-user’s account – however, you have to ask yourself what will happen to these services when this person leaves the company and is removed from Active Directory?
Configuring Service Accounts
Service accounts can be configured during installation. From a core install, commandline or scripted install you would include the /SQLSVCACCOUNT and /AGTSVCACCOUNT flags to configure the Database Engine and SQL Server Agent service accounts respectively:
~$ Setup.exe /qs /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<password>" /SQLSYSADMINACCOUNTS="<DomainName\UserName>" /AGTSVCACCOUNT="<DomainName\UserName>" /IACCEPTSQLSERVERLICENSETERMS
Or this can be done via the SQL Server Installation Center:
Using SQL Server Configuration Manager, you can modify these settings at any stage. Open the Configuration Manager, right click and edit the properties of the service you want to change.
This recently caught me out when I had to perform a backup to a networked storage device in my dev environment. We live and learn.