教學課程：使用隨機化加密在已啟用記憶體保護區的資料行上建立及使用索引

發行項
05/04/2023

適用於：SQL Server 2019 (15.x) 及更新版本 - 僅限 WindowsAzure SQL Database

本教學課程會指導您使用具有安全記憶體保護區的 Always Encrypted 中所支援的隨機化加密，在已啟用記憶體保護區的資料行上建立及使用索引的方式。它會顯示：

如何在能夠存取保護資料行的金鑰 (資料行主要金鑰和資料行加密金鑰) 的情況下建立索引。
如何在無法存取保護資料行之金鑰的情況下建立索引。

Prerequisites

下載最新版的 SQL Server Management Studio (SSMS)。

遵循本教學課程中的下列步驟之前，請確定您已完成開始使用 Always Encrypted 與安全記憶體保護區教學課程的其中一個。

步驟 1：在您的資料庫中啟用加速資料庫復原 (ADR)

注意

此步驟只適用於 SQL Server。如果您使用的是 Azure SQL Database，請略過此步驟。 ADR 會在 Azure SQL Database 中自動啟用，且不支援停用。

Microsoft 強烈建議先在您的資料庫中啟用 ADR，再使用隨機化加密於已啟用記憶體保護區的資料行上建立第一個索引。請參閱具有安全記憶體保護區的 Always Encrypted 中的＜資料庫復原＞一節。

關閉您在上一個教學課程中所使用的任何 SSMS 執行個體。關閉 SSMS 將會關閉您已開啟的資料庫連接；此為啟用 ADR 的必要條件。
開啟新的 SSMS 執行個體，並在未針對資料庫連接啟用 Always Encrypted 的情況下，以 sysadmin 身分連線到 SQL Server 執行個體。
1. 啟動 SSMS。
2. 在 [連線到伺服器] 對話方塊中指定您的伺服器名稱，選取驗證方法，然後指定認證。
3. 選取 [選項 >>]，然後選取 [Always Encrypted] 索引標籤。
4. 請確定未選取 [啟用 Always Encrypted (資料行加密)] 核取方塊。
5. 選取 [連接]。

開啟新的查詢視窗並執行下列陳述式以啟用 ADR。

ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;

步驟 2:在未隔離角色的情況下建立並測試索引

在此步驟中，您將會建立並測試已加密資料行上的索引。您將會以擔任 DBA、管理資料庫，以及具有保護資料之金鑰的資料擁有者等角色的單一使用者身分執行。

開啟新的 SSMS 執行個體，並在已針對資料庫連接啟用 Always Encrypted 的情況下連線到 SQL Server 執行個體。
1. 啟動新的 SSMS 執行個體。
2. 在 [連線到伺服器] 對話方塊中指定您的伺服器名稱，選取驗證方法，然後指定認證。
3. 選取 [選項 >>]，然後選取 [Always Encrypted] 索引標籤。
4. 選取 [啟用 Always Encrypted (資料行加密)] 和 [啟用安全記憶體保護區] 核取方塊
5. 如果您使用資料庫的證明，請選取代表證明服務 (主機守護者服務或 Microsoft Azure 證明) 的記憶體保護區證明通訊協定值，並填入記憶體保護區證明 URL。否則，請選取 [否]。
6. 選取 [連線]。
7. 如果系統提示您啟用 Always Encrypted 的參數化查詢，請選取 [啟用]。
如果系統未提示您啟用 Always Encrypted 的參數化，請確認其已啟用。
1. 從 SSMS 的主功能表選取 [工具]。
2. 選取 [選項]。
3. 瀏覽至 [查詢執行]>[SQL Server]>[進階]。
4. 確定已選取 [啟用 Always Encrypted 的參數化]。
5. 選取 [確定]。

開啟查詢視窗並執行下列陳述式，以將 Employees 資料表中的 LastName 資料行加密。您將會在稍後的步驟中建立並使用該資料行上的索引。

ALTER TABLE [HR].[Employees]
ALTER COLUMN [LastName] [nvarchar](50) COLLATE Latin1_General_BIN2 
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Randomized, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO   
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

在 LastName 資料行上建立索引。由於您是在啟用 Always Encrypted 的情況下連線到資料庫，SSMS 內的用戶端驅動程式會以透明方式向記憶體保護區提供 CEK1 (保護 LastName 資料行的資料行加密金鑰)，其為建立索引的所需項目。
```
CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO
```
在 LastName 資料行上執行豐富查詢，並確認 SQL Server 在執行查詢時會使用該索引。
1. 在相同的或新的查詢視窗中，確定工具列上的 [包含即時查詢統計資料] 按鈕已經開啟。
2. 執行下列查詢。
```
DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO
```
3. 在 [即時查詢統計資料] 索引標籤中，觀察查詢是否使用索引。

步驟 3：建立具有角色隔離的索引

在此步驟中，您將會在已加密的資料行上建立索引，並扮演兩個不同的使用者。一個使用者是 DBA，其需要建立索引，但不具備金鑰的存取權。另一個使用者是資料擁有者，其具備金鑰存取權。

在未啟用 Always Encrypted 的情況下使用 SSMS 執行個體來執行下列陳述式，以在 LastName 資料行上放置索引。
```
DROP INDEX IX_LastName ON [HR].[Employees]; 
GO
```
以資料擁有者的身分 (或是能存取金鑰的應用程式)，以 CEK1 填入記憶體保護區內的快取。

注意

除非您在步驟 2：在未隔離角色的情況下建立並測試索引之後曾重新啟動 SQL Server 執行個體，否則此步驟是不必要的，因為 CEK1 已經位於快取之中。我們加入它的原因是為了示範資料擁有者如何在金鑰尚未存在於記憶體保護區中的情況下為它提供金鑰。
1. 在已啟用 Always Encrypted 的 SSMS 執行個體中，於查詢視窗中執行下列陳述式。該陳述式會將所有已啟用記憶體保護區之資料行的加密金鑰傳送到記憶體保護區。請參閱 sp_enclave_send_keys 以取得詳細資料。
```
EXEC sp_enclave_send_keys;
GO
```
2. 作為執行上述預存程序的替代方案，您也可以執行會針對 LastName 資料行使用記憶體保護區的 DML 查詢。這將會僅搭配 CEK1 來填入記憶體保護區。
```
DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO
```
以 DBA 的身分建立索引。
1. 在未啟用 Always Encrypted 的 SSMS 執行個體中，於查詢視窗中執行下列陳述式。
```
CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO
```
以資料擁有者的身分，在 LastName 資料行上執行豐富查詢，並確認 SQL Server 在執行查詢時會使用該索引。
1. 在已啟用 Always Encrypted 的 SSMS 執行個體中，選取現有的查詢視窗或開啟新的查詢視窗，並確定工具列上的 [包含即時查詢統計資料] 按鈕已經開啟。
2. 執行下列查詢。
```
DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO
```
3. 在 [即時查詢統計資料] 中，觀察查詢是否使用索引。

後續步驟

另請參閱

使用具有安全記憶體保護區的 Always Encrypted 在資料行上建立及使用索引

Share via