• Dana Epp

How to quickly detect if your SQL Azure databases are encrypted at rest

So I was recently discussing how to best protect data at rest in Azure with an IT pro from a major bank in the US. There was a misconception that he had that I thought should be cleared up when it comes to SQL Azure.

Mid last year Microsoft introduced the concept of "transparent data encryption" (TDE) for SQL Azure. This has been available to SQL on-premise for some time, and it was nice to see that we got this for the Microsoft Cloud. And using 'secure by default' principles Microsoft automatically turns TDE on when you create a new SQL Azure database. But this does NOT mean your existing databases are safe. Nor does it guarantee that TDE won't be turned off by your cloud admin or devops teams.

One of the key benefits of the public cloud is how easy it is to deploy and maintain new services like SQL. One of the key weaknesses of public cloud is that same benefit. It is far too easy to turn off technical safeguards like TDE that you believe is being used. It's a single bloody toggle in the Azure Portal!!

So here is quick tip on how you can quickly check if you have TDE on for your database. Run this Powershell command (assuming you have the Azure PowerShell module installed) :

(Get-AzureRmSqlDatabaseTransparentDataEncryption -ServerName <your_db_server_name> -ResourceGroup <your_resource_group> -DatabaseName <your_db_name>).State

It will return "Enabled" if TDE is on. It will return "Disabled" if it is not. Put that in script that you execute through Azure Automation once a day and have it alert you if it ever changes to Disabled!

NOTE: You will not see the "master" database encrypted on an Azure SQL instance. So if you do something crazy like iterate through all your databases on all your servers you should always see those return with TDE Disabled. That's OK. That is to be expected.