Azure SQL Database – TDE

I work in the financial space so you can imagine that security is quite high on the agenda. TDE (Transparent Data Encryption – see this article for more details https://blobeater.wordpress.com/category/sql-server-2/tde/ ) is nothing new, setting it up on “earthed” SQL Servers can have some what of an overhead but in the Azure world it is so simple to setup.

The big advantage of TDE in Azure over the earthed flavour is that Microsoft does alot of the work for you. Assuming your database is in a geo-replication partnership it will be protected by a different key on each of the servers. Also Microsoft will rotate your certificate at least every 90 days.

Azure SQL Database does not support Azure Key Vault integration with TDE. SQL Server running on an Azure virtual machine (IaaS) can use an asymmetric key from the Key Vault.

Within the settings section of your Azure SQL Database you will find the TDE option as shown below. To do this successfully you will need to be connected as the Azure Owner, Contributor, or SQL Security Manager.

enc1

Select ON for the data encryption option and click save.

enc2

Seriously, that is all it takes.

I will then move to SSMS (SQL Server Management Studio) and issue the following query to understand the status. (Credit to David Pless: TSP – Microsoft)

 SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0' THEN 'No database encryption key present, no encryption'
         WHEN '1' THEN 'Unencrypted'
         WHEN '2' THEN 'Encryption in progress'
         WHEN '3' THEN 'Encrypted'
 WHEN '4' THEN 'Key change in progress'
 WHEN '5' THEN 'Decryption in progress'
 WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed'
         ELSE 'No Status'
      END,
percent_complete, create_date, key_algorithm, key_length,
encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

enc3

Naturally tempdb gets encrypted too along with the user database. Azure uses AES-256 bit algorithm.

These sorts of activities get tracked and logged by the Activity log within Azure.

enc4