チュートリアル: ランダム化された暗号化を使用してエンクレーブ対応の列でインデックスを作成して使用する

  • [アーティクル]

適用対象: 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」の「データベース復旧」セクションを参照してください。

  1. 前のチュートリアルで使ったすべての SSMS インスタンスを閉じます。 SSMS を閉じると、開いていたデータベース接続が閉じられます。ADR を有効にするにはこうする必要があります。

  2. SSMS の新しいインスタンスを開き、データベース接続の Always Encrypted を有効にしないで、sysadmin として SQL Server インスタンスに接続します。

    1. SSMS を起動します。
    2. [サーバーへの接続] ダイアログで、サーバーの名前を指定し、認証方法を選択して、資格情報を指定します。
    3. [オプション >>] をクリックして、[Always Encrypted] タブを選択します。
    4. [Always Encrypted を有効にする (列の暗号化)] チェック ボックスがオンになっていないことを確認します。
    5. [接続] を選択します。

  3. 新しいクエリ ウィンドウを開き、下のステートメントを実行して ADR を有効にします。

    ALTER DATABASE ContosoHR SET ACCELERATED_DATABASE_RECOVERY = ON;

手順 2: ロールを分離せずにインデックスを作成してテストする

この手順では、暗号化された列でインデックスを作成してテストします。 データベースを管理する DBA と、データを保護しているキーにアクセスできるデータ所有者の両方のロールを担う、1 人のユーザーとして作業します。

  1. SSMS を開き、データベース接続の Always Encrypted を有効にして、SQL Server インスタンスに接続します。

    1. SSMS の新しいインスタンスを開始します。
    2. [サーバーへの接続] ダイアログで、サーバーの名前を指定し、認証方法を選択して、資格情報を指定します。
    3. [オプション >>] をクリックして、[Always Encrypted] タブを選択します。
    4. [Always Encrypted を有効にする (列の暗号化)] および [セキュリティで保護されたエンクレーブを有効にする] チェック ボックスをオンにします。
    5. データベースに構成証明を使っている場合は、構成証明サービス (ホスト ガーディアン サービスまたはMicrosoft Azurezure Attestation) を示しているエンクレーブ構成証明プロトコルの値を選択して、エンクレーブ構成証明 URL に入力します。 それ以外の場合は、いいえを選択します。
    6. [接続] を選択します。
    7. Always Encrypted クエリのパラメーター化を有効にするよう求められたら、[有効] を選択します。

  2. Always Encrypted のパラメーター化を有効にすることを求められなかった場合は、有効になっていることを確認します。

    1. SSMS のメイン メニューから [ツール] を選択します。
    2. [オプション...] を選択します。
    3. [クエリ実行]>[SQL Server]>[詳細] の順に移動します。
    4. [Always Encrypted のパラメーター化を有効にする] がオンであることを確認します。
    5. [OK] を選択します。

  3. クエリ ウィンドウを開き、下のステートメントを実行して、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

  4. LastName 列にインデックスを作成します。 Always Encrypted を有効にしてデータベースに接続しているため、SSMS 内のクライアント ドライバーによって、CEK1 (LastName 列を保護している列暗号化キー) がエンクレーブに透過的に提供されます。インデックスを作成するにはこれが必要です。

    CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO

  5. LastName 列に対して高度なクエリを実行し、クエリの実行時に SQL Server によってインデックスが使われることを確認します。

    1. 同じクエリ ウィンドウまたは新しいクエリ ウィンドウで、ツール バーの [ライブ クエリ統計を含む] ボタンがオンになっていることを確認します。

    2. 下のクエリを実行します。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

    3. [ライブ クエリ統計] タブで、クエリによりインデックスが使われていることを確認します。

手順 3: ロールを分離してインデックスを作成する

この手順では、2 人の異なるユーザーとして、暗号化された列にインデックスを作成します。 1 人のユーザーは、インデックスを作成する必要がありますが、キーにアクセスできない DBA です。 もう 1 人のユーザーは、キーにアクセスできるデータ所有者です。

  1. Always Encrypted が有効になっていない SSMS インスタンスを使い、下のステートメントを実行して LastName 列のインデックスを削除します。

    DROP INDEX IX_LastName ON [HR].[Employees]; 
GO

  2. データ所有者 (または、キーにアクセスできるアプリケーション) として、エンクレーブ内のキャッシュに CEK1 を設定します。

    Note

    「手順 2: ロールを分離せずにインデックスを作成してテストする」の後に SQL Server インスタンスを再起動した場合を除き、CEK1 は既にキャッシュに存在しているため、この手順は冗長です。 キーがエンクレーブにまだ存在しない場合に、データ所有者がエンクレーブにキーを提供できる方法を示すため、キーを追加しました。

    1. Always Encrypted が有効になっている SSMS インスタンスにおいて、次のステートメントをクエリ ウィンドウで実行します。 そのステートメントでは、エンクレーブ対応の列のすべての暗号化キーがエンクレーブに送信されます。 詳しくは、「sp_enclave_send_keys」をご覧ください。

      EXEC sp_enclave_send_keys;
GO

    2. 上のストアド プロシージャを実行する代わりに、エンクレーブを使う DML クエリを LastName 列に対して実行してもかまいません。 これにより、エンクレーブに CEK1 だけが設定されます。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

  3. DBA として、インデックスを作成します。

    1. Always Encrypted が有効になっていない SSMS インスタンスにおいて、次のステートメントをクエリ ウィンドウで実行します。

      CREATE INDEX IX_LastName ON [HR].[Employees] ([LastName])
INCLUDE ([EmployeeID], [FirstName], [SSN], [Salary]);
GO

  4. データ所有者として、LastName 列に対して高度なクエリを実行し、クエリの実行時に SQL Server によってインデックスが使われることを確認します。

    1. Always Encrypted が有効になっている SSMS インスタンスで、既存のクエリ ウィンドウを選択するか、または新しいクエリ ウィンドウを開き、ツール バーの [ライブ クエリ統計を含む] ボタンがオンになっていることを確認します。

    2. 下のクエリを実行します。

      DECLARE @LastNamePrefix NVARCHAR(50) = 'Aber%';
SELECT * FROM [HR].[Employees] WHERE [LastName] LIKE @LastNamePrefix;
GO

    3. [ライブ クエリ統計] で、クエリによりインデックスが使われていることを確認します。

次のステップ

関連項目