SQL Server 2005 Service Accounts
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/sqlserver2005serviceaccounts.asp
-
Thank you for the update on the type of services found in 2005.
Is there a recommendation for what user to start each service with? I have heard for security reasons you will want to start MSSQLSERVER as a domain user but not a domain admin. The user should have local administrator right on the server.
Now that there are ten services is there a recommendation for a multi-server environment? This should help with security and manageability?
Thank you,
-
Erik,
From what I've read in research and based on SS2K recommendations, you want to start all services with a domain user. This enables you to move the service in a DR situation easily. However, the user should neither be a domain admin nor a local admin. It's not necessary. You should stick with Configuration Manager or SQL Setup to set the accounts and the proper rights will be given. If they need additional rights to a folder, you might want to create another group or use one of the built-in SQL groups and assign the rights there, moving the account in .
As far as separation, I've run services under separate accounts or the same one (per instance). I HIGHLY recommend separate accounts for different instances. I need to do a Part 2 on that alone.
For each service, using separate accounts makes it a pain to setup, but you only do that rarely. And you should change those passwords rarely, so it shouldn't matter.
-
Thanks for the article - I've been a bit slow on the new world of SQL 2005 and find such bits of info useful for the increasingly frequent times that I am brushing up against the new beast
-
How rare is rare for you?
Steve wrote:
For each service, using separate accounts makes it a pain to setup, but you only do that rarely. And you should change those passwords rarely, so it shouldn't matter.
-
I am fairly new to SQL2K5. The various accounts and local Groups look more complicated but work fine, and it's great to rely on Configuration Manager to handle the group memberships.
After installing Management Studio, the only issue I had to resolve was displaying status information for connected servers. My domain account gets sysadmin rights on all our SQL servers, so I get a connection and can administer them; but I don't get status displayed until I grant myself remote WMI permissions (it's all in BOL). A quick and dirty fix is to make my account a local Admin (Adminstrators get WMI permissions by default). The proper way is to create a local group on each server, grant it WMI permissions, and get membership of that group (probably via a Global group - we have a mature domain structure and no Active Directory). It would have been nice for MS to have foreseen this need to administer WMI permissions and given us a standard group to use.
Am I overcomplicating things? Has anyone a recommendation or standard for handling WMI permissions?
Also: we run all our Server and Agent services under a domain admin account. While this is not recommended by MS, we have a tight corporate WAN, limited external connections and decent security. Can anyone suggest a good reason or risk to cause us to remove domain (and hence local) admin rights from this account?
-
I'd argue that domain admin isn't needed and if there is a hole in your permissions, it's not worth allowing a contractor or malicious (or unlucky) employee, the capabilies to cause domain issues. Changing it to a domain user, non admin, shouldn't impact the server and if things change, you're ok.
-
Great article Steve.
Do you have in mind to write a similar article for Katmai?
-
The MS article Security Best Practices - Operational and Administrative here http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SQL2005SecBestPract.doc may be also useful.
About Katmai; in the course available on MS website, there is not any "security" chapter under enhancements or new features, excepting the new AUDIT command. Does that mean the security of service accounts would be the same as for sql 2k5 service accounts?
-
looks like you can still stop Analysis Services separately - if you run:
net stop MSSQLServerOLAPService
Johnny
-
How did you grant remote WMI access??
I'm also struggling to have the status indicator show when using a non admin account
-
Hello Steve,
You recommend a naming convention such as SQL_(servername)_(instance)_(service). What if the this is a cluster? Would you replace the servername with the clustername?
Thanks,
Danny
-
Danny Beutler (6/11/2008)
Hello Steve,You recommend a naming convention such as SQL_(servername)_(instance)_(service). What if the this is a cluster? Would you replace the servername with the clustername?
Thanks,
Danny
I think you must have network name as a cluster resource in your sql server group, so I prefer to use that name.
-
Zarko Jovanovic (7/11/2008)
Danny Beutler (6/11/2008)
Hello Steve,You recommend a naming convention such as SQL_(servername)_(instance)_(service). What if the this is a cluster? Would you replace the servername with the clustername?
Thanks,
Danny
I think you must have network name as a cluster resource in your sql server group, so I prefer to use that name.
To expand on this further, this might be of interest (or bordom depending on your perspective! :w00t:)...
I have recently been involved in migrating an SQL 2005 cluster to a new domain (yes I know microsoft recommend a rebuild but we did not have that option). It works slightly differently for a cluster as you obviously use the cluster administrator to manage various resources. Your point above is correct as you define a 'virtual sql server' network name that you connect to, and this is set in the cluster administrator which registers the name in DNS.
The services are however set on each node (although you can alter some service names in the cluster administrator resource) and you can (although not advised!!) set a different service account to run the same service on each node!
I would like to see this article expanded to a cluster environment setting as well (if only to see if I have indeed set everything correctly!! :D)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply