I’m introducing a new talk for 2016: Securing SQL Server. Here’s my abstract:
A default SQL Server installation is reasonably secure, but “reasonably secure” doesn’t cut it anymore in an era in which one bad line of code, one week password, or one open port can result in your customer database ending up on Pastebin. In this talk, we will look at different methods of securing a SQL Server instance, from venerable (principle of least privilege, Transparent Data Encryption) to novel (Always Encrypted, row-level security). These tools and techniques will show us ways for developers, database administrators, and network specialists to work together to secure corporate assets and prevent CISOs from ending up on the nightly news.
My plan is to hit on a few topics, introduce some resources to learn more about the topic, and move on. The goal is to provide an overview of some of the tools and technologies available, including:
- Applying the principle of least privilege to SQL Server logins, users, and roles.
- Implementing Transparent Data Encryption.
- Implementing Always Encrypted in SQL Server 2016.
- Implementing row-based security, both in SQL Server 2016 and prior to 2016.
- Using network segmentation to limit access to SQL Server instances.
- Writing good, parameterized SQL to prevent against SQL injection.
- Reducing the SQL Server surface area.
There are entire books on SQL Server security, so a one-hour talk can’t cover everything. Nonetheless, my intent with this talk is to give everybody who attends at least one new resource.
This review covers Dale Meredith’s Ethical Hacking: Reconnaissance/Footprinting. The material in this course follows pretty closely to the Certified Ethical Hacker material on the topic, and I think Meredith’s rendition has many of the same benefits and flaws that I found with the CEH literature.
The course is 3 1/2 hours long, so it might take a couple plane flights to get through this, and fortunately, it’s not the type of course where you need to be sitting in front of a computer typing away with the instructor to get anything out of it. Meredith covers topics at a fairly high level and then drills into tools and techniques for collecting information on a target. The primary emphasis of this course is looking at methods with no or low visibility to defenders, such as doing Google searches, researching employees through social media sites, looking at the public website, and finding ambient information from sources like job postings, news articles, and company blogs. This is recon, so we’re collecting information that we’ll use later in a penetration test. Meredith does show off some tools like WinHTTrack, and running that tool might raise the ire of a capable defender, but otherwise it’s smooth sailing for an attacker. Meredith finishes up the course discussing methods to mitigate exposure in public records.
I’m assuming that many of the people watching this course are preparing for the CEH, and I think Meredith tailored his material to the topic. The big downside to this is that the CEH hits you with lots of tools and techniques all at once, with relatively little focus on any of them. I think Meredith did a good job focusing on some of the most important of these (such as Googledorks), but it felt like there were too many things happening all at once in this course, and there were times in which I really wish we could have seen a deep dive on more techniques than just Googledorks. I hope that the later classes in this series offer much deeper looks at tools like nmap rather than a shotgun blast of software (which is how the CEH presents a lot of their material).
Also nice is that there’s support in Firefox and Chrome, meaning that I can get the same experience across both browsers. No IE/Edge support, though.
I had the pleasure of watching Troy Hunt go through a website security review with Lars Klint. This video definitely gets a 5-star rating from me because Troy walks through a step-by-step process, explaining to a developer with a relatively limited security background what the problems are, how you can trigger these problems, and—most importantly—how to fix these problems. He does all of this over the course of less than 2 hours, so it’s a quick watch.
If you want to see Troy do this in a lot more detail, check out his Hack Yourself First course on Pluralsight. That one features nearly 10 hours of content and goes into a lot more depth than this video.
Chris Bell of Water Ox Consulting has recently released sp_WOXCompliant, a tool which helps you check your instances for compliance. His first goal is to get STIG tests in place.
I ran it against a local instance I use in a VM. I haven’t put much effort into securing this instance for various reasons, so I wasn’t surprised that I ended up with 111 results.
These tests cover a range of scenarios. In the picture above, you can see that my data files are located on the C drive, that I have AdventureWorks installed, that I have not disabled the VIEW ANY DATABASE role, and that I still have an sa account, although it is disabled.
Other checks that I failed include:
- Databases are not encrypted using TDE.
- I am auditing failed logins only, and not successful logins.
- Attribute names are shared across entities but the data types are not the same (for example, two tables have columns named Name, but one is a varchar(30) and the other a varchar(50)).
- No asymmetric keys are available for encryption.
- SSIS, VSS, the SQL Server Browser, etc. are installed but may not be required.
- Certain SQL trace flags for auditing are missing, and I’m not using the equivalent Extended Events.
- My SQL Server installation is out of date; there are new updates.
All in all, this is a very interesting tool to run against environments, and as updates come in, the tool will get more valuable. Some of the findings are more theoretical (like services which are running) because the procedure has no way of telling if your documentation is up to date or if you really should be running those services, so you’ll never have 0 entries in the set. What we do get, however, is a solid checklist for things to look at, and over time, I expect this procedure to be one of my go-to installation tools on instances I manage.
Sean McCown has a fantastic blog post on how xp_cmdshell is safe by default and turning it on is not a security risk. I’ve seen auditors freak out when they see this on and have seen DBAs obstinately refuse to use any solution which requires shelling out. This is the wrong attitude to take, as McCown points out. The xp_cmdshell command is secure by default (requiring sysadmin access to run). Instead of freaking out about this, DBAs and managers should spend more time ensuring that service accounts follow the principle of least privilege, that the number of people with sysadmin be minimized, that the SQL Server servers are correctly network segmented, and all those other things which actually improve security posture.
Gizmodo had this interesting article today. I found it apropos because I had a conversation with my wife today about my PayPal account and a random e-mail I got from the company that I needed to reset my password because somebody had been monkeying around. (No worries, no money changed hands. I think it was probably because I hardly ever use the account.) Anyway, she asked if changing our password (which I did) would prevent us from being hacked. I told her “probably not.” I’m sure no hacker is dumb enough to target me on purpose, but a lot of these attacks are more like looting a grocery store. The chances of one individual egg being broken are pretty low, but when there’s so much smashing and grabbing, well, I wouldn’t get too attached to Eggbert.
I am curious as to our resident security expert’s take on the article.