Transparent Data Encryption in Azure SQL Database

Transparent Data Encryption in Azure SQL Database
JULY 26, 2015

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
You can also enable TDE using the Azure Preview Portal or using PowerShell, which is explained in detail in this post. In order to check the progress of the encryption, you can use the DMV sys.dm_database_encryption_keys as shown below.
Figure 2 - sys.dm_database_encryption_keys
As you can see from the results above, the database has an encryption_state of 3, which means that the database is encrypted, and percent_complete is 100 so the encryption has completed successfully.

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.

Add new comment