使用 [Always Encrypted 精靈] 設定資料行加密Configure column encryption using Always Encrypted Wizard

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

[Always Encrypted 精靈] 是一個功能強大的工具,可讓您設定所選取資料庫資料行所需的 Always Encrypted 設定。The Always Encrypted Wizard is a powerful tool that allows you to set the desired Always Encrypted configuration for selected database columns. 根據目前設定和所需目標設定,精靈可以加密資料行、將其解密 (移除加密) 或重新加密 (例如,使用新的資料行加密金鑰,或與針對資料行所設定目前類型不同的加密類型)。Depending on the current configuration and the desired target configuration, the wizard can encrypt a column, decrypt it (remove encryption), or re-encrypt it (for example, using a new column encryption key or an encryption type that is different from the current type, configured for the column). 在精靈的單一執行中,可以設定多個資料行。Multiple columns can be configured in a single run of the wizard.

精靈可讓您使用現有資料行加密金鑰來加密資料行,或您可以選擇產生新的資料行加密金鑰,或同時產生新資料行加密金鑰和新資料行主要金鑰。The wizard allows you to encrypt columns with existing column encryption keys, or you can choose to generate a new column encryption key or both a new column encryption key and a new column master key.

精靈的運作方式是將資料移出資料庫,並在 SSMS 處理序中執行密碼編譯作業。The wizard works by moving data out of the database and performing cryptographic operations within the SSMS process. 精靈會在資料庫中建立具有所需加密設定的新資料表、從原始資料表載入所有資料、執行所要求的密碼編譯作業、將資料上傳至新資料表,然後交換原始資料表與新資料表。The wizard creates a new table (or tables) with the desired encryption configuration in the database, loads all data from the original tables, performs the requested cryptographic operations, uploads the data to the new table(s), and then swaps the original table(s) with the new table(s).

注意

執行密碼編譯作業可能需要很長的時間。Running cryptographic operations can take a long time. 在這段期間,資料庫無法寫入交易。During that time, your database is not available to write transactions. PowerShell 是在較大資料表上進行密碼編譯作業時的建議工具。PowerShell is a recommended tool for cryptographic operations on larger tables. 請參閱使用 Always Encrypted 與 PowerShell 設定資料行加密See Configure column encryption using Always Encrypted with PowerShell.

注意

如果您使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 且 SQL Server 執行個體是以安全記憶體保護區進行設定,您可以就地執行密碼編譯作業,而不需要將資料移出資料庫。If you are using SQL Server 2019 (15.x)SQL Server 2019 (15.x) and your SQL Server instance is configured with a secure enclave, you can run cryptographic operations in-place, without moving data out of the database. 請參閱使用具有安全記憶體保護區的 Always Encrypted 就地設定資料行加密See Configure column encryption in-place using Always Encrypted with secure enclaves. 請注意,此精靈不支援就地加密。Note that the wizard does not support in-place encryption.

建議使用 PowerShellUse PowerShell is a recommended

權限Permissions

若要使用精靈來執行密碼編譯作業,您必須具備 VIEW ANY COLUMN MASTER KEY DEFINITIONVIEW ANY COLUMN ENCRYPTION KEY DEFINITION 權限。To perform cryptographic operations using the wizard, you must have the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION permissions. 您也必須具備在保存金鑰的金鑰存放區中,存取您正在使用資料行主要金鑰的權限:You also must have permissions to access column master keys, you are using, in the key stores holding the keys:

  • 憑證存放區 - 本機電腦 - 您必須具有當成資料行主要金鑰使用之憑證的讀取權,或為電腦上的系統管理員。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 - 您需要有包含資料行主要金鑰之保存庫的 get、unwrapKey 和 verify 權限。Azure Key Vault - you need the get, unwrapKey, and verify permissions on the vault containing the column master key.
  • 金鑰存放區提供者 (CNG) - 使用金鑰存放區或金鑰時,系統可能會提示您提供必要權限和認證 (取決於存放區和 KSP 設定)。Key Store Provider (CNG) - you might be prompted for the required permission and credentials when using a key store or a key, depending on the store and the KSP configuration.
  • 密碼編譯服務提供者 (CAPI) - 使用金鑰存放區或金鑰時,系統可能會提示您提供必要權限和認證 (取決於存放區和 CSP 設定)。Cryptographic Service Provider (CAPI) - you might be prompted for the required permission and credentials when using a key store or a key, depending on the store and the CSP configuration.

此外,若您正在使用精靈建立新的金鑰,您必須具備使用 [新增資料行主要金鑰] 對話方塊佈建資料行主要金鑰使用 [新增資料行加密金鑰] 對話方塊佈建資料行加密金鑰中所列的額外權限。In addition, if you are creating new keys using the wizard, you must have additional permissions listed in Provision Column Master Keys with the New Column Master Key Dialog and Provision Column Encryption Keys with the New Column Encryption Key Dialog.

開啟 [Always Encrypted 精靈]Open the Always Encrypted Wizard

