Always Encrypted

SQL DatabaseのカラムをSSMSを使ってAlways Encryptedで暗号化してみた

Always Encryptedを利用すると、SQL DatabaseやSQL Serverのデータベースに格納された、クレジットカード番号や身分登録番号 (アメリカの社会保障番号など) などの機微なデータを暗号化して格納することができる。

今回は、SSMS(SQL Server Management Studio)を使って、SQL DatabaseのカラムをAlways Encryptedで暗号化してみたので、その手順を共有する。また、その際の暗号化キーを保護するための列マスターキーは、Azure Key Vault上に作成する。

なお、Always Encryptedについては、以下のサイトを参照のこと。
https://docs.microsoft.com/ja-jp/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15

前提条件

Azure Portal上に、以下の記事に従ってSQL Databaseのデータベースを作成済であること。

Azure Potal上でSQLデータベースを作成してみたAzure Portal上では、常に最新の安定したバージョンの SQL Serverデータベースである「SQL データベース」が利用でき...

また、Azure Portal上に、以下の記事に従ってKey Vaultを作成済であること。

Azure Key Vaultを作成しKey Vaultのシークレットを取得してみた(ソースコード以外編)Azure Key Vaultを利用すると、パスワード等の機密情報へのアクセスをAzure Portal上のみに制限することができる。今...

なお、Azure Portalにログインするユーザーの、Key Vaultのアクセスポリシーが以下のようになっていること。
前提条件_1

前提条件_2

さらに、ローカルのWindows端末上に、バージョン18以降のSSMS(SQL Server Management Studio)をインストール済であること。なお、SSMSのインストール手順については以下のサイトを参照のこと。
https://www.ipentec.com/document/sql-server-install-sql-server-management-studio-for-sql-server-2019

やってみたこと

  1. SSMSによるSQL Databaseへのログイン
  2. 暗号化カラムを含むテーブルの作成とデータ追加
  3. SSMSを使ったAlways Encryptedによるカラム暗号化
  4. SSMSで暗号化されたカラムを復号化し表示

SSMSによるSQL Databaseへのログイン

ローカルのWindows端末上にインストール済のSSMS(SQL Server Management Studio)を利用して、Azure上のSQLデータベースにログインする。その手順は以下の通り。

1) スタートメニューから、SQL Server Management Studioを選択する。
SSMSログイン_1

2) Azure上のSQLデータベースへのログイン情報を指定し、「接続(Connect)」ボタンを押下する。
SSMSログイン_2_1

なお、上記SQL Databaseの接続先は、以下のSQL Databaseへの接続文字列を参照のこと。
SSMSログイン_2_2

3) Azure上のSQLデータベースに接続し、データベースの中身を確認すると、以下のようになる。
SSMSログイン_3



暗号化カラムを含むテーブルの作成とデータ追加

次に、Always Encryptedで暗号化するカラムを含むテーブルを作成し、データを追加する。その手順は以下の通り。

1) 「New Query」ボタンを押下する。
テーブルの作成_1

2) データベースを、テーブル作成用データベースに変更する。
テーブルの作成_2

3) テーブル「dbo.USER_PASS」を新規で作成するSQLを入力し、「Execute」ボタンを押下する。
テーブルの作成_3

4)「更新」ボタンを押下すると、「dbo.USER_PASS」テーブルが作成されていることが確認できる。
テーブルの作成_4

5) 作成したテーブル「dbo.USER_PASS」にデータを追加するSQLを入力し、「Execute」ボタンを押下する。
テーブルの作成_5

6) 作成したテーブル「dbo.USER_PASS」のデータを確認するSQLを入力し、「Execute」ボタンを押下すると、追加したデータが確認できる。
テーブルの作成_6



SSMSを使ったAlways Encryptedによるカラム暗号化

SSMSを使って、作成した「dbo.USER_PASS」テーブルの「pass_encrypted」を、Always Encryptedを利用して暗号化する。その手順は以下の通り。

1) 作成した「dbo.USER_PASS」テーブルを選択し右クリックし、「カラムの暗号化(Encrypt Columns)」を選択する。
AlwaysEncryptedによる暗号化_1

2) 以下の画面が表示されるため、「次へ(Next)」ボタンを押下する。
AlwaysEncryptedによる暗号化_2

3) カラム「pass_encrypted」のチェックを選択し、暗号化タイプ(Encryption Type)で「Deterministic(決定的)」を選択する。
AlwaysEncryptedによる暗号化_3

なお、暗号化タイプには、「Deterministic(決定的)」と「Randomized(ランダム化)」の2種類がある。その内容については、以下のサイトを参照のこと。
https://docs.microsoft.com/ja-jp/sql/relational-databases/security/encryption/always-encrypted-cryptography?view=sql-server-ver15

4) 下記の状態で「次へ(Next)」ボタンを押下する。
AlwaysEncryptedによる暗号化_4

