This quick write up comes courtesy of a tweet by Jonathan Kehayias (@SQLSarg) yesterday morning (Please Note: OH means overheard, meaning that statement is something Jonathan overheard. He’s WAYYYYY too smart to actually spread something that dumb as valid advice) . Here’s the tweet:
Ok, so I’ve seen a couple of stupid things written up in the last few days but this one just might take the cake. If you have been a DBA for any amount of time then you’ve more than likely come across a vendor application that uses the ‘sa’ account for access to the database. I won’t get into details about the sa (or system administrator) account here but check out this article by Ken Johnson at SQLServerCentral.com about it (check out the discussion thread as well to learn more).
So what exactly is wrong with that statement in the tweet? Well, as stated by Jeff Smith (@hillbillyToad) this morning:
“Ok Jorge, stop making fun of me”. No, as long as you access things using sa for “simplicity” or “optimization” I’m going to beat this over your head like an Acme mallet. Using ‘sa’ account for everything is akin to being handed the keys to the bank and being told “yeah, go ahead and make your deposits and withdrawals from your own account but try not to touch anything else while you’re digging around the vault”. Seriously, I’m not kidding. Handing someone the ‘sa’ account is handing them the keys to your SQL kingdom. Think about it, if you write an application that is accessing your database with FULL admin rights, what if someone performs a SQL injection attack and drops your production tables for kicks?
Listen folks, I know that security can be a pain but it’s there for a reason. Don’t get lazy and just assume the user needs an admin account to access the database because 9/10 times it doesn’t. You could probably get by fine on creating a new schema with write/read access and maybe EXECUTE stored procedures permissions. In fact, secure yourself from SQL injection attacks by wrapping your code in stored procedures in the first place.
There’s a ton of resources out there to learn how to properly secure SQL Server. Get up to speed by reading up about Security and Protection on MSDN. There’s also tons of videos and demonstrations out there. Check out the Quest Pain of the Week webcast on SQL Injection courtesy of Brian Kelley (@kbriankelley) and Kevin Kline (@kekline). Finally (WARNING: Blatent self-promotion inbound) make sure to check out SQL University’s security week from Semester 1. Bottom line is if someone tells you “this application needs to run as sa”, have them give you a detailed explanation as to why. Part of your job as a responsible DBA is to protect your data and your database servers. If they simply don’t know any better then offer to educate them on schemas, security groups, etc. And remember, “because its an optimization” is a stupid answer.
I had an MS app that required both System Admin and for the computer account to be placed in the admin group of the SQL Server, (SCCM) WTF, MS?
Ha, yeah Joey, even MS isn’t immune to stupid practices. One trick is to run a SQL (server-side) trace while the application is running and see EXACTLY what its doing. If its just a bunch of SELECT statements and not much else, then you can take it to the dev and go “look, you don’t need admin rights to do this”.
I’ve had more than one experience with a certain very large company that makes hardware and monitoring software where they have stated “give the account sa access”. When asked what permissions were actually needed they didn’t know, it was not documented and they would have to open a ticket against the engineering team to find out.
A couple of traces later and they needed to look at two DMV’s and have a user in each database, nothing more.
It’s funny what a simple trace can dig up. Well done!
We had a guy tell us this and we got pressure from the Execs so I just renamed the real sa account and created a new account with read/write privileges named sa. They never knew the difference and the app worked just fine.
lmao that’s AWESOME, well played!
There are many moments that I have to decide what battles are worth fighting. That surely wasn’t one of them when I could have my cake and eat it too.
LOL, this is funny. I (fairly) recently had to analyze some poorly performing SQL statements…guess which account I was given to login as? I could feel the power!
What do they do when it comes time to change passwords because some one was asked to leave their job etc? I had a nightmare of a project a few years ago where the whole development department knew sa, the link servers were configured to use sa, the jobs were encryted using dtsrun which was using sa, OS batch files were passing parameters using sa and in some cases the sa was even hard coded into the apps themselves! It is a real nightmare to fix believe me….especially when you have to start changing passwords on a regular basis due to some new SOX or Auditing requirements. I totally agree that assigning sa rights is a no no and should be avoided at all costs. Good Post.
Thanks John. One practice that many do with security passwords that are vital like the sa account is to write it down on a piece of paper, put it in a sealed envelope and keep it somewhere safe. For us we have dedicated IT Security team so I’d hand the envelope to them. In your office if you don’t have dedicated security folks then just make sure that envelope stays in a safe place. If you’d like to come up with a secure random password, check out Ron Dameron’s PowerShell article on how to generate a random password with only two lines of PoSH: http://ronalddameron.blogspot.com/2009/09/two-lines-of-powershell-random.html