Well, that was interesting
While playing around with demos to use in my newer session, Hacking Exposé Too (See it at Summit 2015 or during the 24HOP Summit Preview next week!), I stumbled across a fun demo that one could call the poor man’s encryption for backups.
It was quite simple to do, and if you are running a version of SQL Server after 2008 you already have this capability. Yes, even in Standard edition and it is just a simple option to turn it on.
No keys or anything fancy like that.
Here is a shot of the location of the switch to turn it on as well as a script to enable it as well.
The code:
EXEC sys.sp_configure N'backup compression default', N'1' GO RECONFIGURE WITH OVERRIDE GO
“But All You Did Was Turn On SQL Backup Compression”
BINGO! That is all I did.
Here’s the deal.
By enabling the compression of backups, not only do you save space (sometimes) on your storage, you also scramble the information in the backup file itself.
This is a nice extra bonus if someone is trying to get your data, without using SQL Server.
Here’s a sample of what a page of data looks like in your actual normal backup file. (*Assuming you are not using backup compression, or TDE)
No, my wife does not work at Adventure Works.
You can get this using a hex editor like I did, or you can even open a backup file in MS Word or a text editor and search for the plain text.
Yes, it is all stored in plain text unless you do something about it.
Want proof? Here’s a screenshot from a hex editor looking at a page in a simple .bak file.
The Poor Man’s Encryption
Why does compression look like encryption, but doesn’t really protect the data that much?
When we compress the data in the backup, we are essentially looking for patterns and de-duplicating them. The values are then replacing in the file with values that are much smaller and a bunch of pointers to indicate what the actual value would be.
When you restore the database to a SQL Server, it knows the algorithm used and puts everything back to normal on your SQL Server.
So, to make it easier to see, here is what the same page from the backup file might look like after compression was applied.
I say look like, since I couldn’t find the page in the compressed data.
Looks a LOT like encrypted data doesn’t it? That’s kinda cool.
This Is Not Encryption!
No, it isn’t. It is the poor man’s way to encrypt data because it really isn’t safe.
If someone gets a hold of your backup, and has SQL Server (get Enterpise edition free for 6 months here) , they can grab that compressed backup and just restore it.
You still need to have the file encrypted in some way. You can use SQL backup encryption, or TDE, or even a 3rd party tool.
If you don’t have those options, and want to get something in place to prevent basic file scanners from pulling raw data straight out of your SQL Server backup files, you can just compress the file. Oh, and while you’re at it, probably save some space on your drive so your managers can then used saved budget from your storage project to buy you a better version of SQL Server so you can encrypt your data properly.
[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]
Chris Bell is an independent SQL Server consultant, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services, training and the FREE sp_WOxCompliant compliance check script for your SQL Server environment.
Click here to contact Chris or to follow him on twitter. [/icon_box]