Thursday, December 22, 2016

Azure SQL Database - Enable TDE - Does it encrypt the bacpac file?

We use Transparent Data Encryption (TDE) for protecting our databases, making sure that no one can get our databases added to a different instance even though they manage to get either data files (.mdf) or backup files (.bak). TDE makes sure that data in data files is encrypted and it is called as encrypting data at rest. The standard way of enabling this is;
  1. Create a master key in master database.
  2. Create (or add an existing one from a copy) a certificate protected by the master key.
  3. Create a database encryption key protected by the certificate, in the database needs to be protected.
  4. Enable encryption in the database.

This is different with Azure SQL Database. Azure allows us to enable TDE with Azure SQL Database. It does not require steps like above, it can be simply enabled using the portal.


Although we do not create the certificate and the database encryption key manually, the storage of the database is encrypted using a symmetric key that is protected by a built-in server certificate in the SQL Server
.
Not only that, the main exception comparing with TDE-Enabled On-Premises database is, copying this database from the portal to local environment. If the database is not an Azure SQL Database and if you take a backup of it, you will not be able to restore it in another instance without certificate created for TDE. But if the database is an Azure SQL Database, when you export it to a bacpac file, it can be loaded to any instance without the Azure SQL Server level Certificate used to protect it. See below images;

1. Exporting the bacpac to classic storage.


2. Loading the database to on-premises server using Import Data-tier application... wizard.


3. Getting the database successfully added.



The reason for this is, when exporting a TDE-Enabled Azure SQL Database, the bacpac file is not getting encrypted, hence we need to make sure that it is secured.

No comments: