教學課程：開始使用 Always Encrypted

發行項
05/23/2023

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

本教學課程會教導您如何開始使用 Always Encrypted。它會顯示：

如何加密資料庫中選取的資料行。
如何查詢加密的資料行。

注意

如果您要尋找具有安全記憶體保護區的 Always Encrypted 資訊，請改為參閱下列教學課程：

必要條件

在本教學課程中，您需要：

Azure SQL Database、Azure SQL 受控執行個體或 SQL Server 中的空白資料庫。下列指示假設資料庫名稱為 ContosoHR。您必須是資料庫的擁有者 (db_owner 角色的成員)。如需如何建立資料庫的資訊，請參閱快速入門：建立單一資料庫 - Azure SQL Database 或在 SQL Server 中建立資料庫。
選擇性，但建議使用，特別是如果您的資料庫位於 Azure 中：Azure Key Vault 中的金鑰保存庫。如需建立金鑰保存庫的資訊，請參閱快速入門：使用 Azure 入口網站建立金鑰保存庫。
- 如果您的金鑰保存庫使用存取原則權限模型，請確定您在金鑰保存庫中具有下列金鑰權限：get、list、create、unwrap key、wrap key、verify、sign。請參閱指派金鑰保存庫存取原則。
- 如果您正在使用 Azure 角色型存取控制 (RBAC) 權限模型，請確定您是金鑰保存庫之 Key Vault 密碼編譯長角色的成員。請參閱使用 Azure 角色型存取控制來提供 Key Vault 金鑰、憑證和秘密的存取權。
最新版的 SQL Server Management Studio (SSMS) 或最新版的 SqlServer 和 Az PowerShell 模組。只有在您使用 Azure Key Vault 時，才需要 Az PowerShell 模組。

步驟 1：建立並填入資料庫結構描述

在此步驟中，您將建立 HR 結構描述和 Employees 資料表。然後，您會將一些資料填入資料表。

SSMS
PowerShell

連線到您的資料庫。如需如何從 SSMS 連接到資料庫的指示，請參閱快速入門：使用 SQL Server Management Studio (SSMS) 連接及查詢 Azure SQL Database 或 Azure SQL 受控執行個體或快速入門：使用 SQL Server Management Studio (SSMS) 連接及查詢 SQL 執行個體。
開啟 ContosoHR 資料庫的新查詢視窗。

貼上並執行下列陳述式，以建立名為 Employees 的新資料表。

CREATE SCHEMA [HR];
GO

CREATE TABLE [HR].[Employees]
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL
    , [SSN] [char](11) NOT NULL
    , [FirstName] [nvarchar](50) NOT NULL
    , [LastName] [nvarchar](50) NOT NULL
    , [Salary] [money] NOT NULL
) ON [PRIMARY];

貼上並執行下列陳述式，將一些員工記錄新增至 Employees 資料表。

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '795-73-9838'
    , N'Catherine'
    , N'Abel'
    , $31692
);

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '990-00-6818'
    , N'Kim'
    , N'Abercrombie'
    , $55415
);

在 PowerShell 工作階段中，執行下列命令。請確定使用您的伺服器位址，以及對您資料庫有效的驗證設定來更新連線字串。

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

步驟 2：加密資料行

在此步驟中，您將佈建資料行主要金鑰和資料行加密金鑰，以進行 Always Encrypted。然後，您將加密 Employees 資料表中的 SSN 和 Salary 資料行。

SSMS
PowerShell

SSMS 提供一個精靈，其可透過設定資料行主要金鑰、資料行加密金鑰及加密選取的資料行，來協助您輕鬆設定 Always Encrypted (「一律加密」)。

在 [物件總管] 中，展開 [資料庫>ContosoHR>資料表]。
在 Employees 資料表上按一下滑鼠右鍵，然後選取 [加密資料行] 以開啟 [Always Encrypted 精靈]。
在精靈的 [簡介] 頁面上，選取 [下一步]。
在 [資料行選取] 頁面上。
1. 選取 SSN 和 Salary 資料行。針對 SSN 資料行選擇確定性加密，以及針對 Salary 資料行選擇隨機化加密。確定性加密支援查詢，例如涉及在加密資料行上執行相等比較的點查閱搜尋。隨機化加密不支援在加密資料行上執行任何計算。
2. 針對兩個資料行保留 CEK-Auto1 (新增) 作為資料行加密金鑰。此金鑰尚未存在，將由精靈產生。
3. 選取 [下一步]。
在 [主要金鑰組態] 頁面上，設定精靈將產生的新資料行主要金鑰。首先，您必須選取要儲存資料行主要金鑰的位置。精靈支援兩種金鑰存放區類型：
- Azure Key Vault - 如果您的資料庫位於 Azure 中，建議使用
- Windows 的憑證存放區
一般而言，Azure Key Vault 是建議的選項，特別是在您的資料庫位於 Azure 時。
- 若要使用 Azure Key Vault：
  1. 選取 [Azure 金鑰保存庫] 。
  2. 選取 [登入]，然後完成登入 Azure。
  3. 登入之後，頁面會顯示您可以存取的訂用帳戶和金鑰保存庫的清單。選取包含您想要使用的金鑰保存庫的 Azure 訂用帳戶。
  4. 選取您的金鑰保存庫。
  5. 選取 [下一步]。
