June 20, 2007 at 5:19 pm
What is a recommended practice regarding SQL Server & Agent service accounts? I didn't think SQL Server service required sysadmin privileges to the server, but recently when i changed the svc a/c it using EM, it was setup with sysadmin on that instance.
This being said, why would one use separate accounts for the db service and the agent svc. I found this to be true for both 2000 & 2005.
June 20, 2007 at 8:19 pm
SQL Server 2005 executes as a set of Windows services. Each service can be configured to use its own service account. This facility is exposed at installation. SQL Server Configuration Manager manages these accounts. In addition, these accounts can be set programmatically through the SQL Server WMI Provider for Configuration. When selecting a Windows account to be a SQL Server service account, there is a choice of:
· 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
When choosing service accounts, the principle of least privilege should be considered. This is equivalent with the following statement:
The service account should have exactly the privileges that it needs to do its job and no more privileges.
Account isolation should be considered as well:
The service accounts should not only be different from one another, they should not be used by any other service on the same server.
Only the first two account types in the list above have both of these properties. Making the SQL Server service account an administrator, at either a server level or a domain level, bestows too many unneeded privileges and should never be done. The Local System account is not only an account with too many privileges, but it is a shared account and might be used by other services on the same server. Any other service that uses this account has the same set up privileges as the SQL Server service that uses the account. Although Network Service has network access and is not a Windows super-user account, it is a shareable account. This account is useable as a SQL Server service account only if can ensure that no other services that use this account are installed on the server.
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.
The ability to use the SQL Server Instant File Initialization feature should be activated - it 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."
BOL In SQL Server 2005, data files can be initialized instantaneously. This allows for fast execution of the following file operations:
Create a database
Add files, log or data, to an existing database
Increase the size of an existing file
Restore a database or filegroup
Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files.
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.
Because SQL Server service accounts also abide by Windows password expiration policies, Microsoft recommends changing 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. After doing an upgrade in place, the Group Policy Administration tool can be used to remove this privilege.
The SQL Server Agent service account requires sysadmin privilege in the SQL Server instance that it is associated with.
Note: To accommodate the principal of least privilege, the SQL Server Agent service account should not have excessive privileges. Instead, a proxy should be used 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)
June 20, 2007 at 10:34 pm
This is the microsoft KB article for permissions for sql server service accounts.
http://support.microsoft.com/kb/283811
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply