Recently I attended a local SQL user group presentation where one of the speakers was presenting key features of SQL Server installations. One of the points mentioned was security. He detailed removing or renaming sa, windows only authentication, not using port 1433, password policy, Kerberos, sql agent proxy accounts; the list can be infinite.
In the past I have had clients who unfortunately use applications that really poor in terms of SQL Server security. So here’s the scenario – and what I am sure is very familiar to freelance SQL Consultants and DBAs:
“Can we go with Windows Authentication?”
CTO: “No, that will break the application.”
“Can we remove the sa account it runs under?”
“No, that will also break the application.”
“Dynamic ports?”
“No, has be 1433; the app is hardcoded that way”.
I could go on with more issues like this but the answer is always the same, “No, it will break the application”.
This is the where the politics of security come into play, immediately you’re working on the back foot in that from a SQL Server security perspective, there isn’t much you can do to without breaking the application. Sure, you can shore up the rest of the SQL Server installation with best practices, method of least privilege, etc., but it seems like putting out a small fire whilst you ignore your house burning down behind you. Brent Ozar touches on these points in his consultant blogs, but sometimes what you end up with is a SQL Server production box, running with so many security holes it resembles Internet Explorer.
But we inform the CTO of the potential risk, however because the application is so fundamental to the business there isn’t much you can do, save covering your back and emailing the risks to as many people as possible should the worst happen. You can try to look for other ways of hardening the production environment, limiting the number of sysadmin accounts, removing as much access from Production as possible.
Another client there was an issue with an existing third party windows account (used by more than one person) that when checked, had read/write access to every AD group ever created. This account also has sysadmin access along with every single server role ticked! Look familiar?
So, after removing the sysadmin privileges, you find yourself in meetings trying to explain why a number of financial reports now do not run. It is well documented how to get SQL Server security wrong but then in my example the account in question belongs to a college buddy of the CEO, so woe betide anyone that stops his sa access to the databases so they can run reports on the OLTP production boxes and hey, new DBA guy, can you find out why users are experiencing a slow down at that time?
At this point I would like to make an impassioned plea to application designers at this point: Please please please stop your product going to RTM with the ‘sa’ account hardcoded in it just because it was easier to code that way.
I would love to hear from anyone else who managed to solve these type of issues. Did you take the CTO for a drink and plead with him? Or when the system started slowing down/became hacked did you sit back and paint a huge sign on your monitor – I TOLD YOU SO?
