<

SQL Server security: The service account

Published on
14,632 Points
2,932 Views
7 Endorsements
Last Modified:
Microsoft SQL Server database engine runs as a Windows service so it will need to run under the security context of a Windows account. Choosing the right account is the first step to set a good security layer for SQL Server.
 
As with any service or application it will only need the strictly necessary privileges -- not less and not more. The SQL Server service only needs to have rights to the directories where it is storing data, log files, backups, and some system permissions.
Type of accounts that may be used as SQL Server service account:
  1. Built-in accounts - Accounts managed by the Service Control Manager (SCM):
    • Local System - Local Windows System account that has administrative rights on the computer and can access network resources if the machine has been granted the necessary permissions on the Active Directory (AD).
    • Network Service - Same as Local System but with limited administrative rights on the computer.
  2. Domain account - Service account created in the Active Directory with the single purpose of being used by a service. To be a service account this account needs to have the Log on as a Service right.
  3. Managed Service account - An Active Directory account that is tied to a specific computer. The password is managed automatically by Active Directory and will be changed regularly without stopping the service. This account can only be used for services and it cannot be used for log on. Also, it cannot be used in a MSCS SQL Server cluster since the service account must be used on several cluster nodes (this account is tied to a specific computer).
  4. Virtual Service account - A local account that requires no password management and can access the network with a computer identity. It cannot be created or deleted manually. As soon as a service is installed on a machine, it is available and has the same name as the service. This account cannot be used for SQL Server Failover Cluster Instance because the virtual account would not have the same SID on each node of the cluster.
 
Avoid using built-in accounts since they have more privileges than those ones that are needed by the SQL Server service and because these accounts are usually used by other services, so they can take control of the SQL Server instance since they will have administrative rights.

If you are running SQL Server 2012 or superior version in a stand-alone machine then use a Managed Service account (MSA) or a Virtual Service account (VSA) to simplify service management, depending on whether resources external to the SQL Server computer are needed (use MSA) or not (use VSA).

For SQL Server Failover Cluster Instances you must always use a Domain account. When using a domain account as a SQL Server service account, make sure it does not have a password expiration policy set  and does not have the need to change password in the first logon checked.

Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.
7
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free