- 若要使用 Windows 憑證存放區：
  1. 選取 [Windows 憑證存放區]。
  2. 保留 [目前使用者] 的預設選項 - 這會指示精靈在目前使用者的存放區中產生憑證 (您的新資料行主要金鑰)。
  3. 選取 [下一步]。
在 [就地加密設定] 頁面上，不需要其他設定，因為資料庫未啟用記憶體保護區。選取 [下一步]。
在 [執行設定] 頁面上，系統會詢問您要繼續進行加密，還是要產生稍後要執行的 PowerShell 指令碼。保留預設的設定，然後選取 [下一步]。
在 [摘要] 頁面上，精靈會告知您它將執行的動作。檢查所有資訊是否都正確，然後選取 [完成]。
在 [結果] 頁面上，您可以監視精靈作業的進度。等到所有作業都順利完成，然後選取 [關閉]。
(選擇性) 探索精靈在您的資料庫中所做的變更。
1. 展開 [ContosoHR>安全性>Always Encrypted 金鑰]，以探索資料行主要金鑰的中繼資料物件，以及精靈所建立的資料行加密。
2. 您也可以針對包含金鑰中繼資料的系統目錄檢視來執行下列的查詢。
```
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
```
3. 在 [物件總管] 中，以滑鼠右鍵按一下 Employees 資料表，然後選取 [編寫資料表的指令碼為>CREATE 至>新增查詢編輯器視窗]。這將開啟一個新的查詢視窗，其中包含 Employees 資料表的 CREATE TABLE 陳述式。請注意，出現在 SSN 和 Salary 資料行定義中的 ENCRYPTED WITH 子句。
4. 您也可以針對 sys.columns 執行下列的查詢，以擷取這兩個加密資料行的資料行層級加密中繼資料。
```
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
```

在您的金鑰存放區中建立資料行主要金鑰。

如果您正在使用 Azure Key Vault，請執行下列的命令，在您的金鑰保存庫中建立一個非對稱金鑰。請確定您提供訂用帳戶的正確識別碼、包含金鑰保存庫的資源群組名稱，以及金鑰保存庫名稱。

Import-Module "Az"
Connect-AzAccount
$subscriptionId = "<your Azure subscription ID"
$resourceGroup = "your resource group name containing your key vault"
$keyVaultName = "your vault name"
$keyVaultKeyName = "your key name"

# Switch to your subscription.
Set-AzConteXt -SubscriptionId $subscriptionId

# To validate the above key vault settings, get the key vault properties.
Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 

# Create a key in the key vault.
$keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
$keyVaultKey

如果您正在使用 Windows 憑證存放區，請執行下列的命令，在目前的使用者存放區中建立一個憑證。

$cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

使用 SqlServer PowerShell 模組來連接到您的資料庫。請確定您為資料庫提供一個有效的連接字串。
```
$database = Get-SqlDatabase -ConnectionString $connectionString
$database
```

在您的資料庫中佈建資料行主要金鑰中繼資料物件 (參考您在金鑰存放區中已建立的實體資料行主要金鑰)。

如果您正在使用 Azure Key Vault，請執行下列的命令。

# Sign in to Azure for the SqlServer PowerShell module
Add-SqlAzureAuthenticationContext -Interactive

# Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid

# Create column master key metadata object (referencing your certificate), named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

如果您正在使用 Windows 憑證存放區，請執行下列的命令。

# Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

# Create column master key metadata object, named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

產生一個資料行加密金鑰，並使用您已建立的資料行主要金鑰將它加密，然後在資料庫中建立一個資料行加密金鑰中繼資料物件。
```
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
```

加密 Employees 資料表中的 SSN 和 Salary 資料行。針對 SSN 資料行選擇確定性加密，以及針對 Salary 資料行選擇隨機化加密。確定性加密支援查詢，例如涉及在加密資料行上執行相等比較的點查閱搜尋。隨機化加密不支援在加密資料行上執行任何計算。

