Slides and Code for SSIG Talk

Thank you for those who made it out to the SQL Server Innovators Guild last night in Greenville, SC. I hope you enjoyed the talk and that it’ll create conversations about how we better secure the ETL pipeline. With attacks against data becoming more and more prevalent, I only see this area growing in concern, especially as we understand that attackers will get through the perimeter or are already there (like the life change issues we talked about).

 

ZIP file: ETL_Pipeline_Security_Slides_Code.zip (585 KB)

Advertisements

Speaking on ETL Security

I will be giving a presentation on ETL (Extract, Transform, Load) security at two user groups in the coming weeks.

Securing the ETL Pipeline

We’re going to look at typical ETL (Extract, Transform, Load) pipelines and consider the weak points an attacker might go after. Our goal in this isn’t to cause FUD (Fear, Uncertainty, and Doubt), but to discuss risks at each point, options for protecting the vulnerability, and what we’ve seen typically done (if anything). While this talk primarily focuses on Microsoft SQL Server, especially the database engine and SSIS, many of the points covered will be applicable to any solution set.

If you’re in or near the Midlands or Upstate of SC, I’d love for you to come out so we can meet and discuss this topic, professional development, and SQL Server in general:

August 5, 2014 – SQL Server Innovators Guild – Greenville, SC

August 14, 2014 – Midlands PASS Chapter – Columbia, SC

Minimize permissions for file locations

When we talk about security, we often point to the Point of Least Privilege. I write a lot about applying this to SQL Server, but it’s important to handle this outside of SQL Server, especially at the file / share level. Why would we care about this as DBAs / DB Pros? Here are a few good reasons:

Too Much Read Access:

By having these locations exposed with greater than required read access, it means folks can potentially get to the data and abuse it. You have an information disclosure / data exposure issue. It’s not just about trusting the individual. Anyone can fall prey to a malicious email which then deploys malware onto the system. From there the attacker hops, using the credentials of said user. Therefore, it’s important to lock down read access as much as possible.

Too Much Write Access:

This one is more of a concern with regards to ETL processes as there is typically minimal validation done on the files. For instance, do you perform an MD5 hash check on the files you might import into SQL Server? Therefore, if someone understands what’s in the files, it would be trivial to undermine the contents therein. This is why reconciliation checks and the like are so important. But even they can be beat.

Another problem, though, is that if someone has write access in both production and development, a write intended for development can happen in production. If it’s not caught, you could have a big problem with respect to that ETL process. Again, this isn’t a trust issue. All it takes is someone who has too much work to do (that describes just about everyone in IT), hasn’t had enough sleep lately (ditto), or someone who is distracted for some other reason.

Typically, when someone has write access, that person also has delete access. Therefore, if someone wanted to be malicious, you can imagine the kind of damage that could be done if they deleted the snapshot replication files or the backup files. The same is true for the ETL files, especially ones where there are some cycles turning to produce them or the files that are the result of manual processes (like where Excel spreadsheets are filled out and then imported).

The Bottom Line:

That’s why we have the Principle of Least Privilege. Applying it, regardless of your full trust in your personnel, is important. And as I described to someone recently, people change. Someone who is trustworthy but who is certainly underwater with respect to finances could consider committing an act that he or she would normally avoid. How fast can this happen? Divorce, unexpected medical bills, being ripped off, totaling a brand new car, etc., are all ways that a person’s finances can suddenly do a 180. Therefore, seek to lock down anywhere files are used as part of any of your processes.

Speaking at Midlands PASS Chapter tonight

The Midlands PASS Chapter is an official PASS (Professional Association for SQL Server) chapter located in Columbia, SC. It’s free to attend our meetings, which are typically held the 2nd Thursday of each month.

Once a year we like to do an open forum on SQL Server security. It’s typically held in February, but was postponed due to the inclement weather. Therefore, we’re holding the open forum tonight, March 13, 2014, from 5:30 to 7:00 PM. The first part of every meeting is meet and greet to give folks time to network. Then we settle in for a presentation, or in this case, a forum discussion.

The SQL Server security open forum is as it sounds: folks are free to bring up whatever they want to with regards to SQL Server security and as a community we’ll try to take things apart and come up with the best answer. While I may focus on SQL Server security, I don’t have all the answers. None of us do. That’s why a few years ago we went to this more free form discussion.

If you’re in the area and would like to attend, please drop on by. We meet at Microstaff IT in Cayce, SC at 440 Knox Abbot Drive (tower with Bank of America logo on the top). If you look on Google Maps, the address is marked wrong. Google Maps is pointing at the shopping center right next to the tower, but the parking lots are connected.

Security #Datachat on Twitter Tonight

Tonight, at 9 PM Eastern, I’ll be participating in a #datachat on SQL Server security. It’s sponsored by Confio (now part of Solarwinds).

You can find more details about the #datachat here.

How can you participate? Simply open up a search for #datachat and participate in the community Q and A. The more, the merrier!

I hope to see you online.

What If Someone Tampered with the Process?

I’m a big fan of automation. Automation means I can do more. Automation means I eliminate the mundane stuff to focus on critical things. I like automation as an IT professional.

However, as a security professional, a question that is ever present in my mind is,

“What if someone tampered with the process?”

Case in point: you have an automated process to build VMs. That includes configuring particular security groups for a particular type of build in the local Administrators group (you should already be doing some of this with group policy, but that is automation as well). What if an attacker was able to slip into the automation to include a particular account or a particular group? How long would it be before you caught it?

This is why I’m a big believer in a human putting eyes on automation results at some point and relatively frequently at that. In fact, I’m a big believer in multiple levels of verification. Maybe it’s my military background and things like the two person rule. If you’ve watched a movie like Crimson Tide you’ve seen it in action. Two people have keys that must be used together. This ensures that one person, acting alone, can’t do something devastating (in a relative sense).

I know there’s a balance to be met. Too much manual effort and you undo the benefits of automation. However, too much reliance on automation and you’re eventually going to miss something.

Recording of SQL Injection Webcast Now Available

On Tuesday I gave a webcast along with MSSQLTips on SQL Injection. If you were unable to attend (or were able to attend and want to see it again), you can view it at the following link [registration required]:

SQL Injection: What it is, how it happens and how to stop it?

I was asked about the slides and scripts. You can find them as a download here:

SQL Injection Presentation Materials