June 27, 2008 at 9:16 am
for security reasons we go for every sql instance runs under a different account (unless part of logshipped\mirrored pair) with different passwords.
sa password also different on each server.
all dbas in a windows group with sysadmin privelages so thats how we log on, that way we hardly ever use sa account and if we lose that password we can just change it. sa password also changes on regular basis.
passwords kept secure in central location.
---------------------------------------------------------------------
June 27, 2008 at 9:18 am
Matt Miller (6/27/2008)
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.
Ouch... do you know how it got your sa password? Stored somewhere?
June 27, 2008 at 9:20 am
Normal is to use a single SA account. After reading some of the reasoning here it might be worth another look.
Not all gray hairs are Dinosaurs!
June 27, 2008 at 9:26 am
Its safer to go for separate service accounts for each instance. that way if you accidently lock out the account you dont have downtime on all your instances. Since the use of kerberos in 2005, sql reacts almost instantly to service account lockouts :w00t:
June 27, 2008 at 10:42 am
I use passwords like, "Changing a password every 30 days is ridiculous".
and
"Having a separate username/password per instance isn't necessary"
June 29, 2008 at 1:04 am
We only have one instance per server, so that simplifies things somewhat. Each server has service accoutns that follow a naming ocnvention that includes the server domain and the server name and the type of service. sa is locked down on all servers. No one, ever, under any circumstances, logs in as sa. DBA's have separate ID's that they use for administering servers. These ID's are memebrs of a group with sysadmin rights. Logins are done witehr by remote connection or by terminal server. The privileged ID activities are tracked and reviewed. The goal is to prevent DBA's from doing something stupid accidentally. Any other privileged ID's are protected with a password system that checks out the ID to an authorized user, then changes the password when the ID is checked in. If a user fails to check the ID in in a specified time period, the password expires and the [assword becomes unusable. The ID's are not allowed to change their own passwords. Usage of the privileged ID's is tracked and reviewed.
June 30, 2008 at 7:39 am
I generally use Windows domain security, and don't use mixed-mode. That means the sa account isn't active.
When I have had to use mixed security, I've used different, extreme passwords for each instance. I don't change them unless there's a need for it (like someone leaving who knew one). Frequently changing passwords doesn't really increase security, per studies and tests. (In many cases, it actually reduces security.)
At the place I currently work for, all jobs, etc., run under one domain account, which isn't used for anything else. I'd prefer that it be done under multiple separate accounts, since this one account has to have far too many privileges in order to do all the things it is used for, but I'm not the one in charge of security for these servers.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 30, 2008 at 10:29 am
We have 200+ Instances (increasing by the week it seems).
Builtin\Admins removed, SA set to a formula that can be worked out if necessary but no-one would normally login with SA.
Where possible Windows Authentication is only used. Due to the Apps though it is sometimes unavoidable to use mixed mode.
DBA group with SA privs.
Our rule is that no-one except DBAs have SA privs. In a few cases where a useless 3rd party app has been purchased and this is not possible we make it clear that we cannot provide normal support levels and if there are problems we will only help out on a "best endeavors" basis.
All new SQL servers are built using the same domain "service account" to run the services under.
This has the advantage that as a lot of our servers talk to each other we don't get any access issues, the downside is that if for some reason there was a problem with the account all the servers would stop working.
June 30, 2008 at 11:33 am
Stueyd (6/30/2008)
Our rule is that no-one except DBAs have SA privs. In a few cases where a useless 3rd party app has been purchased and this is not possible we make it clear that we cannot provide normal support levels and if there are problems we will only help out on a "best endeavors" basis.
Our problem is that we buy most of our apps "on the shelf". And few respect security best practises. Some of them require sysadmin server role. Our policy is then :
- dedicated instance
- network and / or domain quarantine
- non-admin service account with restricted NTFS privileges.
Stueyd (6/30/2008)
All new SQL servers are built using the same domain "service account" to run the services under.
The problem is, a sysadmin login can then do whatever he wants on ALL other db servers (instance and OS).
Don't want to give bad ideas here, but if someone can inject SQL through ONLY ONE of these loosy applications, he'll get some high privileges at instance and OS levels, on all DB Servers in your domain (hope SQL service account is not domain admin ;o).
June 30, 2008 at 1:14 pm
I'd question all third party apps on this.
We implemented Dynamics about 8 years ago and they said they required SA to run the application. I dug in and we determined they really only used SA to create logins, and even then, they could have gotten by with security admin.
However no one at Dynamics (shortly before MS purchased it), paid attention. We ended up creating logins manually and then matching them up in the application worked great.
We just had to allow an extra 10-15 minutes for each support call to explain to them why their requirement of sa was bull@$#%%$%#
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 1, 2008 at 12:20 am
Steve Jones - Editor (6/30/2008)
I'd question all third party apps on this.We implemented Dynamics about 8 years ago and they said they required SA to run the application. I dug in and we determined they really only used SA to create logins, and even then, they could have gotten by with security admin.
However no one at Dynamics (shortly before MS purchased it), paid attention. We ended up creating logins manually and then matching them up in the application worked great.
We just had to allow an extra 10-15 minutes for each support call to explain to them why their requirement of sa was bull@$#%%$%#
Oh yes, most software vendor sales rep / installers just haven't got a clue how their applications security is set up.
In many cases it's just an installtime .INI files that needs to be modified and the needed accounts at most need db-ownership (install time). (in many cases not even that at runtime)
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
July 1, 2008 at 1:29 am
I think this kind of underlines an important point. Anyone blithely saying "there's no need for separate passwords for each instance" is making just as bold and sweeping a statement as anyone saying "if you don't have separate passwords for every instance, you cannot fail to end up with a disaster". The true standpoint is, as we so often see, a big fat "depends".
Obviously, the more places you use the same password, the greater the scope for mischief by anyone who gets hold of that password. Similarly obviously, the more you segregate instances password-wise, the more work it is to maintain. Where on that spectrum is appropriate for your company depends heavily on other security practices in place, the prevailing maintenance capacity and, as can be seen above, the number and type of third party database requirements.
Me, I infinitely prefer using separate passwords per instance (a breach in one place is, by definition, contained), but I fully expect that I'd review that rule were the shape of my SQL Server infrastructure to significantly change, either by more databases being needed or new third party app requirements.
Semper in excretia, sumus solum profundum variat
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply