6

We have a number of MS SQL servers in our environment running either SQL Server 2005 standard/enterprise or SQL server 2008 enterprise. Currently the SQL services are running as local service or network service and the MS recommended best practice is to run as a domain account which is what we are trying to move towards.

Is the best practice with regards to domain accounts to have a separate domain account per service per server? So if we have 4 SQL services we want to run per server and we have 50 servers, we would create 50 * 4 = 200 accounts in AD? This seems excessive to me and I was wondering if anyone has any real experience with this type of setup and its management.

BoxerBucks
  • 1,374
  • 1
  • 9
  • 19

3 Answers3

4

I generally create a single domain service account and use that for all of the services on all of the servers. My suggestion would be to do one of two things:

  1. Create a single domain service account that is used for all services on all servers.

  2. Create a domain service account for all of the services on each server, so you'll have a separate service account for each server instead of four service accounts for each server.

joeqwerty
  • 108,377
  • 6
  • 80
  • 171
  • My only concern with this approach is password rotation. Since we are required to change pw every 45 days, if I change it on the domain account, I have to change it on every server at that time. – BoxerBucks Jun 02 '10 at 12:55
  • Yes you would. Service accounts are one of those annoying things that come up with password security rotations. If it's a large hassle you could possibly see if service accounts could be on a longer rotation. There may be some 3rd party software for this kind of thing as well but I've never looked into that. – Sean Howat Jun 02 '10 at 14:11
  • Unfortunately, there are mandatory security reasons we have a short password rotation schedule. So I guess that's the trade off. Use many service accounts for more granular change cadences, use one for a big bang password rotation. I was just wondering if there is any other reason to, or if anyone else practices, using separate service accounts per machine per service. – BoxerBucks Jun 02 '10 at 15:12
  • There are third party utilities for this type of thing. Here's just one from a Bing search: http://toolsite.liebsoft.com/sam_features.htm – joeqwerty Jun 03 '10 at 00:14
  • 1
    @boxerbucks, Password policies are usually only applied to account types that can login interactively; service accounts should not be able to do so and most security policies exempt them. (My experience, food for thought). – Chris S Aug 13 '10 at 12:59
1

If your AD schema is at 2008 R2 and the SQL servers are also R2, you may want to investigate Managed Service Accounts. (it looks like this can be used if you're on earlier versions, but it sounds like more of a pain than it's worth)

You can set up scheduled & automatic password changes, convert existing service accounts to be managed, set account expiration, create them on the domain or locally... It looks like the accounts will then have new passwords generated for them according to the domain policy Domain Member: Maximum machine account password age under Local Policy\Security Options.

Kara Marfia
  • 7,892
  • 5
  • 32
  • 56
  • This is a great new feature of 2008 and when we upgrade all our DC's, we will raise our domain functional level to 2008 R2 so we can use these managed accounts. – BoxerBucks Sep 08 '10 at 12:58
-1

we run all our SQL Services under domain administrator account, our network security policy is such a way that we need to change domain admin password often, one alternative i have is that to create a domain user with admin privileges, is this advisable or should i use admin account only or are there any alternatives with better security. please give your feedback.

Regards Praveen

  • 2
    The domain admin account should **never** be used for service accounts. Also; please do not hi-hack others questions, please use the "Ask Question" button in the upper right corner of every page. Thank you and welcome to Server Fault. – Chris S Aug 13 '10 at 13:03