使用 Always Encrypted 與 PowerShell 設定資料行加密

適用於：SQL Server Azure SQL Database Azure SQL 受控實例

本文提供使用 Set-SqlColumnEncryption Cmdlet 設定資料庫數據行目標 Always Encrypted 組態的步驟（在 SqlServer PowerShell 模組中）。 Set-SqlColumnEncryption Cmdlet 會修改目標資料庫的架構，以及儲存在選取數據行中的數據。您可以根據資料行的指定目標加密設定和目前的加密組態，對儲存在資料行中的資料進行加密、重新加密或解密。

注意

如您使用 SQL Server 2019 (15.x) 且 SQL Server 執行個體是以安全記憶體保護區進行設定，您可在此執行密碼編譯作業，而無須將資料移出資料庫。請參閱使用具有安全記憶體保護區的 Always Encrypted 直接設定資料列加密。

如需 SqlServer PowerShell 模組中 Always Encrypted 支援的詳細資訊，請參閱使用 PowerShell 設定 Always Encrypted。

必要條件

若要設定目標加密組態，您必須確定：

資料庫中已設定一個資料行加密金鑰 (如果您要加密或重新加密資料行)。如需詳細資訊，請參閱使用PowerShell設定Always Encrypted 金鑰。
您可以從執行 PowerShell Cmdlet 的電腦存取要加密、重新加密或解密的每個資料行的主要金鑰。
您使用 SqlServer PowerShell 模組 22.0.50 版或更新版本。

效能與可用性考量

若要套用資料庫的指定目標加密設定， Set-SqlColumnEncryption Cmdlet 會以透明方式從包含目標數據表的數據行下載所有數據，將數據上傳回一組臨時表（使用目標加密的設定），最後以新版本的數據表取代原始數據表。適用於 SQL Server 的基礎 .NET Framework 資料提供者會在下載及上傳資料時依據目標資料行的當前加密設定及指定的加密設定進行加密或解密。根據受影響資料表中的資料大小與網路頻寬而定，移動資料的作業可能需要一段很長的時間。

Set-SqlColumnEncryption Cmdlet 支援兩種方法來設定目標加密組態：在線和離線。

使用離線方法時，目標資料表 (以及與目標資料表相關的所有資料表，例如，與目標資料表具有外部索引鍵關聯性的所有資料表) 無法在作業持續期間寫入交易。使用離線方法時，一律會保留外鍵條件約束的語意（CHECK 或 NOCHECK）。

使用線上方法 (需要 SqlServer PowerShell 模組版本 21.x 或更新版本) 時，會以累加方式執行資料的複製和加密、解密或重新加密作業。應用程式可以在數據移動作業期間，讀取和寫入目標數據表的數據，但上次反覆專案除外，其持續時間受限於 MaxDownTimeInSeconds 參數（您可以定義）。若要在複製資料時偵測及處理應用程式可以進行的變更，Cmdlet 會在目標資料庫中啟用變更追蹤。因此，比起離線方法，線上方法可能會使用伺服器端上的更多資源。使用線上方法的作業可能也需要更多時間，特別是在對資料庫執行大量寫入的工作負載時。線上方法可用來一次加密一個資料表，而該資料表必須具有主索引鍵。根據預設，會使用 NOCHECK 選項重新建立外鍵條件約束，以將對應用程式的影響降到最低。您可以藉由指定 KeepCheckForeignKeyConstraints 選項，強制保留外鍵條件約束的語意。

以下是在離線和線上方法之間進行選擇的指導方針：

使用離線方法：

要將操作時間縮至最短。
同時對多個資料表中的資料行進行加密、解密和重新加密。
如果目標資料表沒有主索引鍵。

使用線上方法：

將應用程式資料庫的停機時間/無法使用的情況降至最低。

安全性考量

