SQL Server Security Audits – A Confession

When clients need help, bugs require patching and production deadlines loom large, it’s easy to think that cutting a few corners is the “right thing” to do. I’ve done it, and I’m willing to bet that many of you have as well. Doing so puts the security of your company’s data at risk and makes each audit review that much more painstaking in the future. SQL server audits are no different.

Sure, complying with each request from your friendly auditor doing a SQL Server security audit may feel tedious and time-consuming, and you may be sure that your auditor is enacting some Kafkaesque torture upon you (go read the Trial, a classic and you’ll see what I mean.), but those seemingly extraneous details serve a crucial purpose.

Here’s my confession – I actually like those auditors and their findings. Even though it gives me great pain to say it, here are just six reasons ways those technical auditors are right. You should respect their suggestions, even if your industry doesn’t require the “fun” of an annual audit. These are sadly 6 still too common audit findings, why you should care about them and how you can make it better. 

Authorized Users

Common sense dictates that only authorized personnel or system admins have database access, but the problem of authorization persists. The reality is, it barely takes a simple line of SQL code (or mere seconds) for someone with access to delete an entire database or even several databases. The action doesn’t even have to be malicious or intentional. A new employee could accidentally erase thousands of rows of valuable client or company data. We all saw this it happen in a hosting gone wrong situation I blogged about earlier this year, and it is not pretty. As the DBA or database manager, you should implement strict limits on who has system admin access, for both the standard database and the backup.

Some Suggestions here:

1. Review who has SA level access TODAY. Do they need it?
2. Review who has Local admin access TODAY. Do they need it?
3. For the really paranoid – Do even your most privileged users need that level of acccess all the time? Now I say this as a SQL Server consultant, it helps and it matters. But there are many tools out there like Secret Server from Thycotic and vendors like them which integrated with AD and allow you to temporarily elevate an account’s status or check out an admin level account, use it and then the checked out PW expires. At least there are steps – audited steps – to get the access.

Self-Auditing

Monitoring who accesses the company’s database and when they did gives you a more accurate picture of what’s going on. It also makes your auditor’s job, and the auditing process itself, a lot easier. (BTW, this means your job is easier if you are the DBA or the DBA manager. Trust me. As someone who is terrible with receipts, it’s so much more stressful looking for them on October 14th or after an auditor sent you a note… If you had it ahead of time? Life is easier all around) And, should something happen (not really a question of if, but when), you should be able to quickly and easily access records about who was in the system at the time of the incident, even if you followed the Authorization advice above, but especially if you didn’t!

You should track both failed and successful login attempts. If a hacker does manage to get into your SQL Server database(s), they are more likely to have done so through the “front-door” so to speak, since guessing or brute forcing a login password is a lot easier than trying to hack an entire system. Of course, numerous failed login attempts are also a strong indication that someone is trying to steal your data. Performing a SQL Server Health Check can help you figure out what steps you need to take to protect your company and client data.

Suggestions:

1. Look to failed login auditing – At the very least, audit these and read your logs. Or get a tool to scrape them and review them like Splunk or SumoLogic. Look for patterns and trends.
2. Really consider successful login auditing – You may be in an industry that requires it. You can offload your logs and back them up. You can AND SHOULD create more than the default 6 error logs and recycle more frequently. You can scrape the windows login logs also and save those. (we look at your error log setup when we do health checks. Why? Because it makes life easier for troubleshooting to have them rightly configured , but it saves you time at the audit)

For Crying Out Loud – Use Windows Authentication

Just because something is simple doesn’t mean it’s right. Yes, SQL Server comes with its own authentication system; however, auditors strongly recommend using Windows authentication over SQL Server. It’s sort of like a pre-filled out finding on their forms already. Windows authentication is the more secure choice because it uses the Active Directory environment, which is already likely controlled by strict policies and is responsible for authenticating all users on your network. This approach means that Windows checks to see who is accessing the database and when, the user’s permission level, and whether or not the attempt was successful. You can complete this simple yet effective security step via T-SQL or through the SQL Server Management Studio in a matter of minutes. Sure. You have Great Plains or the application from one of the myriad software vendors (including, sadly, some of my favorite) which require it. So – we can work on that with our $$$ by pushing our vendors to look to other approaches. And we can still work around it with password policies, running checks for bad passwords, white hat hack attempts, careful auditing and logging and doing the steps like following least privilege.

Suggestion: Just do it. If you have an app which has no way around it, make them use strong passwords, don’t give that app account with the dumb name and weak password the keys to your entire kingdom. And if you don’t have one of those apps? Rejoice and create your first SQL Server without SQL authentication.

Encryption

So here’s the deal. I’ve heard the discussions. I know the arguments. “No one is going to walk into the data center and take a physical drive, and if they did – how would they even take the right drives and do something with them?” You know I agree. I also know that Transparent Data Encryption has some weak points and points of exploit discussed out there. But you know what? I also know that the deadbolt on my house is really not going to keep a hardened criminal intent on taking what they want out of my house. But when the insurance company comes by after they break in? They are going to ask the police “well were there any signs of forced entry?” If the answer is “no, looks like they just sleep with their doors and ground floor windows open, and no floodlights, no security system” – my insurance company may reach deep into the 1.5pt font of my contract and agreement and say “well you didn’t protect yourself. So we’ll be capping our coverage to this amount”.

