This particular question came up while I was delivering a workshop last week on the new features in SQL Server 2014. Since transparent data encryption has been available since SQL Server 2008, why would I still need the backup encryption feature?

For one, transparent data encryption is an Enterprise Edition-only feature whereas backup encryption exists in Standard Edition (I can stop right here and move on to the next topic of discussion.)  The price tag of an Enterprise Edition license is more than enough justification to consider using backup encryption. I’ve worked with customers in the past who had to use encryption utilities like TrueCrypt and GnuPG to encrypt their backups because they do not have Enterprise Edition. When customers have Enterprise Edition, my default recommendation is to use transparent data encryption.  I wrote an article back in 2008 about how we can implement transparent data encryption to provide encryption at rest as well as for the backups we take on the databases.

So, What’s The Real Difference?

Both TDE and backup encryption provides encryption – the former encrypting the MDF/NDF and LDF files together with the backups taken, the latter just the backups. That also means that, whether you use TDE or backup encryption, your database backups will be encrypted. But with backup encryption, only the backups are secured. Try detaching and re-attaching the database files to a different SQL Server instance and you can easily do so. TDE does not allow you to re-attach database files unless the target SQL Server instance has the certificate used to encrypt the database files. Below is a sample workflow and script to test it out (I’m *stealing* the code from the script in the article.)

1. Create a database master key on the master database[callout]USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'
[/callout]
2. Create a certificate protected by the master key[callout]CREATE CERTIFICATE NorthwindCert
WITH SUBJECT = 'My DEK Certificate for Northwind database' [/callout]
3. Create sample databases – one that is encrypted using TDE and one that isn’t

From here on, you can perform the test. We’ll use the Northwind_TDE database as the one that has TDE enabled and the Northwind_BackupEncryption as the one that does not have TDE enabled but backups will be encrypted.

[callout]--Create database encryption key in the database
USE Northwind_TDE
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE NorthwindCert
GO

–Enable TDE on the database
ALTER DATABASE Northwind_TDE
SET ENCRYPTION ON [/callout]

NOTE: Make sure you backup the encryption key immediately after enabling TDE on your databases or encrypting your backups. And, don’t forget to update your disaster recovery strategies.

Now, because the Northwind_TDE database has TDE enabled, you can’t just detach and re-attach it to any SQL Server instance without first restoring the certificate on the target instance. Doing so will throw this error message.

We won’t have this error message when you try to attach the Northwind_BackupEncryption database on a different SQL Server instance because it’s no different from any other user databases. Again, the only thing encrypted will be the backups, not the database. But this would give you the confidence to store your database backups on Microsoft Azure or any other cloud provider. Or, even better, when you need to transfer backups to initialize Availability Group replication, log shipping, database mirroring or replication for a remote data center. I used to rely on either WinZip or WinRar to compress and encrypt backups to initialize log shipping and database mirroring in previous versions of SQL Server. This meant submitting a separate change request just to install these utilities on the remote SQL Server instance. Now, I have both compression and encryption built into SQL Server for this purpose.

But What if I Only Have Backup Encryption?

So, you only have Standard Edition and are concerned about your databases being compromised. Sure, you can encrypt your backups, but what about someone with administrative privilege and malicious intent started detaching and copying those database files? It’s the same reason I wrote this blog post – I have the same concerns as you do.

This is where the principle of defense in-depth comes in. As a SQL Server DBA, it is our responsibility to make sure that our databases are secure. We need to provide layers upon layers of protection to minimize, if not avoid these types of security breaches. Limiting access to both the host operating system and the SQL Server instance, coupled with auditing access can prevent unauthorized access.

Practice Your Database Recovery Strategies

The beauty of this feature is that you can restore your databases to lower editions like Web or Express Editions. Although you have a database size limitation of 10 GB for Express Editions, it still is a good opportunity to test restoring databases from encrypted backups to make sure that we still meet our recovery objectives.

Additional Resources:

• SQL Server 2014 Backup Encryption
• SQL Server Transparent Data Encryption
• Implementing Transparent Data Encryption in SQL Server 2008 (still applies to later versions)