用來設定資料庫數據行加密的 Set-SqlColumnEncryption Cmdlet 會同時處理 Always Encrypted 金鑰和儲存在資料庫數據行中的數據。因此，請務必在安全的電腦上執行 Cmdlet。如果您的資料庫在 SQL Server 上，請從與裝載您的 SQL Server 執行個體不同的電腦上執行 Cmdlet。因為永遠加密的主要目標是為了確保已加密的敏感性資料安全無虞，即使資料庫系統遭到入侵亦然，所以在 SQL Server 電腦上執行處理金鑰和/或敏感性資料的 PowerShell 指令碼，可能會降低或損害此功能的優點。

任務	發行項	存取純文字金鑰/金鑰存放區	存取資料庫
步驟 1：啟動 PowerShell 環境並匯入 SqlServer 模組。	匯入 SqlServer 模組	否	否
步驟 2：連接到您的伺服器和資料庫	連接到資料庫	否	是
步驟 3：如果您的資料行主要金鑰（用於保護資料行加密金鑰並需要更換）儲存在 Azure 金鑰保存庫中，則請驗證以存取 Azure。	Connect-AzAccount	是	否
步驟 4：如果您的資料行主要金鑰儲存在 Azure Key Vault 中，請取得 Azure Key Vault 的存取權杖。	Get-AzAccessToken	否	否
步驟 5。建構 SqlColumnEncryptionSettings 物件的陣列 - 您要加密、重新加密或解密的每個資料庫資料行各有一個物件。 SqlColumnMasterKeySettings 是存在於 PowerShell 記憶體中的物件。它會指定資料行的目標加密配置。	新建-Sql列加密設定	否	否
步驟 6. 針對您在上一個步驟中建立的 SqlColumnMasterKeySettings 物件陣列，設定在其中指定的所需加密組態。將會根據資料行的指定目標設定和目前的加密組態，對資料行進行加密、重新加密或解密。	Set-SqlColumnEncryption 注意：此步驟可能需要很長的時間。根據您選取的方法 (線上與離線) 而定，您的應用程式可能無法在整個作業期間或作業的部分期間存取資料表。	是	是

使用離線方法加密資料行 - 範例

下列範例示範如何設定兩個資料行的目標加密組態。若有任何一個資料行尚未經過加密，將會對其進行加密。若有任何資料行已使用不同的金鑰和/或不同的加密類型加密，則會將它解密，然後使用指定的目標金鑰/類型重新加密。

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

使用線上方法加密資料行 - 範例

下列範例示範如何使用線上方法，設定兩個資料行的目標加密設定。若有任何一個資料行尚未經過加密，將會對其進行加密。若有任何資料行已使用不同的金鑰和/或不同的加密類型加密，則會將它解密，然後使用指定的目標金鑰/類型重新加密。

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -UseOnlineApproach -MaxDowntimeInSeconds 180 -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

解密資料行 - 範例

下列範例示範如何解密目前在資料庫中加密的所有資料行。

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Find all encrypted columns, and create a SqlColumnEncryptionSetting object for each column.
$ces = @()
$tables = $database.Tables
for($i=0; $i -lt $tables.Count; $i++){
    $columns = $tables[$i].Columns
    for($j=0; $j -lt $columns.Count; $j++) {
        if($columns[$j].isEncrypted) {
            $threeColPartName = $tables[$i].Schema + "." + $tables[$i].Name + "." + $columns[$j].Name 
            $ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType "Plaintext" 
        }
    }
}

# Decrypt all columns.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

加密後

清除存取該數據表的所有批次和預存程式的快取計畫，以刷新參數加密資訊。

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

注意

如果您未從快取中移除受影響查詢的計劃，加密之後的第一次執行查詢可能會失敗。

請小心使用 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 或 DBCC FREEPROCCACHE 來清除計劃快取，因為它可能會導致暫時查詢效能降低。若要將清除快取的負面影響降到最低，您可以選擇性地只移除受影響查詢的計劃。

呼叫 sp_refresh_parameter_encryption 以更新每個模塊參數的元資料（預存程式、函數、檢視、觸發程式），這些參數會保存在 sys.parameters 中，而且可能已透過加密數據行來失效。

後續步驟

使用Always Encrypted開發應用程式

另請參閱

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-04-30