# Encrypt the SSN and Salary columns 
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

(選擇性) 探索資料庫中所做的變更。

執行下列命令來查詢一些系統目錄檢視，其中包含有關資料行主要金鑰和您所建立之資料行加密金鑰的中繼資料。

$query = @'
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

執行下列命令來查詢 sys.columns，以擷取兩個加密資料行的資料行層級加密中繼資料。

$query = @'
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

針對您的連線停用 Always Encrypted 功能來連接到您的資料庫。
1. 開啟新的查詢視窗。
2. 在查詢視窗的任何位置上按一下滑鼠右鍵，然後選取 [連線>變更連線]。這會開啟 [連接到資料庫引擎] 對話方塊。
3. 選取 [選項 <<]。這會在 [連接到資料庫引擎] 對話方塊中顯示其他索引標籤。
4. 選取 [Always Encrypted] 索引標籤。
5. 請確定未選取 [啟用 Always Encrypted (資料行加密)]。
6. 選取 [連線]。
貼上並執行下列查詢。該查詢應該會傳回二進位加密的資料。
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```
針對您的連線啟用 Always Encrypted 功能來連接到您的資料庫。
1. 在查詢視窗的任何位置上按一下滑鼠右鍵，然後選取 [連線>變更連線]。這會開啟 [連接到資料庫引擎] 對話方塊。
2. 選取 [選項 <<]。這會在 [連接到資料庫引擎] 對話方塊中顯示其他索引標籤。
3. 選取 [Always Encrypted] 索引標籤。
4. 選取 [啟用 Always Encrypted (資料行加密])。
5. 選取 [連線]。
重新執行同一個查詢。因為您已針對您的資料庫連線啟用了 Always Encrypted 功能而連上線，所以 SSMS 中的用戶端驅動程式會嘗試將這兩個加密資料行中儲存的資料解密。如果您使用 Azure Key Vault，系統可能會提示您登入 Azure。
啟用 [Always Encrypted 的參數化] 功能。此功能可讓您執行依加密資料行篩選資料的查詢 (或將資料插入加密的資料行)。
1. 從 SSMS 的主功能表中，選取 [查詢]。
2. 選取 [查詢選項]。
3. 瀏覽至 [執行]>[進階]。
4. 確定已核取 [啟用 Always Encrypted 的參數化]。
5. 選取 [確定]。
貼上並執行下列查詢，此查詢會依加密的 SSN 資料行篩選資料。此查詢應該會傳回一個包含純文字值的資料列。
```
DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN
```
或者，如果您使用以存取原則權限模型設定的 Azure Key Vault，請遵循下列的步驟，以查看當使用者嘗試從加密資料行中擷取純文字資料時會發生什麼事 (在無法存取保護資料的資料行主要金鑰的情況下)。
1. 在您的金鑰保存庫的存取原則中，自行移除金鑰 unwrap 權限。如需詳細資訊，請參閱指派 Key Vault 存取原則。
2. 因為 SSMS 中的用戶端驅動程式會快取從金鑰保存庫中取得的資料行加密金鑰長達 2 小時，所以請關閉 SSMS，然後再次開啟它。這可確保金鑰快取是空的。
3. 針對您的連線啟用 Always Encrypted 功能來連接到您的資料庫。
4. 貼上並執行下列查詢。此查詢應該會失敗並出現錯誤訊息，指出您缺少必要的 unwrap 權限。
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```

在停用 Always Encrypted 功能的情況下連接到您的資料庫，並執行查詢以從加密的資料行中讀取資料。此查詢應該會以二進位陣列的形式傳回加密的資料。
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString $connectionString -Query $query
```
在啟用 Always Encrypted 功能的情況下連接到您的資料庫，並執行查詢以從加密的資料行中讀取資料。因為您可以存取保護加密資料行的資料行主要金鑰，所以該查詢應該會傳回純文字資料。
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
```

注意

Invoke-SqlCmd 不支援可以依加密資料行篩選資料或將資料插入加密資料行的查詢。這類的查詢需要進行參數化，而 Invoke-SqlCmd 不支援參數化的查詢。

下一步

使用 Always Encrypted 開發應用程式

共用方式為

教學課程：開始使用 Always Encrypted

必要條件

步驟 1：建立並填入資料庫結構描述

步驟 2：加密資料行

步驟 3：查詢加密的資料行

下一步

另請參閱

意見反應

其他資源