Encrypting SQL Server backups with open source tools

By:   |   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:

Kleopatra File Menu
 Kelopatra Certificate Creation Wizard

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Wiseman 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



Comments For This Article




Sunday, August 20, 2017 - 7:22:48 AM - Maher Back To Top (65080)

How to protact sql mdf file from XCopy by admin and Client User?

Once I create SQL database mdf file in root database user in virtual VeraCrypt Volume Local Disk M:\
Thats actuly accessaible from PC- father administrator or User just in mount Volume.and Not accessaible SQL connection to database in Dismounted.even SQLExpress Services is Runing.
because the instance root directory is on local OS C:\
so mdf database file is NOT prtacted from XCopy PC- user and admin.
so if the admin is the owner of software and database I can make access deny proviladge of user from volume or file or folder security.
but that can be easly proken to the password for father admin from the user he has password of his VeraCrypt OS Volume by using hiering boot.
Even VeraCrypt Encrypt of the entier OS Volume.
so admin proviladge .. gone.
and SQL manager 'sa' Password is Not important because XCopy of mdf File moved to another Server.
now SQL.mdf file is NOT protacted at all. just from user and admin.
- so how can I protact SQL mdf file. 3rd party.
Not by TDE Transparent db Level because only enterprise Edition have this Feature with Cost.
and NOT by SQL Express 2016 adv.
Allows encrypted because that is only for db Colume Level.
I Need any way to give me Stong Security Protaction of database for free.3rd Party level.
and NOT by DBDefence or auther.
Thank you..

What I have tried:

1- VeraCrypt for 
File Encryption and OS Encryption.
2- Security admin Proviladge.
3- DBDefence.
4- Allowes Encrypted sql Express Adv 2016.


Thursday, August 17, 2017 - 1:27:34 PM - Maher Back To Top (64970)

Hi.

I just don this tip with VeraCrypt and mounted Volume using Batch File.and backup sql dstabase successfuly.

The question is how to protact the oreginal mdf file of database with open connection to dstabase. Imean..once the SQL Service running Atumatic Start As LOIN NT AUTHORITY NETWORK SERVICES. at mounted.

Once hidden Volume or dismounted the connection of sql instance cant be generated and not access.

Until mounted the Volume. So now volume and root directory of sql DATA server is visable to the user.

so Sql database.mdf file is Not Protacted.

Ijust want to keep the instance is opend connection with dismount volume or hidden volume.

Please.. help me with SQL Express.


Monday, October 17, 2016 - 9:42:57 PM - Nick Bedford Back To Top (43581)

I use this exact technique for my website database backups, especially since they contain customer and/or patient information.

I have scheduled mysqldump shell scripts which write the database to a .SQL text file, compresses that script to greatly reduce its size then uses GPG to encrypt the ZIP file over into an autosyncing Dropbox folder.

This is then synced onto a workstation in the office where automatic backups will also copy those encrypted backups to other backup destinations. And thanks to PKI, none of the web servers need to store the associated private key for the encrypted files.

That at the very least ensures that anyone with access to the Dropbox account cannot do a thing with those encrypted MySQL dumps.

 


Thursday, November 20, 2014 - 6:13:37 PM - TimothyAWiseman Back To Top (35361)

Chris, thank you for pointing that out.  I am a fan of TDE.  However, it is not available in all editions of SQL Server.  There is a good FAQ that goes over it at http://sqlmag.com/sql-server/transparent-data-encryption-faqs 

 


Thursday, November 20, 2014 - 10:01:59 AM - Chris Higgs Back To Top (35354)

You can also use TDE, Transparent Data Encryption in sql server.  http://msdn.microsoft.com/en-us/library/bb934049.aspx

 


Monday, June 2, 2014 - 1:49:41 PM - TimothyAWiseman Back To Top (32043)

As an update, there have been recent announcements from the somewhat secretive developers of truecrypt that it may not be fully secure, though some observers suggest this may be as simple as the developers warning that the project will no longer be updated.  A full security audit is currently ongoing.















get free sql tips
agree to terms