August 9, 2012 at 9:44 pm
Comments posted to this topic are about the item Separate Accounts
August 10, 2012 at 2:28 am
Hi, It is nice to know that "you are not alone".
I too believe in the separate service account per instance, although you're "do not store the password and change it when needed" seems to be a bit extreme.
August 10, 2012 at 5:49 am
I really like that idea and I will pass it on to our DBA's and admins.
August 10, 2012 at 6:12 am
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?
August 10, 2012 at 6:40 am
I think at the least should be separate account for SQL, there by separating the sql db from system admin.
If separate instanances managed by different people then should also have separate accounts for each.
August 10, 2012 at 6:49 am
We have the same account to run SQL Server for all production and a seperate ID for non production. I was going to have a new account created and used for SQL2012 and continue that with a new account for each new version of SQL Server....
August 10, 2012 at 6:59 am
When I started here the same account was used for all servers and services. All new servers get new accounts created though and we're working on changing the account on old servers. We do use the same account for all services on a box.
The only reason it's a pain to do it this way is the IS Security team is so far behind that it can take a couple weeks to get a new service account created. But it's worth it knowing that if one account gets compromised or locked out that it's not going to affect over 150 servers in our hospital system.
August 10, 2012 at 7:10 am
The whole strategy makes implementing Kerberos in your environment a little tougher.
August 10, 2012 at 7:18 am
If the password of a SQL service account is THAT important to your companies defense of it's data then you are already inside a hurt locker if you ask me but I do know we'd have to change our SOX/SAS/PCI/GSA account documentation to even think about doing this and it would result in god only knows how many issues since we have automated the security model around a 90 day change of service accounts, instance spawning automation and self serivice account management for our clients... I guess for a small shop with under 100 servers this might be a no brainer, but with VM the days of counting servers on two hands are gone...
August 10, 2012 at 7:40 am
We used to have one service account for all instances, but decided a while ago that was a risky strategy. We are now moving to one account per instance, or per pair of instances where we are mirroring databases.
Our Windows Ops team have given us management of an AD group to create these accounts in to.
We do store the passwords for these service accounts, but that's more out of habit than for any specific reason.
August 10, 2012 at 8:05 am
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.
I used KeePass to store the passwords for over 300 servers, along with SQL Server application logins, linked server logins, etc. I generally used a 20 character or more password to make sure that even determined guessing will never succeed.
Using the same account for that many servers is just asking for trouble, and there are just too many ways that someone with sysadmin access on one server might be able to gain access to other servers.
An account lockout on one server takes everything down, like the case you mentioned where someone changed the password.
Also, what do you do if you have a security breach and need to change the common password? It's very unlikely that all servers would have a common maintenance window that would allow this without a major business disruption.
I also use this to be to generate long passwords that it is still possible type without making a lot of mistakes:
August 10, 2012 at 9:20 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.I used KeePass to store the passwords for over 300 servers, along with SQL Server application logins, linked server logins, etc. I generally used a 20 character or more password to make sure that even determined guessing will never succeed.
Using the same account for that many servers is just asking for trouble, and there are just too many ways that someone with sysadmin access on one server might be able to gain access to other servers.
+1 to different accounts and passwords for each instance of SQL Server
+1 to KeePass; it makes generation, storage, and organization of passwords secure and easy
August 10, 2012 at 9:20 am
Basically you are taking the application approach to your SQL instances. Each application, gets a dedicated service account to ensure the application is properly restricted within your environment.
And it not much of an administrative issue, as typically all the accounts are stored in password vault of some nature.
It is also common practice to make the local adminstrator account the same (userid/password) accross all servers.
That way you can still access the machine and see which account(s) have access to the machine/instance.
August 10, 2012 at 10:21 am
SharePoint uses "Managed Accounts" which manage the AD accounts for you. SQL Server should have something similar for exactly this reason...
http://doitwithsharepoint.blogspot.com/2011/07/managed-accounts-in-sharepoint-2010.html
Basically, you can configure a SharePoint service (application pool, indexing service, reporting service, etc.) to use an AD account and automatically change the password for you. If you need to reset the password you do it from the SharePoint interface rather than the AD interface and the changed password will be replicated throughout all servers.
The passwords are stored in a secured registry key that only the SharePoint "farm" administrator can access and even then it's encrypted with the "farm" encryption key you setup during installation:
http://blogs.technet.com/b/wbaer/archive/2010/04/11/managed-accounts.aspx
August 10, 2012 at 11:49 am
We use separate accounts but the password is stored in a secure vault. No one should be typically using these accounts but the are not set up as "non-logonable" Administrators generally understand that they use their own personal accounts for access, not a service account
Francis
Viewing 15 posts - 1 through 15 (of 50 total)
You must be logged in to reply to this topic. Login to reply