Introduction

Transparent Data Encryption (TDE) is a security feature of SQL Server that has been around since SQL Server 2008. With TDE, the entire database is encrypted (also called "at-rest" encryption). With a non-TDE database, if someone were to steal the physical media that contained a particular database, the person could then just attach that database to another SQL Server and browse the data in the databases. TDE gets around this problem by encrypting the database using a database encryption key (DEK). Databases protected with TDE can only be used on server with their DEK.

TDE in Azure SQL Database

Until recently, this type of security has not been available in Azure SQL Database. Now, TDE is available on all Azure V12 SQL Databases, and it is very easy to implement. In order to add TDE to an Azure SQL Database, just execute the T-SQL shown below.

Figure 1 - Enable TDE

Figure 2 - sys.dm_database_encryption_keys

One thing to remember...

While TDE in Azure SQL Database does encrypt "the database, associated backups, and transaction log files at rest without requiring changes to the application" (from Transparent Data Encryption with Azure SQL Database), the one thing that isn't encrypted is any .bacpac files that are created from the database. This means that if someone were to be able to crack the username and password for your Azure SQL Administration account, they could export the database to create a .bacpac file, and then do an "Import Data Tier Application" on an on Premise version of SQL Server to browse the data. I have to say that I was a bit surprised by this. I think that the hacking of usernames and passwords will be a very common way to compromise Azure SQL Database, and I was hoping that TDE would help to mitigate this risk.