August 10, 2012 at 11:52 am
Where we often have issues is with AD accounts and the groups the service accounts belong in. They run under restrictive GPOs and we can't create accounts from the DBA level.
August 10, 2012 at 12:26 pm
I like the idea but managing a service account for every server is a nightmare for my environments. We have upwards of 100 instances so that would mean 100 separate accounts.
I can see a different account for each environment but one for each server seems to be a bit overly complicated for my needs... but if one account gets compromised it does provide ultimate protection.
Perhaps I will suggest this for production use only and leave the rest with shared passwords for each environment.
Thanks for the idea, its these things that make me comeback every day to read what people have found or fixed. 😀
August 10, 2012 at 4:30 pm
We have 7 environments for most systems at the place I work, and we ended up using going down the route of all 6 non-production environments having service accounts on a per server basis and Production instances all have their own service accounts.
Like the rest of you, we use a password manager tool and make all passwords 20+ characters long.
August 10, 2012 at 7:40 pm
I have a separate account created every time I install SQL. I have been sharing accounts between SQL, Agent, Reporting and others, but after reading an article that explained how SQL grants specific privileges based on need, I am starting to use separate accounts for each service.
Why is it a hassle, because nobody wants to do more than the bare minimum since they have so much on their plate. But I ask nicely, and I haven't got any push back.
Dave
August 13, 2012 at 7:15 am
Michael Valentine Jones (8/10/2012)
I agree on the having a different account of each SQL Server with a long random password, but I think you need to store the password. I have seen too many cases where I needed to be able to login with the service account to be able to resolve an issue.
Hi Michael,
Could you share any cases where you have had to log in with a service account to resolve issues?
Regards,
Dave.
August 14, 2012 at 1:06 pm
david.moule (8/13/2012)
Could you share any cases where you have had to log in with a service account to resolve issues?
1) Security permissions testing; just why can't SQL Server access X network resource.
Note that I agree with separate service accounts for every server with extremely long, fully random passwords - eliminating cross-account lockouts is important, as is the ability to restrict who has access to what.
Storing the password is also important to easily test whether someone's changed it to "password" against policy.
August 14, 2012 at 1:13 pm
The problem I have is that the Service ID for SQL Server is not log on able... so... I use my Admin ID to log onto the server, install SQL Server and configure the Service ID to run SQL Server. THen, I try and backup a database to the root of the E drive and it says Access Denied. I create a folder on the root of E drive and backup to that folder and it works. Fun, fun.... security.
August 15, 2012 at 6:39 pm
rrcottin (8/10/2012)
The whole strategy makes implementing Kerberos in your environment a little tougher.
why?
August 15, 2012 at 6:43 pm
Mike Palecek (8/10/2012)
I will admit that I do not have very many instances under my control, but I very much agree with separate accounts and not storing the password. The administrative time spent on password resets, etc. I do not think is too much. The administrative time spent on resting user's forgotten passwords is acceptable so why wouldn't this be?
This isn't for admin password, but a service. You should almost never need a service account password reset.
August 15, 2012 at 6:44 pm
Adam Seniuk (8/10/2012)
I like the idea but managing a service account for every server is a nightmare for my environments.
Why? Once the instance is up and running, the service account is almost never needed? What's the administrative overhead?
August 15, 2012 at 7:33 pm
Steve Jones - SSC Editor (8/15/2012)[hrWhy? Once the instance is up and running, the service account is almost never needed? What's the administrative overhead?
If there is separation of duties where you, as a DBA, do not have the rights to create accounts or drop them in OUs then it is a hassle.
I see a certain logic in what you propose but I wonder how well it scales... especially in an evironment where you can't convince other groups that keeping the same password indefinitely is fine for service accounts.
August 16, 2012 at 8:07 am
BlackHawk-17 has it right, I cannot create accounts but I do need to keep track of them. Also I am in a government position so there is red tape when you want to change from standard guidelines.
Sometimes people don't like change... but I pushed for it and now our new environments "production and pre-production" will have seperate accounts.
Also when installing I found it easier to install with default accounts then go back and reconfigure the services with the AD accounts, any one see issues with doing it that way?
August 16, 2012 at 8:36 am
Adam Seniuk (8/16/2012)
Also when installing I found it easier to install with default accounts then go back and reconfigure the services with the AD accounts, any one see issues with doing it that way?
I don't know how true this is for more recent versions, but if you installed 2005 and below without specifying Windows accounts, you'd get monstrosities such as the BUILTIN\Administrators and NTAUTHORITY\SYSTEM having sysadmin access. Fine if your installation process involves removing that, but no so good if you come back and try to to fix it months later.
We run each service on each instance under a different account. This isn't only to stop a security breach or lockout from affecting everything, it also makes it harder to do things accidentally. Got a process that BCPs data out to a folder? If you have your file system security set up properly and you set the wrong folder, it'll fail with an Access Denied error.
One thing that may make those who use a single account (or small number of accounts) to sleep easier is that SQL Server will continue to run happily after its service account password is changed - until you restart the service, of course. We proved this when our security people insisted on denying us access to the passwords for our own service accounts.
John
August 16, 2012 at 8:38 am
Adam Seniuk (8/16/2012)
Also when installing I found it easier to install with default accounts then go back and reconfigure the services with the AD accounts, any one see issues with doing it that way?
There have been bugs that not everything gets updated when you update the service account in Configuration Manager but I don't remember the details. What's hard about just using the AD account up front? Don't always have the account created when the machine is needed?
August 16, 2012 at 9:45 am
cfradenburg (8/16/2012)
Adam Seniuk (8/16/2012)
Also when installing I found it easier to install with default accounts then go back and reconfigure the services with the AD accounts, any one see issues with doing it that way?There have been bugs that not everything gets updated when you update the service account in Configuration Manager but I don't remember the details. What's hard about just using the AD account up front? Don't always have the account created when the machine is needed?
Pretty much the case, also I am doing 20 installs so it makes it easier to do a scripted install so there is less clicks. I will review the accounts interally and remove any extra access points. Also I will check the services and folders to make sure they are correctly setup.
Thanks all for the feedback.
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply