Transparent Data Encryption In SQL Azure

Introduction

Transparent Data Encryption is a newly introduced security measure in SQL Server. We apply various security measures such as secured code, firewall, hashing of data etc. to secure our database. Imagine a scenario, where the entire hard drive is stolen or cloned or MDF files are stolen, where it can be easily attached to another Server and the data can be accessed. To resolve this problem, Microsoft Introduced the Transparent data encryption feature. This feature allows us to encrypt the data at the page level without increasing the page size or the database size. Transparent Data Encryption uses AES-256 symmetric key called the database encryption key to encrypt the data. Every time, when the data is written to the file, it gets encrypted with the key. Whenever there is a read operation, the data gets decrypted.

Applying a TDE does not require any changes at the code level. The data is being encrypted and decrypted during read and write operation.

In this demo, we will try enabling TDE in SQL Azure database and then check whether the data has been encrypted or not.

First, you need to have a running SQL Azure database in your subscription.

Go to your SQL server settings, find your database, click all the setting and then click Transparent Data Encryption.



Click Data Encryption On Slider.

Click save on the top.

The message will be shown Encryption in progress... After the encryption completes, the status bar will change to Encrypted.



You can also use TSQL Command or PowerShell command to enable the Encryption.

Now, lets check, whether the encryption was successful or not, using TSQL.

Run this code. 

SELECT * FROM sys.databases;

You will see the results, given below:



It shows that the data base is encrypted.

Consequences

1. If you enable TDE, no code change is required as TDE works at Page level during read and write.
2. You can still export your data and restore it elsewhere, even if TDE is enabled.
3. If you are taking a backup of your database and restoring the database or attaching the MDF file in another Server, you need to copy the key from SQL server and use the key to restore the database in the new Server. This will be covered in another blog.