SQL Server DBA

SQL Server and Windows Service Accounts


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:

serviceAccountsSetup

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s