<

SQL Server security: The service account

Published on
13,956 Points
2,256 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
Comment
2 Comments
 

Expert Comment

by:AnthonyMCSE
I have a medical software product that recommends/requires using the primary Domain Admin account when installing SQL 2008 or SQL 2012 on a Domain Controller.  Doesn't this seem like an odd requirement and unsafe?  Maybe not as unsafe as unsafe as using Local System?
0
 
LVL 55

Author Comment

by:Vitor Montalvão
Recommendation is to have a domain account exclusively for SQL Server service. Use an account that is being used for other applications will let those applications to have full access to your SQL Server instance and that's not a good idea.
0

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Join & Write a Comment

Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month