<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL Server security: The service account

Published on
14,303 Points
2,603 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

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month