In the efforts to protect sensitive data there are some considerations that expand beyond its storage within database; more specifically, the backup files that are generated through the database backup process. Backup files are often kept on devices or media that are removed from the database server and stored in a separate location. If not properly secured these devices can be stealthily snatched away, exposing the backup files contained within.

If you were to open a backup file in a program like Notepad, your first impression is that the file contains a bunch of garbley-gunk resulting in the false perception that any plain text sensitive data in the backup file cannot be disclosed by simply scanning the raw backup file. In reality, the data in the database that is stored in plain text is not modified and appears in the backup files as plain text.

To see this for yourself, obtain a plain text value that has not been encrypted or hashed in your database, such as a credit card number or Social Security Number. Open your most recent backup file in an editor like Notepad, Wordpad or Textpad and perform a search for that value. You will discover that the sensitive data that is in plain text within the database also is in plain text in the backup file.

Microsoft SQL Server 2008, Enterprise Edition, introduced Transparent Data Encryption (TDE)  as a new feature that was designed to address this issue. This feature encrypts the physical files of the database. This includes any backup files that are generated through the native backup functionality.

The Database Encryption Key (DEK) is a key that was introduced to the encryption key hierarchy in support of the TDE feature. The DEK encrypts the physical files using one of the following algorithms:

• Advanced Encryption Standard (AES) using key length options of 128, 192 or 256 bit.
• Triple DES 3 Key which is an expanded key length version of Triple DES using a 192 bit key.

For a more in depth exploration of TDE and backup/recovery, check out my blog entry with the title "TDE: Under The Hood With Backup".

For those who do not have Microsoft SQL Server 2008, Enterprise Edition, or do not wish to implement TDE, the need to protect your database backup files remains. There are many third-party backup products available on the market that provide encryption to the backup files that they generate. Three popular options are:

LiteSpeed for SQL Server (Quest Software: Price not published)
This product encrypts backup files using:

• Advanced Encryption Standard (AES) which uses a 256 bit key.

The encryption feature of this product has been available for many versions. The current version of this product is 5.1 which added features such as the ability to define when to perform a differential or full backup, support for SQL Server 2000/2005 Standard Edition, native log shipping conversion and more. For more details on the latest release check out Quest Software's release information page.

SQL Safe Backup (Idera: $1,195 - estimated)
This product offers the user a choice of the algorithm used to protect the backup files. The available options are:

• Data Encryption Standard (DES) which uses a 64 bit key. (not recommended)
• Triple DES which uses a 168 bit key.
• RC2 which uses a 128 bit key.
• Advanced Encryption Standard (AES) which uses a 256 bit key.

The AES 256 bit key encryption option is new with its current version, 5.0, which became available on June 29, 2009. This version also provides log shipping as new functionality. For more details on the latest version of this product, check out Idera's "What's New" page.

SQL Backup Pro (Red-Gate Software: $795 - estimated)
This product encrypts backup files using:

• Advanced Encryption Standard (AES) algorithm with an option of 128 or 256 bit key length.

The encryption features of this product have existed since its initial release. Version 4 introduced the 256 bit key option. At the time of this blog entry the current version is 5.4. Version 6.0 is slated for release this Summer. This new release will include features such as handling of network outages during backup, "self-healing" log shipping, advanced compression algorithm and much more. For more details check out Red-Gate's fact sheet for the release of version 6.0.