您可以在三個不同層級啟動精靈:You can launch the wizard at three different levels:

  • 資料庫層級 - 若您想要加密位於不同資料表中的多個資料行。At a database level - if you want to encrypt multiple columns located in different tables.
  • 資料表層級 - 若您想要加密位於相同資料表中的多個資料行。At a table level - if you want to encrypt multiple columns located in the same table.
  • 資料行層級 - 若您想要加密單一特定資料行。At a column level - if you want to encrypt one specific column.
  1. 使用 SQL ServerSQL Server 的物件總管元件,連接到您的 SQL Server Management StudioSQL Server Management StudioConnect to your SQL ServerSQL Server with the Object Explorer component of SQL Server Management StudioSQL Server Management Studio.

  2. 加密:To encrypt:

    1. 資料庫中位於不同資料表內的多個資料行:請以滑鼠右鍵按一下您的資料庫、指向 [工作],然後選取 [加密資料行]。Multiple columns located in different table in a database, right-click your database, point to Tasks, and then select Encrypt Columns.
    2. 位於相同資料表中的多個資料行:請巡覽至資料表、以滑鼠右鍵按一下資料表,然後選取 [加密資料行]。Multiple columns located in the same table, navigate to the table, right-click on it, and then select Encrypt Columns.
    3. 個別資料行:請巡覽至資料行、以滑鼠右鍵按一下資料行,然後選取 [加密資料行]。An individual column, navigate to the column, right-click on it, and then select Encrypt Columns.

資料行選取頁面Column Selection Page

在此頁面中,您可以選取您想要加密、重新加密或解密的資料行,且您可以為所選取的資料行定義目標加密設定。In this page, you select columns you want to encrypt, re-encrypt, or decrypt, and you define the target encryption configuration for the selected columns.

若要加密純文字資料行 (並未加密的資料行),請為資料行選取加密類型 ([決定性] 或 [隨機]) 和加密金鑰。To encrypt a plaintext column (a column that isn't encrypted), select an encryption type (Deterministic or Randomized) and an encryption key for the column.

若要為已加密的資料行變更加密類型或輪替 (變更) 資料行加密金鑰,請選取所需的加密類型和金鑰。To change an encryption type or to rotate (change) a column encryption key for an already encrypted column, select the desired encryption type and the key.

若您想要精靈使用新的資料行加密金鑰加密或重新加密一或多個資料行,請挑選其名稱中包含 (新增) 的金鑰。If you want the wizard to encrypt or re-encrypt one or more columns using a new column encryption key, pick a key containing (New) in its name. 精靈將會產生金鑰。The wizard will generate the key.

若要解密目前已加密的資料行,請針對加密類型選取 [純文字]。To decrypt a column that is currently encrypted, select Plaintext for the encryption type.

注意

精靈不支援在時態性和記憶體內部資料表上進行密碼編譯作業。The wizard does not support cryptographic operations on temporal and in-memory tables. 您可以使用 Transact-SQL 建立空白的時態性或記憶體內部資料表,並使用您的應用程式插入資料。You can create empty temporal or in-memory tables using Transact-SQL and insert data using your application.

主要金鑰設定頁面Master Key Configuration Page

若您已在上一個頁面為任何資料行選取自動產生的資料行加密金鑰,在此頁面中您需要選取現有的資料行主要金鑰,或設定將加密資料行加密金鑰的新資料行主要金鑰。If you have selected an autogenerated column encryption key for any column on the previous page, in this page you need to either select an existing column master key or configure a new column master key that will encrypt the column encryption key.

設定新的資料行主要金鑰時,您可以在 Windows 憑證存放區或 Azure Key Vault 中挑選現有金鑰,並讓精靈只為資料庫中的金鑰建立中繼資料物件,或選擇同時產生金鑰及描述資料庫中金鑰的中繼資料物件。When configuring a new column master key, you can either pick an existing key in Windows Certificate Store or in Azure Key Vault and have the wizard to create just a metadata object for the key in the database, or you can choose to generate both the key and the metadata object describing the key in the database.

如需在 Windows 憑證存放區、Azure Key Vault 或其他金鑰存放區中建立和儲存資料行主要金鑰的詳細資訊,請參閱對 Always Encrypted 建立和儲存資料行主要金鑰For more information about creating and storing column master keys in Windows Certificate Store, Azure Key Vault or other key stores, see Create and store column master keys for Always Encrypted.

提示

精靈只允許您在 Windows 憑證存放區和 Azure Key Vault 中瀏覽和建立金鑰。The wizard allows you to browse and create keys only in Windows Certificate Store and Azure Key Vault. 精靈也會自動產生兩個新金鑰的名稱,以及描述金鑰的資料庫中繼資料物件。It also auto-generates the names of both the new keys and the database metadata objects describing the keys. 若您需要深入控制您金鑰的佈建方式 (以及針對包含您資料行主要金鑰的金鑰存放區擁有更多選擇),您可以先使用 [新增資料行主要金鑰] 和 [新增資料行加密金鑰] 對話方塊來建立金鑰,然後執行精靈並挑選您已建立的金鑰。If you need more control for how your keys are provisioned (and more choices for a key store containing your column master key), you can use the New Column Master Key and New Column Encryption Key dialogs to create the keys first, and then run the wizard and pick the keys you have created. 請參閱使用 [新增資料行主要金鑰] 對話方塊佈建資料行主要金鑰使用 [新增資料行加密金鑰] 對話方塊佈建資料行加密金鑰See Provision Column Master Keys with the New Column Master Key Dialog and Provision Column Encryption Keys with the New Column Encryption Key Dialog.

後續步驟Next Steps

另請參閱See Also