チュートリアル: ランダム化された暗号化を使用してエンクレーブ対応の列でインデックスを作成して使用する
適用対象: SQL Server 2019 (15.x) 以降 - Windows のみ
Azure SQL データベース
このチュートリアルでは、セキュリティで保護されたエンクレーブが設定された Always Encrypted でサポートされているランダム化された暗号化を使用して、エンクレーブ対応の列にインデックスを作成して使用する方法を説明します。 次のことを示します。
- 列を保護しているキー (列マスター キーと列暗号化キー) にアクセスできるときに、インデックスを作成する方法。
- 列を保護しているキーにアクセスできないときに、インデックスを作成する方法。
前提条件
最新バージョンの SQL Server Management Studio (SSMS) をダウンロードします。
このチュートリアルで以下の手順を行う前に、「セキュリティで保護されたエンクレーブが設定された Always Encrypted を使い始める」のチュートリアルのいずれかを完了してください。
手順1: データベースで高速データベース復旧 (ADR) を有効にする
Note
この手順は SQL Server にのみ適用されます。 Azure SQL Database を使用している場合、この手順をスキップしてください。 ADR は Azure SQL Database で自動的に有効になり、無効化はサポートされていません。
ランダム化された暗号化を使用してエンクレーブ対応の列で最初のインデックスを作成する前に、データベースで ADR を有効にすることを強くお勧めします。 「セキュリティで保護されたエンクレーブが設定された Always Encrypted」の「データベース復旧」セクションを参照してください。
前のチュートリアルで使ったすべての SSMS インスタンスを閉じます。 SSMS を閉じると、開いていたデータベース接続が閉じられます。ADR を有効にするにはこうする必要があります。
SSMS の新しいインスタンスを開き、データベース接続の Always Encrypted を有効にしないで、sysadmin として SQL Server インスタンスに接続します。
- SSMS を起動します。
- [サーバーへの接続] ダイアログで、サーバーの名前を指定し、認証方法を選択して、資格情報を指定します。
- [オプション >>] をクリックして、[Always Encrypted] タブを選択します。
- [Always Encrypted を有効にする (列の暗号化)] チェック ボックスがオンになっていないことを確認します。
- [接続] を選択します。
新しいクエリ ウィンドウを開き、下のステートメントを実行して ADR を有効にします。
ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;
手順 2: ロールを分離せずにインデックスを作成してテストする
この手順では、暗号化された列でインデックスを作成してテストします。 データベースを管理する DBA と、データを保護しているキーにアクセスできるデータ所有者の両方のロールを担う、1 人のユーザーとして作業します。
SSMS を開き、データベース接続の Always Encrypted を有効にして、SQL Server インスタンスに接続します。
- SSMS の新しいインスタンスを開始します。
- [サーバーへの接続] ダイアログで、サーバーの名前を指定し、認証方法を選択して、資格情報を指定します。
- [オプション >>] をクリックして、[Always Encrypted] タブを選択します。
- [Always Encrypted を有効にする (列の暗号化)] および [セキュリティで保護されたエンクレーブを有効にする] チェック ボックスをオンにします。
- データベースに構成証明を使っている場合は、構成証明サービス (ホスト ガーディアン サービスまたはMicrosoft Azurezure Attestation) を示しているエンクレーブ構成証明プロトコルの値を選択して、エンクレーブ構成証明 URL に入力します。 それ以外の場合は、いいえを選択します。
- [接続] を選択します。
- Always Encrypted クエリのパラメーター化を有効にするよう求められたら、[有効] を選択します。
Always Encrypted のパラメーター化を有効にすることを求められなかった場合は、有効になっていることを確認します。
- SSMS のメイン メニューから [ツール] を選択します。
- [オプション...] を選択します。
- [クエリ実行]>[SQL Server]>[詳細] の順に移動します。
- [Always Encrypted のパラメーター化を有効にする] がオンであることを確認します。
- [OK] を選択します。
クエリ ウィンドウを開き、下のステートメントを実行して、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 によってインデックスが使われることを確認します。
同じクエリ ウィンドウまたは新しいクエリ ウィンドウで、ツール バーの [ライブ クエリ統計を含む] ボタンがオンになっていることを確認します。
下のクエリを実行します。
DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%'; SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix; GO
[ライブ クエリ統計] タブで、クエリによりインデックスが使われていることを確認します。
手順 3: ロールを分離してインデックスを作成する
この手順では、2 人の異なるユーザーとして、暗号化された列にインデックスを作成します。 1 人のユーザーは、インデックスを作成する必要がありますが、キーにアクセスできない DBA です。 もう 1 人のユーザーは、キーにアクセスできるデータ所有者です。
Always Encrypted が有効になっていない SSMS インスタンスを使い、下のステートメントを実行して LastName 列のインデックスを削除します。
DROP INDEX IX_LastName ON [HR].[Employees]; GO
データ所有者 (または、キーにアクセスできるアプリケーション) として、エンクレーブ内のキャッシュに CEK1 を設定します。
Note
「手順 2: ロールを分離せずにインデックスを作成してテストする」の後に SQL Server インスタンスを再起動した場合を除き、CEK1 は既にキャッシュに存在しているため、この手順は冗長です。 キーがエンクレーブにまだ存在しない場合に、データ所有者がエンクレーブにキーを提供できる方法を示すため、キーを追加しました。
Always Encrypted が有効になっている SSMS インスタンスにおいて、次のステートメントをクエリ ウィンドウで実行します。 そのステートメントでは、エンクレーブ対応の列のすべての暗号化キーがエンクレーブに送信されます。 詳しくは、「sp_enclave_send_keys」をご覧ください。
EXEC sp_enclave_send_keys; GO
上のストアド プロシージャを実行する代わりに、エンクレーブを使う DML クエリを LastName 列に対して実行してもかまいません。 これにより、エンクレーブに CEK1 だけが設定されます。
DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%'; SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix; GO
DBA として、インデックスを作成します。
Always Encrypted が有効になっていない SSMS インスタンスにおいて、次のステートメントをクエリ ウィンドウで実行します。
CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName]) INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]); GO
データ所有者として、LastName 列に対して高度なクエリを実行し、クエリの実行時に SQL Server によってインデックスが使われることを確認します。
Always Encrypted が有効になっている SSMS インスタンスで、既存のクエリ ウィンドウを選択するか、または新しいクエリ ウィンドウを開き、ツール バーの [ライブ クエリ統計を含む] ボタンがオンになっていることを確認します。
下のクエリを実行します。
DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%'; SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix; GO
[ライブ クエリ統計] で、クエリによりインデックスが使われていることを確認します。
次のステップ
- チュートリアル:セキュリティで保護されたエンクレーブが設定された Always Encrypted を使用する .NET アプリケーションの開発」をご覧ください。
- チュートリアル:セキュリティで保護されたエンクレーブが設定された Always Encrypted を使用する .NET Framework アプリケーションの開発