使用 Always Encrypted 将加密数据批量加载到列中Bulk load encrypted data to columns using Always Encrypted

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

若要在大容量复制操作期间加载加密数据,而不用在服务器上执行元数据检查,请使用 ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 选项创建用户。 **** To load encrypted data without performing metadata checks on the server during bulk copy operations, create the user with the **ALLOW_ENCRYPTED_VALUE_MODIFICATIONS** option. 此选项旨在由旧式工具使用,或者由不能使用 Always Encrypted 的第三方提取-转换-加载 (ETL) 工作流使用。This option is intended to be used by legacy tools or third-party Extract-Transform-Load (ETL) workflows that cannot use Always Encrypted. 这使用户可以安全地将加密数据从包含加密列的一组表中移动到具有加密列的另一组表中(在相同或不同的数据库中)。This allows a user to securely move encrypted data from one set of tables, containing encrypted columns, to another set of tables with encrypted columns (in the same or a different database).

ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 选项The ALLOW_ENCRYPTED_VALUE_MODIFICATIONS Option

CREATE USERALTER USER 都具有 ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 选项。Both CREATE USER and ALTER USER have an ALLOW_ENCRYPTED_VALUE_MODIFICATIONS option. 当设置为“开”时(默认为“关”),此选项将取消在大容量复制操作中,在服务器上进行的加密元数据检查,以便用户可以在表或数据库之间大容量复制加密数据,而无需解密数据。When set to ON (the default is OFF), this option suppresses cryptographic metadata checks on the server in bulk copy operations, which enables the user to bulk copy encrypted data between tables or databases, without decrypting the data.

数据迁移方案Data Migration Scenarios

下表显示了适用于多个迁移方案的建议设置。The following table shows the recommended settings appropriate for several migration scenarios.

always-encrypted-migrationalways-encrypted-migration

加密数据的大容量加载Bulk Loading of Encrypted Data

使用以下过程加载加密数据。Use the following process to load encrypted data.

  1. 对于作为大容量复制操作目标的数据库中的用户,请将该选项设置为“开”。Set the option to ON for the user in the database that is the target for the bulk copy operation. 例如:For example:
 ALTER USER Bob WITH ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = ON;  
  1. 运行大容量复制的应用程序或作为该用户进行连接的工具。Run your bulk copy application or tool connecting as that user. (如果应用程序使用启用了始终加密的客户端驱动程序,请确保数据源的连接字符串不包含 column encryption setting=enabled ,以确保从加密列进行检索的数据始终处于加密状态。(If your application uses an Always Encrypted enabled client driver, make sure the connection string for the data source does not contain column encryption setting=enabled to ensure the data retrieved from encrypted columns remains encrypted. 有关详细信息,请参阅使用 Always Encrypted 开发应用程序。)For more information, see Develop Applications using Always Encrypted.)

  2. 将 ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 选项设置回“关”。Set the ALLOW_ENCRYPTED_VALUE_MODIFICATIONS option back to OFF. 例如:For example:

    ALTER USER Bob WITH ALLOW_ENCRYPTED_VALUE_MODIFICATIONS = OFF;  
    

潜在的数据损坏Potential for Data Corruption

错误使用此选项可能导致数据损坏。Improper use of this option can lead to data corruption. ALLOW_ENCRYPTED_VALUE_MODIFICATIONS 选项使用户可以将任何数据插入数据库中的加密列,包括使用不同密钥进行加密的数据、加密不正确的数据,或根本未加密的数据。The ALLOW_ENCRYPTED_VALUE_MODIFICATIONS option allows the user to insert any data into encrypted columns in the database, including data that is encrypted with different keys, incorrectly encrypted, or not encrypted at all. 如果用户意外复制了使用为目标列设置的加密方案(列加密密钥、算法、加密类型)进行不正确加密的的数据,则将无法解密数据(数据将损坏)。If the user accidently copies the data that is not correctly encrypted using the encryption scheme (column encryption key, algorithm, encryption type) set up for the target column, you will not be able to decrypt the data (the data will be corrupted). 请慎用此选项,因为它将导致数据库中的数据损坏。This option must be used carefully, as it can lead to corrupting data in the database.

下面的方案演示了因错误导入数据而导致数据损坏的方法:The following scenario demonstrates how improperly importing data could lead to data corruption:

  1. 用户的该选项设置为“开”。The option is set to ON for a user.

  2. 用户运行连接到数据库的应用程序。The user runs the application that connects to the database. 应用程序使用大容量 API 将纯文本值插入到加密列中。The application uses bulk APIs to insert plain text values to encrypted columns. 应用程序需要启用了始终加密的客户端驱动程序对插入的数据进行加密。The application expects an Always Encrypted-enabled client driver to encrypt the data on insert. 但是,应用程序配置不正确,因此其结果是使用不支持始终加密的驱动程序,或连接字符串不包含 column encryption setting=enabledHowever, the application is misconfigured, so that either it ends up using a driver that does not support Always Encrypted or the connection string does not contain column encryption setting=enabled.

  3. 应用程序向服务器发送纯文本值。The application sends plaintext values to the server. 由于针对用户在服务器中禁用了加密元数据检查,因此该服务器可让不正确的数据(纯文本而不是正确加密的已加密文本)插入到加密列中。As cryptographic metadata checks are disabled in the server for the user, the server lets the incorrect data (plaintext instead of correctly encrypted ciphertext) to be inserted into an encrypted column.

  4. 相同或其他应用程序使用启用了始终加密的驱动程序连接到数据库,并且连接字符串包含 column encryption setting=enabled ,并检索了数据。The same or another application connects to the database using an Always Encrypted-enabled driver and with column encryption setting=enabled in the connection string, and retrieves the data. 应用程序要求以透明方式解密数据。The application expects the data to be transparently decrypted. 但是,由于数据是错误的密文,因此驱动程序无法解密此数据。However, the driver fails to decrypt the data because the data is incorrect ciphertext.

最佳做法Best practice

针对使用此选项的长时间运行的工作负荷,使用指定的用户帐户。Use designated user accounts for long running workloads using this option.

针对短时间运行的大容量复制应用程序或需要移动加密数据而无需对其进行解密的工具,在运行该应用程序之前,将该选项设置为“开”,并在运行了操作后立即将其设置回“关”。For short running bulk copy applications or tools that need to move encrypted data without decrypting it, set the option to ON immediately before running the application and set it back to OFF immediately after running the operation.

不要使用此选项开发新应用程序。Do not use this option for developing new applications. 请改用提供 API(用于阻止针对单个会话的加密元数据检查)的客户端驱动程序,例如用于 SQL Server 的 .NET Framework 数据提供程序中的 AllowEncryptedValueModifications 选项 - 请参阅使用 SqlBulkCopy 复制加密数据Instead, use a client driver that offers an API for suppressing cryptographic metadata checks for a single session, such as the he AllowEncryptedValueModifications option in .NET Framework Data Provider for SQL Server - see Copying Encrypted Data using SqlBulkCopy.

后续步骤Next Steps

另请参阅See Also