Sql server 2005 Security Best Practices Operational and Administrative Tasks

Service Account Selection and Management

• 
  Domain user that is not a Windows administrator
  
• 
  Local user that is not a Windows administrator
  
• 
  Network Service account
  
• 
  Local System account
  
• 
  Local user that is a Windows administrator
  
• 
  Domain user that is a Windows administrator
  

Using a local user or domain user that is not a Windows administrator is the best choice. If the server that is running SQL Server is part of a domain and must access domain resources such as file shares or uses linked server connections to other computers running SQL Server, a domain account is the best choice. If the server is not part of a domain (for example, a server running in the perimeter network (also known as the DMZ) in a Web application) or does not need to access domain resources, a local user that is not a Windows administrator is preferred.

Creating the user account that will be used as a SQL Server service account is easier in SQL Server 2005 than in previous versions. When SQL Server 2005 is installed, a Windows group is created for each SQL Server service, and the service account is placed in the appropriate group. To create a user that will serve as a SQL Server service account, simply create an "ordinary" account that is either a member of the Users group (non-domain user) or Domain Users group (domain user). During installation, the user is automatically placed in the SQL Server service group and the group is granted exactly the privileges that are needed.

If the service account needs additional privileges, the privilege should be granted to the appropriate Windows group, rather than granted directly to the service user account. This is consistent with the way access control lists are best managed in Windows in general. For example, the ability to use the SQL Server Instant File Initialization feature requires that the Perform Volume Maintenance Tasks user rights be set in the Group Policy Administration tool. This privilege should be granted to SQLServer2005MSSQLUser$MachineName$MSSQLSERVER group for the default instance of SQL Server on server "MachineName."

SQL Server service accounts should be changed only by using SQL Server Configuration Manager, or by using the equivalent functionality in the WMI APIs. Using Configuration Manager ensures that the new service account is placed in the appropriate Windows group, and is thus granted exactly the correct privileges to run the service. In addition, using SQL Server Configuration Manager also re-encrypts the service master key that is using the new account. For more information on the service master key, see Encryption later in this paper. Because SQL Server service accounts also abide by Windows password expiration policies, it is necessary to change the service account passwords at regular intervals. In SQL Server 2005, it is easier to abide by password expiration policies because changing the password of the service account does not require restarting SQL Server.

SQL Server 2005 requires that the service account have less privilege than in previous versions. Specifically, the privilege Act As Part of the Operating System (SE_TCB_NAME) is not required for the service account unless SQL Server 2005 is running on the Microsoft Windows Server™ 2000 SP4 operating system. After doing an upgrade in place, use the Group Policy Administration tool to remove this privilege.

The SQL Server Agent service account requires sysadmin privilege in the SQL Server instance that it is associated with. In SQL Server 2005, SQL Server Agent job steps can be configured to use proxies that encapsulate alternate credentials. A CREDENTIAL is simply a database object that is a symbolic name for a Windows user and password. A single CREDENTIAL can be used with multiple SQL Server Agent proxies. To accommodate the principal of least privilege, do not give excessive privileges to the SQL Server Agent service account. Instead, use a proxy that corresponds to a CREDENTIAL that has just enough privilege to perform the required task. A CREDENTIAL can also be used to reduce the privilege for a specific task if the SQL Server Agent service account has been configured with more privileges than needed for the task. Proxies can be used for:

• 
  ActiveX scripting
  
• 
  Operating system (CmdExec)
  
• 
  Replication agents
  
• 
  Analysis Services commands and queries
  
• 
  SSIS package execution (including maintenance plans)
  

• 
  Use a specific user account or domain account rather than a shared account for SQL Server services.
  
• 
  Use a separate account for each service.
  
• 
  Do not give any special privileges to the SQL Server service account; they will be assigned by group membership.
  
• 
  Manage privileges through the SQL Server supplied group account rather than through individual service user accounts.
  
• 
  Always use SQL Server Configuration Manager to change service accounts.
  
• 
  Change the service account password at regular intervals.
  
• 
  Use CREDENTIALs to execute job steps that require specific privileges rather than adjusting the privilege to the SQL Server Agent service account.
  
• 
  If an agent user needs to execute a job that requires different Windows credentials, assign them a proxy account that has just enough permissions to get the task done.