By: Tim Wiseman | Updated: 2013-01-24 | Comments (6) | Related: > Backup
Problem
Someone can bypass most of the permissions-based limitations on viewing data from a SQL instance by restoring a backup to an instance where they have system administrator privileges. One way to help prevent that from happening is to ensure that the backups are encrypted. In this tip we will be looking at a few open source encryption options for protecting database backup files.
Solution
Why consider an open source solution?
There are also several excellent solutions for encrypting SQL Server backups, but there are several reasons to consider using an open source solution, at least under certain circumstances:
- Price - Many third party products which provide encryption are highly affordable and well worth their price. However, for certain cost sensitive operations, the fact that the open source solutions considered here are free can be a significant fact. This can also be significant if there are numerous copies of a database which will have backups taken, such as when developers have their own local copies of the database and backups.
- Ability to distribute - Many open source products allow distribution within the terms of their license. So, as long as the terms of the license are followed, you widely use it within the business or even distribute it to customers.
- Flexibility - There are several open source options to consider, each with their own advantages and disadvantages in different situations. With several options, it is easier to find exactly the solution that works best in the target environment and easily switch to a different solution if needed.
TrueCrypt
TrueCrypt is a versatile encryption packages that permits either the encryption of an entire partition or the creation of a virtual disk inside a file that is protected by encryption. It is governed by the TrueCrypt License and also supports advanced features such as tools to create hidden volumes or use keyfiles along with passwords.
While unlocked and mounted, TrueCrypt files are treated by the operating system essentially like a disk partition and thus they are vulnerable to someone with the appropriate operating system permissions copying the backup files out of them. But this provides excellent security against someone physically stealing the hard drive with a copy of the backups on it. Additionally, the TrueCrypt volume can be mounted and unmounted as often as necessary to minimize this risk, and since this can be done from a command line, that can be scripted and automated.
So, with a TrueCrypt volume created, you could create a PowerShell script or batch file to open the TrueCrypt volume, make the backup, and then dismount the TrueCrypt volume. Of course, one problem with this is that it stores the password as part of the script, or requires it to be supplied at the command line. So if this is to be automated, the script itself needs to be protected from disclosure or the password may be compromised. A very simple example PowerShell script, without any error checking and assuming would look like:
#Open the TrueCrypt Volume #The Pipe to Out-Null makes sure it waits until TrueCrypt is done before proceeding. & 'C:\Program Files\TrueCrypt\TrueCrypt.exe' /v E:\AllMedia\dbs\TrueCryptTest.tc /l T /p ThisIsABadPassword /q | Out-Null #Open the connection and make the backup $SQLConn = New-Object System.Data.SQLClient.SQLConnection $SQLConn.ConnectionSTring = "Server=TIMWISEMAN-PC\SQLExpress12; Trusted_Connection=True" $SQLConn.Open() $SQLcmd = $SQLConn.CreateCommand() $SQLcmd.CommandText = "Backup Database HddDb To Disk = 'T:\testHddDb.bak'" $SQLcmd.Executenonquery() #dismount the TrueCrypt Volume & 'C:\Program Files\TrueCrypt\TrueCrypt.exe' /d T /q | Out-Null
GnuPG
GnuPG is a sophisticated, flexible, cross platform encryption system licensed under the GPL. GPG4Win provides a suite of tools for using GnuPG on a Windows based system. One of its more common usage scenarios is to encrypt and sign e-mail messages and it comes with a number of tools to help with this. But a valuable attribute to a DBA trying to secure backups is that, unlike with TrueCrypt and similar systems, it supports asymmetric, or Public Key, encryption.
With public key encryption, two keys are generated. One key is used to encrypt files, and can be shared widely or even made public. And the other keys is used to decrypt files and should be limited only to people authorized to decrypt the files. So, it is possible to encrypt a file without ever directly using the key that would decrypt the file. This means that you can use GPG in a script and make the script widely readable without worrying that this will compromise the encryption for the backups.
The first step to using GPG is to create the pair of keys it needs to work:
Once that is done, the actual encryption can be handled from the command line, and so it can be scripted. A command line encryption looks like:
gpg --recipient "TimothyAWiseman" --output "E:\AllMedia\dbs\HddDbBackup.gpg" --encrypt "E:\AllMedia\dbs\HddDbBackup.bak"
That command line, crucially, does not need to include a password, though it does assume that the recipient is listed on the standard keyring. The password only needs to be supplied when the file is decrypted, so the encryption process can be fully automated without risking exposure of the password. While it is possible to create the SQL Server backup directly into a preexisting TrueCrypt file, GPG works on files that already exist, meaning that simply encrypting the backup with GPG will leave an unencrypted copy of the backup available. One way to deal with this is to use a secure deletion program like Sdelete by Mark Russinovich to remove the original backup after the encrypted copy has been made.
Limitations
Although encrypting SQL Server's backups can help provide additional security from certain types of threats, it does not provide perfect protection. Obviously, encrypting the backups does not help against any attacks which involve going directly against the live instance of SQL Server. Nor will it provide any protection against an internal threat that legitimately has access to the passwords and decides to abuse them.
Further, while genuinely cracking strong encryption is extremely difficult, there are a number of techniques that can be used to bypass encryption in some instances. Whole disk encryption, for instance, can be circumvented through techniques such as an "Evil Maid Attack" or a cold boot attack. Similarly, social engineering attacks have been successful at times in getting passwords, as have the use of keyloggers in some circumstances. In short, encryption in general and encrypting the backups in particular is an excellent tool in providing additional security, but it should be only one tool in a comprehensive security plan.
Next Steps
- Safegaurd the encryption keys. If they are compromised, the encryption will not help. If they are lost or forgotten, the encryption will make the encrypted files worthless.
- Tom's Hardware has a Review of TrueCrypt 6.1.
- There is an editorial by Steve Jones about the significance of encryption.
- MSDN has an article on Database Encryption in SQL Server 2008 Enterprise Edition.
- Implementing Transparent Data Encryption in SQL Server 2008 covers implementing TDE which also provides some protection for backups made of that database, but at a performance cost.
- There are other tips related to encryption here.
About the author
Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-01-24
