Encrypting SQL Server 2005 Backups
Posted on July 11, 2008
4 Comments
There just doesn’t seem to be an elegant way to encrypt SQL Server 2005 backups. It certainly isn’t a built-in option and I’ve been spending time this week trying to think up something “elegant” to accomplish this.
Allow me to share today’s journey with you.
First Take
Many moons ago I didn’t bother with encrypting my database backups. I was more interested in compressing them since I keep them offsite and have to copy them down a VPN from the data centers.
However, a year or two ago I realized that it would be a good idea to drop some encryption into the mix since the storage location is accessible from our office LAN (assuming you know where to look…)
I cobbled up a little batch file that looks like this:
@echo off
for %%A in (*.bak) do 7z a -t7z -mx=9 -mmt=4 -pbigLongHairyKey %%A.zip %%A
for %%A in (*.bak) do del /q %%A
I just run it (via the Windows Job Scheduler) in each directory that contains backup files. It loops through all the backups and uses 7-zip to compress and encrypt each file. Not too shabby, eh? You gotta be old to remember how to do for loops in batch files… 
Of course, you see the big downside to this I bet. Sure, my backups are indeed encrypted. However, if anyone’s bright enough to locate and look in the batch file… well, they’ll have the key.
Second Take
I figured that if I wrote a very simple VB program to do what the batch file does, I could at least stop exposing that key so readily to notepad. However, just calling it via a shell does me no good. Anyone with access to the box could see what processes are running. For instance, doesn’t Process Monitor show command line arguments for a process? (if it doesn’t, I bet something does.)
That idea got scrapped pretty quickly.
While mulling over this option, I also considered just changing the advanced properties on the batch file. If I were to log on as the batch process owner account and hit the “encrypt” box on that one file’s properties dialog, only that account would be able to edit or view the contents of the batch file. That works quite well.
However, my concern about being able to get command line arguments for a running process put the kibosh on that notion.
Third Take
Red Gate makes a nifty little utility called SQL Backup that claims to make life better. Among other things it offers some “native” encryption and compression options. I’ve had good luck with some other Red Gate products, so this seems worth investigating.
I grabbed the 14 day trial earlier today and installed it. However, I didn’t get very far before I had to send a note into their support for clarification on the licensing. See, you license this by “server”, but in this particular context I’m not sure if a server is the machine running SQL Backup or an installed SQL server. If the former, this is a relatively inexpensive option. If the latter, I can’t even begin to afford to license it for my dozen database servers (over $6000?).
So, this option is on hold while I await clarification. I’m pretty sure I can guess the answer though…
Fourth Take
Now I’m back to the coding option, ala “second take”. Instead of just shelling out to call 7-zip from my program I could acquire a library for similar functionality (I’m looking at the Kellerman Software Encryption library).
I’m generally pleased with how fast 7-zip works. I have to confess that I worry that I’ll end up with a library that’s “dog-slow” when encrypting a couple of my larger database backups.
Fifth Take?
Aside from coding something up, I’m not sure how else to approach this and am definitely open to ideas. I’ll be doing some more web searches this weekend, but I’ve yet to stumble over anything wonderful.
Tags: 7-Zip, backup, database, encryption, Red-Gate, sql-server
Possibly Related Posts
Comments
4 Responses to “Encrypting SQL Server 2005 Backups”
Leave a Reply
Hi,
Hopefully our support team have got back to you by now but just in case SQL Backup is licensed per machine not per SQL Server instance. So if you have four or five instances running on a single machine then you’ll only need one license.
If you need any more help finding the best licensing option for you then please feel free to contact our sales team (sales@red-gate.com).
James
–
James Moore
Red Gate Software Ltd
Hi,
There is another technology worth mentioning here, HyperBac. HyperBac provides transparent AES-256 but certificate based data encryption (as well as high performance compression) for SQL Server 2005 backups. Unlike other third party methods which require you to change your backup commands (to use extended stored procs, etc), HyperBac works seamlessly with your native TSQL BACKUP statements or existing database maintenance plans. HyperBac was developed by founding developers of Quest LiteSpeed, it is licensed per server as well and is typically less expensive than other available third party solutions.
@James - I haven’t heard back yet. But, to be fair, I contacted the person on the automated “Thanks for grabbing the trial” email. It seemed like a good idea at the time…
But if I understand your response (server = sql server, not machine running SQL Backup) than it’s a moot point. I can’t afford your software. :-\
@Jeffrey - Thanks for the note and pitch for HyperBac. I’ll have a look next week. Hopefully there’s a trial version?
Hi Chris,
Sorry if I wasn’t clear. Server = machine not per SQL Server. So if you have 2 machines running 6 instances of SQL Server you need 2 licenses.
James