5)「Azure Key Vault」を選択し、「サインイン(Sign In)」ボタンを押下する。
AlwaysEncryptedによる暗号化_5

なお、「Windows certificate store」を選択すると、ローカルのWindows端末上の、証明書 ストア と呼ばれる記憶域の場所にコンピューターのローカルに証明書を保存することになる。今回は、後にAzure上で「dbo.USER_PASS」テーブル上にアクセスするプログラムを作成するため、「Azure Key Vault」を選択する。

6) 以下のようにサインイン画面が表示されるため、Azure Portalにログインする際のメールアドレスを入力し、「次へ」ボタンを押下する。
AlwaysEncryptedによる暗号化_6

7) Azure Portalにログインする際のパスワードを入力し、「サインイン」ボタンを押下する。
AlwaysEncryptedによる暗号化_7

8) サインインすると、Azure Portal上のKey Vaultが設定される。ここで「次へ(Next)」ボタンを押下する。
AlwaysEncryptedによる暗号化_8

9) そのまま、「次へ(Next)」ボタンを押下する。
AlwaysEncryptedによる暗号化_9

10) 表示される設定内容を確認後、「完了(Finish)」ボタンを押下する。
AlwaysEncryptedによる暗号化_10

11) Always Encryptedの設定中に以下の画面が表示されるため、アカウントを選択する。
AlwaysEncryptedによる暗号化_11

12) Always Encryptedの設定が完了すると、以下の画面が表示されるため、「閉じる(Close)」ボタンを押下する。
AlwaysEncryptedによる暗号化_12

13) SSMS上で、再度select文を実行すると、以下のように、カラム「pass_encrypted」が暗号化されていることが確認できる。
AlwaysEncryptedによる暗号化_13

14) また、Always Encryptedに利用するキーが、以下のように作成されていることが確認できる。
AlwaysEncryptedによる暗号化_14

15) Always Encrypted設定で作成された、列マスター キー(CMK_Auto1)の中身を確認すると、以下の通り。作成された列マスターキーを選択し右クリックし、CREATE文を表示すると、その中身が確認できる。なお、列マスターキーとは、後述の列暗号化キーの暗号化に使用される保護キーのことをいう。
AlwaysEncryptedによる暗号化_15_1

AlwaysEncryptedによる暗号化_15_2

16) Always Encrypted設定で作成された、列暗号化キー(CEK_Auto1)の中身を確認すると、以下の通り。作成された列暗号化キーを選択し右クリックし、CREATE文を表示すると、その中身が確認できる。なお、列暗号化キーとは、データを暗号化するために使用される内容暗号化キーのことをいう。
AlwaysEncryptedによる暗号化_16_1

AlwaysEncryptedによる暗号化_16_2

17) Azure Portalにログインし、Azure Key Vaultのキーを確認すると、以下のように、列マスターキーが作成されていることが確認できる。ここで一覧の「CMKAuto1」を表示すると、列マスターキーの内容が確認できる。
AlwaysEncryptedによる暗号化_17_1

以下の画面が表示されるため、現在のバージョンを押下する。
AlwaysEncryptedによる暗号化_17_2

以下のように、作成された列マスターキーのキー識別子等が確認できる。
AlwaysEncryptedによる暗号化_17_3



SSMSで暗号化されたカラムを復号化し表示

SSMS でデータベースに接続する場合は、データベース接続について Always Encrypted を有効にすることで、暗号化されたカラムを復号化し表示することができる。その手順は以下の通り。

1) SSMS(SQL Server Management Studio)からログアウトし、再度SSMSにログインするためのログイン情報を指定し、「Options」ボタンを押下する。
AlwaysEncryptedを利用可能にする_1

2)「Always Encrypted」タブの「Enable Always Encrypted」を選択し、「接続(Connect)」ボタンを押下する。なお、「Always Encrypted」タブは、SSMS 18以降のバージョンで追加されている。
AlwaysEncryptedを利用可能にする_2

3) dbo.USER_PASSテーブルのselect文を指定し、「Execute」ボタンを押下する。
AlwaysEncryptedを利用可能にする_3

4) Azureへログインするためのアカウントのリストが表示されるため、ログインするアカウントを選択する。
AlwaysEncryptedを利用可能にする_4

5) select文が実行され、暗号化したカラム「pass_encrypted」を復号化して表示されることが確認できる。
AlwaysEncryptedを利用可能にする_5

要点まとめ

  • Always Encryptedを利用すると、SQL DatabaseやSQL Serverのデータベースに格納された、クレジットカード番号や身分登録番号 (アメリカの社会保障番号など) などの機微なデータを暗号化して格納することができる。
  • Always Encryptedで使用する暗号化キーを保護するための列マスターキーは、Azure Key Vault上に作成できる。
  • Always Encryptedの設定は、SSMS(SQL Server Management Studio)を使って設定することができる。
  • SSMSで暗号化されたカラムを復号化し表示するには、SSMSで「Enable Always Encrypted」を選択しログインすればよい。