Logging on as SA. It Never Fails

From books on-line:

System administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely. Instead, make system administrators members of the sysadmin fixed server role, and have them log on using their own logins. Use sa only when there is no other way to log in to an instance of Microsoft® SQL Server™ (for example, when other system administrators are unavailable or have forgotten their passwords).

Everytime I teach a SQL Server course, I tell about the horrors of having an application logging in as sa.  I tell them that if you must run in mixed mode, don't assign sa a password, assign it a passsentence.  You've got 128 letters, use them all.  How about "This is my sa pâssword!  Every time I use sa I will do 100 pushups so I can beat up geeks who tell me not to use it!@#$%^&*()[]{}"

I think that's a pretty safe password.  120+ characters, numbers, symbols, mixed case and a little international flava thrown in too.  You'd need one dedicated individual to launch a brute force attack on that password (my estimate is that it would take about 1.1e267 tries to guess it with a brute for attack).

Anyways, every single time I mention this in a SQL Server course, at least one person (sometimes two or three) tells me that some accounting package or cash register system requires them to login with the sa account.  And even worse, the password will ALWAYS be one of three things: password, admin or a blank password.  So they always try to change it to something more secure, and the app will basically implode on itself because it requires the sa account with that password because they've hard coded it like that.  As Charlie Brown would say, "ARGHHHHH."

Ok, if you are one of the people who have no idea why this is a bad idea, let me break it down for you (if you are a database guru (or even just not security-impaired individual) you might want to stop reading because this will probably be boring to you (unless you like laughing at the misfortune of others)).  This is all kinds of bad-times.  For a quick security review, remember that writing a secure app is like defending a castle.  You have to defend all possible ways in.  An attacker only has to find one weak spot.  The more layers of security, the easier it will be for you to defend.

SQL Injection Attacks.
Steven Livingston broke it down nicely in a past post, so I'm not going to cover it again.  You can use Google to search for SQL Injection for more information.  Anyways, I get “we do security through the user interface” quite a bit.  Well that's great.  Just make sure that you have bullet proof validation code.  And pray that your newly hired intern that you've got working on some trivial reporting aspect of the app also writes bullet proof code.  Oh, and stored procedures will not always save you.  Anyone ever seen a stored procedure that has code that looks like this: "exec ('SELECT FirstName, LastName from Employees WHERE City IN (' + @CityList + ')'"?  You and I may be security experts, but all it takes is someone else on our team who was up late drinking Martinis and playing Prince of Persia to come in a introduce a whole slew of bad times.  For more SQL Injection fun, Ted Neward has a really nice post on SQL Injection attacks and sillyness vendors are guilty of.

Brute Force Attack
Guess what SQL Server doesn't do?  Lock out accounts after x amount of bad logins.  You can try as many password combinations as you want.  And the best part?  You already know the username!  Yea haw!  In 10 minutes, I wrote an application that scans the network looking for SQL Servers, and tries 2000 common admin passwords.  The hardest part was finding a text file with passwords and remembering how to read in textfiles (XML, how you spoil me).  For a single server, it can do all 2000 passwords in less than 10 seconds.  Guess what...it can find “admin” as a password pretty easily. 

“Who cares, we are behind a firewall!” you say?  Guess what?  You guessed it, most attacks come from within the firewall.  All it takes is one sales person to run an attachment that launches this attack.  Block exes, scrs, cmds, bats, .pifs and whatever executables as attachments?  Great!  But all it takes is one person to write an old school virus that attaches itself to a floppy disk, or someone gets it via an attachment from their hotmail account and it's hamster huey and the big kablooie time. 

Right-click, open with Notepad
You can view connection strings from notepad you know.  You can view any string really.  Take a look at this program, creatively called Strings.  A little search for password= would work nicely.  I'd be amazed that if people logging into SQL Server with an sa account could spell DES, let alone know to encrypt connection strings.

The scary:

XP_CMDSHELL
This is what always gets people a little nervous.  In class, I tend to format partitions from a text box in Visual Basic using XP_CMDSHELL.  I'll also create domain users with a password of my choosing.  All kinds of fun stuff.  Give me access to XP_CMDSHELL and you've given me the world (well, at least the local computer and perhaps the network).  And you just know that people that login to SQL Server as sa also give SQL Server domain admin permissions...

Subtle Data Changes
Someone dropping a database or formatting a hard drive doesn't scare me.  We've got backup tapes.  What scares me is someone writing code that makes subtle changes.  Perhaps flipping numbers in address or phone numbers.  Deleting a character from an e-mail address.  Adding tiny amounts to purchases or inventory levels.  Things that might not be figured out for week, way past when our backup tapes get rotated back into circulation.

So, how do you solve this?  First of all, don't log on as sa, dummy.  If you need to do some "admin" like tasks, break them off into a separate application.  And even then make sure you need the sa level of security.  I'd create a separate login with "backup operator" privileges, or whatever else is needed.  I'd also HIGHLY recommend not even using SQL Server authentication.  Windows Auth is default for a reason.  It's not that hard to use, in fact you might even find it easier (and for the love of god, don't require people to log in as Administrator to use the app too!!!!!).