投稿日：2025/01/22

はじめに

　前回のブログでは、SQL Serverの列レベルの保護で使用する「Always Encrypted」の概要や暗号化方法についてご紹介しました。
　今回は「Always Encrypted」の設定方法やデータ操作の検証結果を記載します。

暗号化の全体像

　Always Encryptedの暗号化フローは以下の全体像となります。

　1. クライアント側でCMK（列マスターキー）とCEK（列暗号化キー）を作成します。今回の検証ではCMKをWindows証明書ストアで作成しますが、Azure上のSQL Serverであれば、Key Vaultを利用することになります。

　2. サーバ側にCMKのパス情報と、クライアント側で暗号化されたCEKが格納されます。

　3. 暗号化対象のテーブル・列をサーバ側のCEKを使って暗号化します。

暗号化の検証

　SSMSのGUI画面をメインにcustomerテーブルのnameとcredit_numの列を暗号化します。
　まずは検証に使用するテーブルとデータを準備します。

-- 検証用テーブル
CREATE TABLE customer (
    name nvarchar(10),
    credit_num nvarchar(10)
);
-- 2件のデータを挿入
INSERT INTO customer VALUES (
    N'nakagawa',
    N'12345-6789'
);

　一度データの中身を確認しましょう。

　それでは、Always Encryptedの設定を行います。
　対象のテーブルを右クリックし、[列の暗号化]を押下します。

　Always Encryptedの説明が表示されますので、[次へ]を押下します。

　次に暗号化する列を選択します。
　CEKは自動作成されるので、そちらを利用します。
　なお、今回はnameとcredit_numが対象なのでチェックを入れ、種類は決定論的とランダム化どちらも検証してみます。

　次にマスターキーを作成します。今回はWindows証明書ストアを利用するので、デフォルトの設定で[次へ]を選択します。

　インプレース暗号化の設定は追加の構成が必要ないので、[次へ]を選択します。

　最後に実行設定です。
　今回は検証レベルなので「続行して今すぐ完了」を選択しますが、暗号化中にテーブルの書き込み操作が行われるとデータが失われる可能性があります。
　なので、稼働中のデータベース・テーブルであればメンテナンス時間を設ける必要がありそうですね。

　サマリーが表示されますので、最終確認してください。
　[完了]を押下すると暗号化の設定が行われます。

　暗号化の設定が正常に終了した場合、以下の画面が表示されるので[閉じる]を押下します。

　CMKはWindows証明書ストアを指定したため、「ユーザー証明書の管理」の証明書に格納されています。

　SSMSからは、SQL Server上のCMKとCEKを確認できますね。

　以下のSQLからでも確認できます。

SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys;
SELECT * FROM sys.column_encryption_key_values;

公式Doc
　sys.column_master_keys (Transact-SQL)
　sys.column_encryption_keys (Transact-SQL)
　sys.column_encryption_key_values (Transact-SQL)

　Always Encryptedで暗号化されているカラム名とテーブル名、暗号化の種類は以下のSQLで確認することができます。

SELECT
    c.[name] AS ColumnName,
    c.[encryption_type],
    c.[encryption_type_desc],
    c.[encryption_algorithm_name],
    c.[column_encryption_key_id]
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.[object_id] = t.[object_id]
WHERE 
    c.[encryption_type] IS NOT NULL;

　さて、暗号化の状態ですがどうなっているでしょうか…。

　name列は決定論的な暗号化のため、暗号化されていても同データということが分かりますね。
　一方、ランダム化の暗号化がされているcredit_num列は同データではありますが、暗号化後のデータは異なります。

復号化の全体像

　Always Encryptedの復号化のフローは以下の全体像となります。

　クライアント側でクエリを実行する際、クライアント側のCEKでクエリのパラメータがあれば暗号化します。
　暗号化されたクエリがサーバ側で実行され、その結果をクライアント側のCMKを介して復号化し、結果を表示させます。

復号化の実機検証

　前提条件ともなっている「Column Encryption Setting=Enabledの設定」は、SSMSのオプションで以下のどちらかを設定する必要があります。

・Always Encrypted > Always Encryptedを有効にする（列の暗号化）

・追加の接続パラメータ > 「Column Encryption Setting=Enabled」の貼り付け

　単純に以下のSQLを実行すると、復号された結果を確認することができます。

SELECT name, credit_num FROM customer;

　
　決定論的な暗号化を施した列を指定した絞り込み検索はパラメータ化クエリでの操作が必須となります。
　よって、以下のSQLを準備することで結果を得ることができます。

DECLARE @name nvarchar(10) = 'nakagawa';
SELECT name, credit_num FROM customer
WHERE name = @name;

挿入操作も同様にパラメータ化クエリでの対応が可能です。

DECLARE @name nvarchar(10) = 'nakagawa';
DECLARE @credit_num nvarchar(10) = '12345-6789';
INSERT INTO customer (name, credit_num) 
VALUES (@name, @credit_num);

　なお、前回のブログで記載しているようにランダム化で暗号化された列は絞り込み条件として指定することはできません。
　指定して検索を行うと、以下のエラーが出力されます。

メッセージ 33277、レベル 16、状態 2、行 16
暗号化スキームが列/変数 '@credit_num', 'credit_num' と一致していません。列/変数の暗号化スキームは (encryption_type = 'RANDOMIZED', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'test') であり、行 '6' の近くの式は DETERMINISTIC, or RANDOMIZED, a BIN2 collation for string data types, and an enclave-enabled column encryption key, or PLAINTEXT の暗号化スキームが必要です。
メッセージ 8180、レベル 16、状態 1、プロシージャ sp_describe_parameter_encryption、行 1 [バッチ開始行 10]
ステートメントを準備できませんでした。
バッチの実行中にエラーが発生しました。エラー メッセージ: 内部エラー。sp_describe_parameter_encryption から返された結果セットに、ステートメントのパラメーター '@p7fbf745cde3c4adf98904f0bb29deee0' またはプロシージャ 'DECLARE @credit_num AS NVARCHAR (10) = @p7fbf745cde3c4adf98904f0bb29deee0;

まとめ

　実際にAlways Encryptedを使用して、暗号化・復号化操作を行ってみましたが、操作感はそこまで難しくないものと思いました。

　しかし、複数の利用制約があるため高セキュリティの要件がない限りは動的データマスキングの方が保守運用はしやすそうです。