September 11, 2013 at 7:38 am
Just wanting to get your views on the use of a common sa password for all instances vs individual sa passwords.
Obviously the sa login is to only to be used in emergencies and the password will be really complex.
What do you do?
September 11, 2013 at 8:11 am
1 password per instance. We personally have a stored procedure which creates secure passwords up to 15 characters long with varied case / numbers / symbols (excluding certain symbols).
The passwords are stored in a master sheet which only a few DBA have access to (also protected).
September 11, 2013 at 8:26 am
mike.dinnis (9/11/2013)
Just wanting to get your views on the use of a common sa password for all instances vs individual sa passwords.Obviously the sa login is to only to be used in emergencies and the password will be really complex.
What do you do?
I follow a naming convention to name all my sql instances, and follow a similar one for the sa password. So for any instance i have installed, all i need to know is the instance name and I can guess at what I set the sa account password to be (it is still complicated). Either way, your sa account should be disabled anyways 🙂
September 11, 2013 at 12:43 pm
What should it be? A unique password for each server across the entire enterprise.
What is it usually? One 'sa' password across the entire enterprise.
What compromise I have I used? Disable the 'sa' login completely and rely on AD logins to manage all the servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 11, 2013 at 12:47 pm
Grant Fritchey (9/11/2013)
Disable the 'sa' login completely and rely on AD logins to manage all the servers.
This.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2013 at 1:06 pm
GilaMonster (9/11/2013)
Grant Fritchey (9/11/2013)
Disable the 'sa' login completely and rely on AD logins to manage all the servers.This.
Yes, but you should still set a strong SA password, and make sure it is unique. Since the SA password should be rarely used, 15 or more random characters is not overkill.
Also, make sure you use unique, strong, random passwords for the SQL Server service accounts, and setup different service accounts for each SQL Server.
Use a password safe program, like KeePass (it's open source ad free), to securely store all the passwords.
September 11, 2013 at 2:01 pm
I would never allow the sa password to be the same on more than one instance. The sa account is to be used only in an emergency anyway (such as via DAC when no other method can get to the instance).
I also insure that sa passwords are changed often and on a random schedule. That's to try to prevent anything from using the sa account for any non-emergency activity.
The generated password values are stored in a table that is encrypted and is DENYed to public.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 12, 2013 at 6:07 am
GilaMonster (9/11/2013)
Grant Fritchey (9/11/2013)
Disable the 'sa' login completely and rely on AD logins to manage all the servers.This.
+1
September 12, 2013 at 10:10 am
Grant Fritchey (9/11/2013)
Disable the 'sa' login completely and rely on AD logins to manage all the servers.
I agree in theory, but in a disaster recovery scenario, is it then possible to work on SQL Server before AD is online and available? I haven't tested that.
Michael Valentine Jones (9/11/2013)
Since the SA password should be rarely used, 15 or more random characters is not overkill.Use a password safe program, like KeePass (it's open source ad free), to securely store all the passwords.
For those servers I have with an "sa" account, they all have individual long, cryptographically random sa passwords.
More importantly, whenever someone who had access to them leaves the company, they all get changed. This is critical!
Piker. SA passwords can be up to 128 characters long, and include both symbols and extended ASCII. At anything more than length 25 or so, you're going to use copy/paste or KeePass autotyping anyway, so there's no reason not to go for a maximally secure password.
Personally, I have had trouble with AD passwords of length 128 for SSRS credentials, so I'll often go down to length 126 - still including symbols and extended ASCII.
+1 on KeePass (http://keepass.info/[/url]) - don't forget, after you create your KeePass database, go to File, Database Settings, Security, click the "1 second delay" link, and then INCREASE it further - you're not going to get getting into these very often, so if it takes a few seconds, who cares? This will give you a little more margin in the future.
For a second layer of security, you can also store your sa password list (whether it be KeePass, encrypted Excel document, or whatever) on one or more encrypted flash drive(s), some of which are FIPS 140-2 validated (http://www.apricorn.com/products/hardware-encrypted-drives/aegis-secure-key.html) - note that these erase the encryption key after so many (10) bad PIN attempts.
September 12, 2013 at 10:32 am
In a D/R situation, it might be a royal pita to have only AD accounts to have access to the instance.
I'd rather be able, in a genuine emergency, to bring up the instance if necessary in a different domain for testing, recovery, etc..
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 12, 2013 at 11:24 am
First, give each individual who needs sysadmin access through SQL login (ie can't reasonably use an NT login for it) a separate SQL login on each instance on each server, give each login the privileges it needs, and enforce a sensible password policy. You have the option of telling SQL Server that passwords for this account must meet the NT password policy of the server; if the server has a sensible password policy, that's a good thing to do, otherwise it's probably not so you may need to rely on having a written policy and trusting people to follow it (perhaps enforcing the NT policy even when it's not a sensible policy would be a good thing for any of your privileged people who are likely to do something even stupider than what the not sensible NT password policy allows, but you would probably be much better off ensuring that any such people have insufficient access to your databases to do any harm). Do remember that draconian rules requiring excessively frequent password changes are always part of a bad password policy, never part of a good one.
Second, give the SA account on each instance on each server a different password; make sure each password long and so messy that no-one stands a chance of remembering it, and don't write it down, and then disable every SA account.
If it's workable, it's best to stick to NT logins only for privileged users; it's fine to create privileged SQL logins as above if you can't survive on NT logins, but not otherwise. Having a privileged account used by more than one person is less secure that having them use separate accounts. Having two privileged accounts with the same name on two different instances or servers is less secure that giving all privileged accounts different names. Leaving the SA account without a very messy password is a little insecure if the account is disable but pure insanity if the account is not disabled. Having a privileged account with a well-known name (eg SA for an SQL account or Administrator for an NT account) is extremely insecure unless that account is disabled.
Always delete privileged accounts immediately their user ceases to be entitled to the privileges.
Tom
September 12, 2013 at 11:56 am
L' Eomot Inversé (9/12/2013)
if the server has a sensible password policy, that's a good thing to do...
Do remember that draconian rules requiring excessively frequent password changes are always part of a bad password policy, never part of a good one.
Second, give the SA account on each instance on each server a different password; make sure each password long and so messy that no-one stands a chance of remembering it, and don't write it down, and then disable every SA account.
...
Everyone, be aware that even sensible password policies are 100% incapable of preventing users from choosing bad passwords, like "Password1" or "P@$$w0rd" or "Christine1994!", since complexity rules don't account for dictionary* or rules-based dictionary* attacks.
I agree completely regarding change frequency - password change rules have only one purpose: to limit the amount of time a password is useful if it's leaked/broken. A truly good password (see below) isn't going to be broken, though it can still be leaked. Changing passwords perhaps once a year seems about right to me, and that's mainly just to invalidate passwords people knew when they moved departments and should no longer know.
Examples of "messy" cryptographically random passwords (DO NOT USE THESE EXACTLY - use KeePass or another local generator to build yours - they'll end up in password cracker wordlists very soon)
128 character, symbols and extended ASCII, total keyspace > 2^512:
Ròú#ÔÀdc=y…†5ý@Qóûÿèíæ·—9ÿësrÿ5:!tqÛÞtPp|]ï‰vªSê+þ]íá…ƒh.xÀåMGûÛ/Ãݹ™?…(ª9ÀgÒbšÜçÆõ‹ðÏREñêèñW8f¬n™RNÞÑz¿Çw—(?¾!?c0NT»çê^Å_a
A much more sedate 126 character, both cases and numbers only, total keyspace > 2^512
dDdFgVCto3lTXLKdFi05Xx7wyb4rv0FvKqvAByASWnwx7WzXw8qiKWsRr1CWgzHyo8WrUCULdJno8gxPOFZHYyXN5XYwQY8HSJ9vmP53sznznxvXr6L7Knl2fgH5On
An amusing 128 character password, curly braces only, total keyspace = 2^128 (estimated to last 2E20 years if you use one machine today and it's a single SHA1 [SQL 2005-2008 R2] round, and 100 years if you take Moore's Law into account)
}{{}{{}}{}{{{{}}}}{}{}{{}}{{}{}{}}}{}}}{}{{}}{}}{{}}{{}}{}}}{{{{{{}}}{}}{}}{}{{{}}{}{}}}}{}}}}}{{{}{{}{{}}}}{{}{{{}}}}}}{}{}}}}{
Note that a 15 character password, both cases, numbers, symbols, has a total keyspace less than 2^112.
*dictionary, in this context, means a cracking wordlist. For instance, the top 25 words (which will then have rules applied, rules like gravity... no, wait, rules like capitalization, 1337speak, appending dates, appending numbers, etc.) in the old, popular, and very small "phpbb" dictionary are
123456
password
phpbb
qwerty
12345
12345678
letmein
111111
1234
123456789
abc123
test
123123
123
monkey
dragon
trustno1
master
hello
1234567
computer
killer
000000
whatever
internet
September 12, 2013 at 12:02 pm
Separately, you need to use different passwords on each instance so that you're not worried about changing them. Work hard up front so that you're willing and ready to change one or more passwords at any point in time.
An example:
You set up a new SQL Server for a third party app the business bought, and wants it set up RIGHT NOW. The vendor insists you give them the "sa" username and password. You say "no". The vendor complains to the business. You lose the argument. You may even be out of the office when you lose the argument, so someone else needs to give them the "sa" password, and they fail to change it beforehand.
If you have one "sa" password everywhere, now the vendor definitely has access to everything you have, and you need to change every single sa password everywhere!
And now another vendor comes in, and...
September 13, 2013 at 1:33 am
Seems like the consensus is that if sa is to be left enabled (preferably not) then individual passwords should be used. These individual passwords should be long and strong and disposable (just like loo paper!).
Thanks all for your thoughts.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply