Export always encrypted data from Azure SQL

Question

Here are the environment details:

I've one Azure SQL Databases V12 and another local DB SQL Server 2016. I've enabled Always Encrypted on a table in the first DB - DB1. The same table structure is also created and i need to migrate the encrypted data from first DB to second DB.

When I try to export data from DB1 to DB2 I am getting error

"Creating destination for Encrypted source is not supported".

The error is because the CMK and CEK that were used to encrypt DB 1 is in Azure-Key-vault. The export tool never gave an option to access key-vault.

How do I migrate the data from DB1 to DB2?

Answer 1

@G_Tania: which import/export tool, are you trying to use? I understand, you want to use a local certificate in your target database (not the column master key stored in Azure Key Vault), correct? Do you also want to replace the column encryption key (CEK), or do you want to keep the original CEK?

In general, there are two kinds of out-of-the-box tools that support migrating databases using Always Encrypted, each of which supports different scenarios:

• Bacpac-based tools – please, see https://learn.microsoft.com/en-us/azure/sql-database/sql-database-export. Such tools generate a bacpac file that contains encrypted data and original key metadata. The data remains encrypted throughout the migration process and when you import the file to your target server, the key metadata will point to the original keys (the master key in Azure Key Vault, in your case). If you want to use different keys in the target database, you still can use bacpac-based tools, but after importing the database, you will need to rotate your keys. Please, see https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/overview-of-key-management-for-always-encrypted.
• SSIS-based tools, such as the Import/Export Wizard that ships with SSMS: https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard. These tools are useful if you want to migrate the data and replace the keys in one step. The process for migrating and encrypting data using the I/E Wizard is described here: https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/. The process for re-encrypting the data will be similar – you need to make sure that Always Encrypted is enabled for both the source and the target connection. In addition, since your master key is stored in Azure Key Vault, you need to use the ODBC 13.1 driver (at least for your source database connection). Unlike ADO.NET, ODBC allows you to specify Key Vault credentials in the connection string. Please, see “Using the Azure Key Vault Provider” in https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver.