June 26, 2008 at 8:45 pm
Comments posted to this topic are about the item The Multiple Instance Dilemna
June 27, 2008 at 12:38 am
It is a tough one as you have to manage security vs practicality. You need your systems secure, but you cannot make it so secure as you cannot function in your support role adequately. This is something that must "flex" depending on the security requirements / policy of your current employer, however.
It goes without saying that the "DBA" group applied to replace BUILTIN\ADMINISTRATORS is an absolute must, but I also currently use server specific accounts with very little permissions applied.
SA password I always rename and have the same across all instances and store that password / username combination in a password vault of some kind. Periodic changes are also essential, as are when a privileged staff member leaves.
June 27, 2008 at 1:52 am
I'm the other way round - though the number of SQL instances we manage in our business is pretty small (<10). But then I do have a different SA password for every instance.
Having said that, from a practicality point of view I can certainly see the advantage of having separate service accounts if you have many SQL instances and change these service account passwords on a regular basis.
This topic's certainly given me food for thought, so I might consider implementing some of these recommendations when I come round to doing a wider security review (currently trying to tighten up some very loose security on some of our databases!).
June 27, 2008 at 2:26 am
My policy is rather having a unique complex (>50 chars) sa password that only the dba and the IT mgr / Security mgr knows. Just like the domain admin account.
As for centralized administration, we use 1+ domain group(s) for DBAs.
When administering an instance on a non approved domain, if possible, connect on a remote admin console (via TS...) which is in the correct domain, and on which admin tools are installed.
Only on last resort, connect with a SQL alternate login (like "dba", or so). I'd then suggest to NEVER "remeber password" in your admin tools (Mgt studio...), unless your workstation is a real "VAULT" (even more secured than your db servers).
June 27, 2008 at 2:49 am
I will use only one set of username and password for the SA account for all the instances of SQL Server. The SA account will only be known to the Senior or Head DBA and IT Head. I will definitely remove the builtin/admin user account. Then I will setup a domain group for all the DBA. Then enforce a strict password security policy on it.
June 27, 2008 at 6:10 am
I must admit I hate with a vengeance the idea of multiple instances of generic logins with the same password, so we have different sa passwords for each instance (and, as it happens, different system and sys passwords for each Oracle instance too). If we can use Windows authentication, then we do, and if we regularly need to access instances with SQL logins, we create user-specific logins with equivalent rights instead (in which case, since it's only that user who knows the password, they can use the same password across multiple servers if they wish).
Personally, I'd far rather each DBA had their own login than have them all regularly diving in and out of the databases with sa - at least the logs then show who's responsible for making any given changes. And if you set things up like that, having different sa passwords for each instance is then not really that much hassle, and a compromised login on one server is far more containable.
Semper in excretia, suus solum profundum variat
June 27, 2008 at 6:34 am
And then there is SOx :blink: ...
Nowadays we implement a service account per service. The corresponding passwords are guid-like strings, so only usable for install using an INI file.
We have mixed configurations, and also generate a guid-like string for sa.
sa is not used, admin tasks are performed using windows logins.
sa passwords are changed 2 times a year using a script like this:
(just to be sure ...)
Declare @TheNewOne varchar(128)
Select @TheNewOne = newid()
ALTER LOGIN [sa] WITH PASSWORD=@TheNewOne
-- sa = sqlgod so noone should use it !
And yes,for sysadmins there is a separate individual windows login per environment (dev/QA/prod), all nicely traced/audited.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 27, 2008 at 7:22 am
For a small number of servers, I use the same username/password for all. It is too much of a pain to keep track of a different password for each server. Besides, any kind of "standardized" password will be easy to figure out once someone figures out your pattern. I create a complex password of 15+ characters and keep that in a password protected file on my computer. I will change it once per year but no more frequently.
If I had to manage a larger environment, I may consider a different password for different "logical" groups of servers but still would NEVER maintain separate passwords for each physical server.
June 27, 2008 at 7:24 am
I'd face huge logistical challenges in updating our service accounts. I've got ~100 SQL instances running; and I suspect there are a few more I don't even know about (they surface from time to time when someone needs help). Given company history and undefined standards, I expect there are also many DTS packages and maintenance jobs running under the service accounts. Ferreting all of this out and updating it correctly before any of the SQL instances tries to restart (or someone tries running a job) is a daunting prospect!
What I'd like to do is get a brand new domain account and gradually swap old for new keeping careful track of which servers have been converted (perhaps as we migrate each instance to 2005).
At some point we'll have to update the original passwords to find what we've missed!
June 27, 2008 at 8:03 am
I tend to keep the sa password consistent on all instances, and agree with changing it frequently (although I think that changing it every 30 days is too often). I also use windows authentication as much as possible to decrease the number of places that I need to manually change passwords.
June 27, 2008 at 8:35 am
At my last job where I was the DBA Team Lead, I implemeneted a policy of unique, 12-character random passwords (from Password Safe), but we had a single set of service accounts (one for each service). We were also in the process of eliminating BUILTIN\ADMIN access. At my current job, the password policy (or more accurately, the effective lack of a policy) drives me nuts. We have to change the SQL port from 1433 on all our installations, but the password policy is so weak ... :crazy:
June 27, 2008 at 8:45 am
I use different SQL Server service accounts and passwords for each SQL Server instance. We have about 200 SQL Server instances; so it is out of the question to attempt to share them.
If you share the account across multiple servers, it is almost impossible to change the password without causing a major production outage.
I generate a random password of at least 12 characters in length to make a brute force attack against the service accounts virtually impossible.
For SQL Server logins, I only allow their use with applications that cannot use Windows authentication, and I generate random 30+ character passwords to discourage routine use and make them safe against brute force hacks.
June 27, 2008 at 9:08 am
A leftover from my days as a UNIX admin with about 20 systems - use the same password and key it to the date it changed.
Example:
Use a pattern for the password, such as MMDddDD
Change the password on Tuesday, January 3.
Send email: password changed today.
Those with a need to know can look at the calendar and recognize the password is 01Tue03.
You may want something more secure, such as more characters (YYMMDddDD: 0801Tue03) plus some punctuation, but the concept is easy to use and the password itself is never posted.
John
June 27, 2008 at 9:10 am
I totally agree with Mickael, and that's the way we do it with SQL 2k5 instances.
1 svc account per instance (Engine, Agent...), whatever the number of instances on a server.
Automatic random password generation is something we didn't implement, as service accounts are domain accounts, and we choosed to NOT allow automatic domain account creation during our (scripted) installations.
This has a priceless advantage, however : NO-ONE (not event install operator, dba, security mgr...) is ever aware (no mail, tel call, hand draft...) of any svc account password. Your security manager will love it 🙂
June 27, 2008 at 9:12 am
At a previous job, we used to have the same SA passwords....until some trojan captured that password and started mercilessly DOS'ing our servers.
So, instead of having a common password, we came up with a common "formula" for the password, based partly on the server name.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply