| title | Migrate data to or from columns using Always Encrypted with SQL Server Import and Export Wizard | Microsoft Docs | |||
|---|---|---|---|---|
| ms.custom | ||||
| ms.date | 06/26/2019 | |||
| ms.prod | sql | |||
| ms.reviewer | vanto | |||
| ms.technology | security | |||
| ms.topic | conceptual | |||
| f1_keywords |
|
|||
| helpviewer_keywords |
|
|||
| ms.assetid | 29816a41-f105-4414-8be1-070675d62e84 | |||
| author | jaszymas | |||
| ms.author | jaszymas | |||
| monikerRange | =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]
The SQL Server Import and Export Wizard is a tool that allows you to copy data from a source to a destination. This document describes how to use the SQL Server Import and Export Wizard if a source or/and a destination is a SQL Server database containing columns protected with Always Encrypted.
With the SQL Server Import and Export Wizard, you can implement the following scenarios for migrating data to or/and from encrypted columns.
If your data source contains plaintext data and your destination is a SQL Server database containing encrypted columns, you can use the SQL Server Import and Export Wizard to retrieve the plaintext data from the source, encrypt it and copy the encrypted data (ciphertext) to the encrypted columns in the destination database. For this migration scenario, the data source can be any data store the SQL Server Import and Export Wizard supports, for example a file, a SQL Server database or a database in another database system.
To ensure the SQL Server Import and Export Wizard can encrypt data, you need to enable Always Encrypted for the destination database connection and you need to have access to the keys protecting the data in the destination database columns. For details, see Enable and disable Always Encrypted for a database connection and Permissions for encrypting or decrypting data during migration.
If you are migrating data stored in encrypted database columns in a SQL Server database, you can configure the SQL Server Import and Export Wizard to decrypt the data and copy the decrypted (plaintext) data to a destination, which can be any data store the SQL Server Import and Export Wizard supports, for example a file, a SQL Server database or a database in another database system.
To ensure the SQL Server Import and Export Wizard can decrypt data, you need to enable Always Encrypted for the source database connection and you need to have access to the keys protecting the data in the source database columns. For details, see Enable and disable Always Encrypted for a database connection and Permissions for encrypting or decrypting data during migration.
If you are copying the data from encrypted columns in a source SQL Server database to encrypted columns in the same or another SQL Server database, you can configure the SQL Server Import and Export Wizard to decrypt the data after retrieving it from the source and re-encrypt it before inserting into the encrypted columns in the destination database. Use this method if the schema of the target columns (for example, column data types, encryption types and column encryption keys) are different than the schema of the source columns.
To ensure the SQL Server Import and Export Wizard can encrypt and decrypt data, you need to enable Always Encrypted for both the source database connection and the destination database connection, and you need to have access to the keys protecting the data in both the source and the target database columns. For details, see Enable and disable Always Encrypted for a database connection and Permissions for encrypting or decrypting data during migration.
If you are copying the data from encrypted columns in a source SQL Server database to encrypted columns in the same or another SQL Server database, and the target columns use exactly the schema (including the same data types, encryption types and column encryption keys) as the source columns, you can configure the SQL Server Import and Export Wizard to retrieve ciphertext from the source columns and insert the encrypted data (ciphertext) into encrypted column in the target SQL Server database.
For this scenario, you can use any data provider that supports SQL Server to connect to the source or the destination SQL Server database. If you are using a provider that supports Always Encrypted to connect to the destination database, you need to make sure Always Encrypted is disabled for the database connection. For details, see Enable and disable Always Encrypted for a database connection.
You also need to ensure, the database principal (user), the SQL Server Import and Export Wizard uses to connect to the destination database, is configured with the ALLOW_ENCRYPTED_VALUE_MODIFICATIONS option set to ON. This option suppresses cryptographic metadata checks on the server in bulk copy operations, which enables the wizard to bulk insert the encrypted data to the destination database, without decrypting the data. For details, see Bulk Load Encrypted Data to Columns Protected by Always Encrypted.
If your migration scenario requires the SQL Server Import and Export Wizard is able to encrypt or/and decrypt data, you need to configure the source SQL Server database connection or/and the destination SQL Server database connection using a data provider that supports Always Encrypted and you need to enable Always Encrypted for the source or/and destination database connection.
You can use any data provider for a connection if you do not need the wizard to encrypt or decrypt data on that connection.
The following data providers in the SQL Server Import and Export Wizard support Always Encrypted.
- .NET Framework Data Provider for SQL Server
- Make sure the machine, on which the wizard runs, uses .NET Framework 4.6.1 or later.
- To enable Always Encrypted for a connection, set
Column Encryption SettingtoEnabledin the connection properties. To disable Always Encrypted, setColumn Encryption SettingtoDisabled. For details, see Connect to SQL Server with the .NET Framework Data Provider for SQL Server and Enabling Always Encrypted for Application Queries.
- .NET Framework Data Provider for ODBC.
- You need install Microsoft ODBC Driver 13.1 or later.
- To enable Always Encrypted for a connection, set
Column EncryptiontoEnabledin the connection properties. To disable Always Encrypted, setColumn EncryptiontoDisabled. For details, see Connect to SQL Server with the ODBC driver for SQL Server and Enabling Always Encrypted in an ODBC Application.
To encrypt or decrypt data stored in a SQL Server source or destination database, you need the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions in the source database.
You also need access to column master keys, configured for the columns, storing data you're encrypting or decrypting:
- Certificate Store - Local computer - you must have the Read access to the certificate that is used a column master key, or be the administrator on the computer.
- Azure Key Vault - you need the get, unwrapKey, and verify permissions on the vault containing the column master key.
- Key Store Provider (CNG) - the required permission and credentials, you might be prompted for when using a key store or a key, depend on the store and the KSP configuration.
- Cryptographic Service Provider (CAPI) - the required permission and credentials, you might be prompted for when using a key store or a key, depend on the store and the CSP configuration. For more information, see Create and Store Column Master Keys (Always Encrypted).