TDE won’t stop everyone. But it does two things – it stops many. It stops accidental thieves. It makes you a more difficult target. And if something bad ever did happen? It’s easier to explain “well here are the 18 layers of protection we had.. of course, we did the relatively inexpensive TDE, of course we encrypted our traffic where we could!”.  When it comes to security – don’t think of “but this is stupid and I’m smarter than that and those auditors are making my life hard!”  Instead – think of the press release. Think of what you would testify to in court or what you would tell your customers. It’s no consolation to say “Well, we didn’t believe in these industry-recognized, often audited measures, because they aren’t enough or because the threat was low” when your data is in someone else’s hands. It’s far beter to show the booklet of protections you’ve taken.

But don’t just put your toe in that pond. Think about going the extra mile:

1. Rotate your keys – Doesn’t help to have a key that’s 10 years old. You can rotate the cert on top of it all without re-encrypting your data as frequently as you like.
2. Don’t decrypt data and move it around – I don’t know how often I see that. How often I see the “ForDev_Decryption” database name out there. Why bother. If your encrypted data will live anywhere in your environment decrypted, why bother encrypting it?
3. Don’t put your keys everywhere – From years being on the fire department and ambulance, I see some of the “clever” places folks leave keys out there. Don’t do that. Don’t set up every single SQL Server as a place that can handle your encrypted data. The more places your certs live, the more places your keys and private keys are known, the more chances you have of losing control of this all.
4. Don’t forget about restores –This is a little bonus tip. Save backups for 7 years for financial reasons? Well if you encrypt your backups or your DBs – and you rotate keys, don’t forget about the ability to restore that DB in 6 years!

Kroll found that data breaches cost companies an average of $5.9 million in 2014. That was less than I expected, and I bet the numbers are worse in 2017 already. A LOT worse with Equifax.

Best Practices For Developers (And Us DBA Types)

In my experience, there are far too many companies with production code that hasn’t gone through a proper code review. Yes, developers usually have a lot on their plates, but that’s no excuse to skip critical best practices. DBAs need to stand firm on this point: code that is deployed to production without review can provide backdoors and bugs that make company databases and client information susceptible to hacking. If you’re wary of the pushback from developers, just be sure to mention Equifax, the latest data breach in U.S. history. More than 150 million people had their credit card numbers, home addresses and bank information exposed, all thanks to a known Apache bug that never got fixed.

But, hey, DBAs and DBA managers, we aren’t off the hook either. I cannot tell you how many basic misses I find doing SQL Server Health checks. At great companies with amazing DBAs – I still find them. We’re all busy. We are doing 4,598 things at the same time for our internal and external customers. Misses happen – but when we start missing out on best practices, we could also be opening up security or auditing holes that will bite us later. Check your servers, check the code that gets put on them.

Stay Current on Updates

And it isn’t just the DBAs and developers. System administrators – in 2017, there’s no excuse for your company OS or software applications not to be up-to-date. In fact, both the FCC and the FTC, as well as other tech-related sites, keep published reports of the latest malware and virus vulnerabilities. Staying current on OS and software updates is by far one of the most straightforward measures a database or systems manager can implement, and it significantly reduces the chances of compromised data. I have lost count of the number of servers I connect to that show me that “windows update is lonely and it sure wishes you looked at these cool updates sometime in the past 2 years” dialog box (or whatever it is 😉 ). Seriously – this is like TDE. Imagine the press release or trial.

“System administrator person, please tell me when you last patched windows on that server?”
“Ummm”
“You mean to tell me you didn’t update for WannaCry?”
“Umm”
“What about this exploit that was well documented?”
“Umm”

Going through an audit isn’t fun. But the reality is, it’s necessary. Getting your company in line with SQL Server auditor recommendations won’t just make the auditor’s job more straightforward, it makes your client’s data more secure and means you are proactive to protect your company’s most treasured assets – the data. It’s all about the data (of course, I may be just a wee bit biased).

P.S. – The Trial really is a great book. Not sure which translation I’ve read and re-read. But check it out. And no, auditors, I really don’t find your accusations the same. I actually secretly love what you bring to a SQL Server environment. And like a parent who threatens their kids with “you better behave or someone will get mad!” At a restaurant, I often write in my health check reports “If I were a software security auditor, this would be a significant finding” – so I really understand the vial role you play. 

P.P.S – Wow. You’ve read all the way to here. THANKS! – Leave a comment. What did I miss? I could have easily covered 29 things here. Things like or xp_cmdshell, or CLR, or auditing in SQL Server, or even the ones people laugh at like default ports  etc. etc. What is on your list? It’s confession time. C’mon – we really all know deep down inside that the auditors are right. Right?