Using Encryption To Protect Your Data From Alien Abduction

Yes, I’m continuing the alien theme for my series on encryption.  It seems to fit well, and I just like the little green alien dude.

I’m going to start the series by discussing the use of encryption to prevent your data from being abducted, or stolen, while “at rest”.  When referring to data as being “at rest”, typically we’re talking about data in a database file that is not currently mounted by a SQL Server instance, or data that is contained within a SQL Server backup file.

SQL Server has a fairly elaborate security layer built-in.  Data access can be limited to specific users or groups, right down to the column level (or even row-level with a little bit of effort).  None of this applies, however, if the data isn’t being accessed through SQL Server.

Let’s assume aliens are after your data.  One night they beam into your datacenter, shut down SQL Server, copy the data files onto a USB drive, restart SQL Server, then fly off into the Milky Way.  You would never know that your data has been stolen.  Fortunately, most of us don’t have anything in our databases that aliens would be interested in.  Our threats are more Earth-bound.

For example, consider the case of a disgruntled administrator who is unhappy with the company and is planning to leave.  During a system maintenance outage he decides to steal a copy of the company’s customer and sales database.  He plugs an external hard drive into the server, shuts down SQL Server, copies the MDF and LDF files onto the external drive, then starts SQL up again.  Because this was a maintenance outage, shutting down SQL Server raises no suspicions.  The admin walks out with his external hard drive, plugs the drive into his home computer, where he’s running an instance of SQL Server.  He attaches the MDF and LDF files that he copied from the company server, and within minutes, it browsing through all of the customer and sales data that the company has stored in their database.

How could this have been prevented?  Depending on the version of SQL Server that you’re using, here are some of the options available:

SQL Server 2000/2005/2008
There is nothing built-in to SQL Server 2000, 2005, or the standard edition of 2008 to help prevent the theft of an at-rest database.  There are third-party options, using software such as NetLib’s Encryptionizer.  Software of this type works by encrypting the physical database files (the MDF and LDF files), using your choice of several different encryption algorithms.  Typically there is an encryption “key” (or password) provided by an administrator at the time of encryption.  Once the data files have been encrypted, that key is required in order to decrypt the files for use.  The decryption software runs as a driver, residing between SQL Server and the physical data files.  All of SQL Server’s I/O requests are handled by this driver, decrypting data as it is read (or encrypting in the case of writes) by SQL Server.  Encryption software of this sort usually offers protection for backups as well.  Encryptionizer, for example, protects files based on the file extension.  It can be configured to protect all *.MDF, *.LDF, *.BAK, and *.TRN files, among others.  Assuming your database backups are written to files using the BAK or TRN file extensions, those backups will be encrypted and protected.


  • The encrypted data files cannot be read without the original encryption key and a copy of the encryption software.  With proper checks and balances in place (the DBA doesn’t have the encryption key or access to the encryption software), this method offers a fair amount of protection against the “rogue admin” scenario.  Auditors call this “seperation of duties” – your system admin controls the software and the encryption key, the DBA controls the mounting of the databases.
  • Encryption and decryption of the database is transparent.  Users and applications that have logins to SQL Server don’t have to do anything additional to gain access to the data – everything works just like it does on an unencrypted database.


  • The work required to decrypt or encrypt data as it is accessed adds overhead.  Typically this overhead is minimal, 3-5% over “normal”.  On an appropriately sized server, the performance impact of this overhead is unnoticed.  On an already overloaded server, the performance impact could be significant.  
  • A determined admin can create native, unencrypted database backups by circumventing the encryption software.  As I mentioned above, Encryptionizer can be configured to protect certain file extensions.  Assuming the BAK file extension is one of those protected, a DBA merely has to write a backup to a different file extension, “DBBackup.BKP” for instance.
  • Loss of the original encryption key potentially means a loss of the entire database.  Without the encryption key, the encrypted data is inaccessible.  PROTECT THE KEY!
  • Data written to TEMPDB is unencrypted.
SQL Server 2008 Enterprise/2008 R2
The enterprise edition of SQL Server 2008, and all versions of SQL Server 2008 R2 offer built-in whole-database encryption (known as TDE, transparent database encryption).  Similar to the third-party solutions for earlier versions of SQL Server, data is decrypted/encrypted as it is read/written to disk.  Unlike those third-party solutions, there is no master encryption key.  Instead, each encrypted database has its own encryption key, which is protected by a server master key and certificate (read more on MSDN).  To facilitate “seperation of duties” and to help protect against a rogue DBA, the server master key and certificate can be created by a server admin, and the individual database keys by the DBA.  Additionally, backups of a TDE-protected database are also encrypted.  In fact, it is not possible to create an unencrypted backup of a TDE-protected database.


  • Allows for true seperation of duties
  • Backups are also encrypted, cannot be used to circumvent encryption
  • No third-party tools required


  • As with the third-party options, there is overhead to the encryption/decryption of data.  
  • Backups of encrypted databases do not compress well, if at all, negating benefits of another SQL 2008 feature, backup compression.
  • Encrypted database cannot take advantage of Instant File Initialization
That pretty much sums up your options for whole-database encryption.  Next up, we’ll dig into column-level encryption, when to use it, why to use it, and how it differs from whole-database encryption.