January 24, 2006 at 8:28 am
We are in the process of standardizing our SQL Server environment. My question is does the service accounts for the instance and sql server agent need to be in the administrators group on the server. I know that they should be domain accounts, but does that domain account need to be in the local administrators group. We will be using a clustered environment, and I think that makes a difference too. Is that correct?
January 24, 2006 at 8:39 am
I can't speak for the clustered environment but SQL2000 does NOT require the service accounts to be local administrators.
The accounts obviously need full privileges to the directories that SQL uses but I have seen nothing definitive for the list of what else might be needed.
I think the precise permissions depend on what portions of SQL you are using. For example, full text searching may require additional permissions where as bog standard DML statements (SELECT, INSERT, UPDATE, DELETE) will require minimal permissions.
The other thing is that each server should have it's own domain account so that one compromised server is limited in how it can affect another.
January 24, 2006 at 8:47 am
The short answer on clustered setups is no, the service accounts do not need to be members of the local administrators group, except for Windows NT 4.0. More here:
How to change service accounts for a clustered SQL Server computer (239885)
Scroll down to the SQL Server 2000 and 2005 section.
K. Brian Kelley
@kbriankelley
January 24, 2006 at 8:55 am
Thanks for the information. From the link you provided there was this bullet:
If the service account for SQL Server is not an administrator in a cluster, the administrative shares cannot be deleted on any nodes of the cluster. The administrative shares must be available in a cluster for SQL Server to function.
I'm not sure I understand this point, can you shed some light on what it actually means.
January 24, 2006 at 9:01 am
Sure. When you install the Windows operating system (NT 4.0, 2000, XP, 2003 and any based on the NT kernel), any drive letters related to hard drives are automatically shared out for administrative access. For instance, if you have a C:\ and an E:\ drive on your server and both refer to hard disk, Windows will automatically create a C$ share and an E$ share. The only access permitted to those shares is to the server's local Administrators group. You can see this on your own workstation if you check out shared drives.
Basically this bullet says that if you don't run SQL Server as an administrator on all nodes of the cluster, don't remove the administrative shares. Removal of the administrative shares is always a consideration when hardening the box and can be done by group policy or registry setting. In the case where you're not running SQL Server with an account that's a local administrator, don't make this change.
K. Brian Kelley
@kbriankelley
January 24, 2006 at 9:11 am
Thanks Brian, you cleared that up for me. Currently we have the service accounts set up with a domain account and in the administrators group. For our new setup we are considering not having the service account part of the administrators group. What is recommended?
January 24, 2006 at 9:25 am
If you follow the Principle of Least Privilege, then the service accounts should be configured with the minimum privileges necessary. That means no administrative access because that's not needed. However, there are trade-offs with doing so and that is enumerated in Books Online under the topic:
Installing SQL Server >> Setting up Windows Service Accounts
Quite honestly the most secure configuration is to lock down those accounts. But I think most folks with clusters run SQL Server with administrative rights on those clusters. The answer really depends on what you're doing on the cluster. For instance, if you're shelling out of SQL Server with xp_cmdshell, that weighs into it.
With 2005 the guidance is certainly to go with the more secure configuration. The SQL Protocols blog has the following to say on the subject:
When installing SQL server 2005, it is always a good practice to use a domain account as the SQL service account and to ensure that such account does not have full administration rights on the local computer. There are some implications in doing this when setting up a SQL server as a virtual server on a Windows cluster. The considerations are in configuring SQL HTTP-SOAP native services.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply