| description | Export and import databases using Always Encrypted | |
|---|---|---|
| title | Export and import databases using Always Encrypted | Microsoft Docs | |
| ms.custom | ||
| ms.date | 10/30/2019 | |
| ms.prod | sql | |
| ms.reviewer | vanto | |
| ms.technology | security | |
| ms.topic | conceptual | |
| helpviewer_keywords |
|
|
| ms.assetid | 29816a41-f105-4414-8be1-070675d62e84 | |
| author | jaszymas | |
| ms.author | jaszymas | |
| monikerRange | =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
This article describes how to export and import databases containing columns protected with Always Encrypted.
When you export a database, all data stored in encrypted columns is retrieved from the database in the encrypted form (ciphertext) and put into the resulting BACPAC. The resulting BACPAC also contains the metadata for Always Encrypted keys.
When you import the BACPAC into a database, the encrypted data from the BACPAC is loaded into the database and Always Encrypted key metadata is re-created.
If you have an application that is configured to query encrypted columns stored in the source database (the one you exported), you don't need to do anything special to enable the application to query the encrypted data in the target database, as the keys in both databases are the same.
For detailed information about how to export and import a database, see:
- Export a Data-tier Application
- Import a BACPAC File to Create a New User Database
- Export an Azure SQL database to a BACPAC file
- Import a BACPAC file to a database in Azure SQL Database
- SqlPackage.exe
You need ALTER ANY COLUMN MASTER KEY and ALTER ANY COLUMN ENCRYPTION KEY on the source database. You need ALTER ANY COLUMN MASTER KEY, ALTER ANY COLUMN ENCRYPTION KEY, VIEW ANY COLUMN MASTER KEY DEFINITION, and VIEW ANY COLUMN ENCRYPTION DEFINITION on the target database.
You do not need to have access to column master keys configured for the encrypted columns, as the data stays encrypted during the export and import operations.