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 Portal上に、以下の記事に従ってKey Vaultを作成済であること。
なお、Azure Portalにログインするユーザーの、Key Vaultのアクセスポリシーが以下のようになっていること。
さらに、ローカルのWindows端末上に、バージョン18以降のSSMS(SQL Server Management Studio)をインストール済であること。なお、SSMSのインストール手順については以下のサイトを参照のこと。
https://www.ipentec.com/document/sql-server-install-sql-server-management-studio-for-sql-server-2019
やってみたこと
- SSMSによるSQL Databaseへのログイン
- 暗号化カラムを含むテーブルの作成とデータ追加
- SSMSを使ったAlways Encryptedによるカラム暗号化
- SSMSで暗号化されたカラムを復号化し表示
SSMSによるSQL Databaseへのログイン
ローカルのWindows端末上にインストール済のSSMS(SQL Server Management Studio)を利用して、Azure上のSQLデータベースにログインする。その手順は以下の通り。
1) スタートメニューから、SQL Server Management Studioを選択する。
2) Azure上のSQLデータベースへのログイン情報を指定し、「接続(Connect)」ボタンを押下する。
なお、上記SQL Databaseの接続先は、以下のSQL Databaseへの接続文字列を参照のこと。
3) Azure上のSQLデータベースに接続し、データベースの中身を確認すると、以下のようになる。
暗号化カラムを含むテーブルの作成とデータ追加
次に、Always Encryptedで暗号化するカラムを含むテーブルを作成し、データを追加する。その手順は以下の通り。
1) 「New Query」ボタンを押下する。
2) データベースを、テーブル作成用データベースに変更する。
3) テーブル「dbo.USER_PASS」を新規で作成するSQLを入力し、「Execute」ボタンを押下する。
4)「更新」ボタンを押下すると、「dbo.USER_PASS」テーブルが作成されていることが確認できる。
5) 作成したテーブル「dbo.USER_PASS」にデータを追加するSQLを入力し、「Execute」ボタンを押下する。
6) 作成したテーブル「dbo.USER_PASS」のデータを確認するSQLを入力し、「Execute」ボタンを押下すると、追加したデータが確認できる。
SSMSを使ったAlways Encryptedによるカラム暗号化
SSMSを使って、作成した「dbo.USER_PASS」テーブルの「pass_encrypted」を、Always Encryptedを利用して暗号化する。その手順は以下の通り。
1) 作成した「dbo.USER_PASS」テーブルを選択し右クリックし、「カラムの暗号化(Encrypt Columns)」を選択する。
2) 以下の画面が表示されるため、「次へ(Next)」ボタンを押下する。
3) カラム「pass_encrypted」のチェックを選択し、暗号化タイプ(Encryption Type)で「Deterministic(決定的)」を選択する。
なお、暗号化タイプには、「Deterministic(決定的)」と「Randomized(ランダム化)」の2種類がある。その内容については、以下のサイトを参照のこと。
https://docs.microsoft.com/ja-jp/sql/relational-databases/security/encryption/always-encrypted-cryptography?view=sql-server-ver15
4) 下記の状態で「次へ(Next)」ボタンを押下する。
5)「Azure Key Vault」を選択し、「サインイン(Sign In)」ボタンを押下する。
なお、「Windows certificate store」を選択すると、ローカルのWindows端末上の、証明書 ストア と呼ばれる記憶域の場所にコンピューターのローカルに証明書を保存することになる。今回は、後にAzure上で「dbo.USER_PASS」テーブル上にアクセスするプログラムを作成するため、「Azure Key Vault」を選択する。
6) 以下のようにサインイン画面が表示されるため、Azure Portalにログインする際のメールアドレスを入力し、「次へ」ボタンを押下する。
7) Azure Portalにログインする際のパスワードを入力し、「サインイン」ボタンを押下する。
8) サインインすると、Azure Portal上のKey Vaultが設定される。ここで「次へ(Next)」ボタンを押下する。
9) そのまま、「次へ(Next)」ボタンを押下する。
10) 表示される設定内容を確認後、「完了(Finish)」ボタンを押下する。
11) Always Encryptedの設定中に以下の画面が表示されるため、アカウントを選択する。
12) Always Encryptedの設定が完了すると、以下の画面が表示されるため、「閉じる(Close)」ボタンを押下する。
13) SSMS上で、再度select文を実行すると、以下のように、カラム「pass_encrypted」が暗号化されていることが確認できる。
14) また、Always Encryptedに利用するキーが、以下のように作成されていることが確認できる。
15) Always Encrypted設定で作成された、列マスター キー(CMK_Auto1)の中身を確認すると、以下の通り。作成された列マスターキーを選択し右クリックし、CREATE文を表示すると、その中身が確認できる。なお、列マスターキーとは、後述の列暗号化キーの暗号化に使用される保護キーのことをいう。
16) Always Encrypted設定で作成された、列暗号化キー(CEK_Auto1)の中身を確認すると、以下の通り。作成された列暗号化キーを選択し右クリックし、CREATE文を表示すると、その中身が確認できる。なお、列暗号化キーとは、データを暗号化するために使用される内容暗号化キーのことをいう。
17) Azure Portalにログインし、Azure Key Vaultのキーを確認すると、以下のように、列マスターキーが作成されていることが確認できる。ここで一覧の「CMKAuto1」を表示すると、列マスターキーの内容が確認できる。
以下の画面が表示されるため、現在のバージョンを押下する。
以下のように、作成された列マスターキーのキー識別子等が確認できる。
SSMSで暗号化されたカラムを復号化し表示
SSMS でデータベースに接続する場合は、データベース接続について Always Encrypted を有効にすることで、暗号化されたカラムを復号化し表示することができる。その手順は以下の通り。
1) SSMS(SQL Server Management Studio)からログアウトし、再度SSMSにログインするためのログイン情報を指定し、「Options」ボタンを押下する。
2)「Always Encrypted」タブの「Enable Always Encrypted」を選択し、「接続(Connect)」ボタンを押下する。なお、「Always Encrypted」タブは、SSMS 18以降のバージョンで追加されている。
3) dbo.USER_PASSテーブルのselect文を指定し、「Execute」ボタンを押下する。
4) Azureへログインするためのアカウントのリストが表示されるため、ログインするアカウントを選択する。
5) select文が実行され、暗号化したカラム「pass_encrypted」を復号化して表示されることが確認できる。
要点まとめ
- Always Encryptedを利用すると、SQL DatabaseやSQL Serverのデータベースに格納された、クレジットカード番号や身分登録番号 (アメリカの社会保障番号など) などの機微なデータを暗号化して格納することができる。
- Always Encryptedで使用する暗号化キーを保護するための列マスターキーは、Azure Key Vault上に作成できる。
- Always Encryptedの設定は、SSMS(SQL Server Management Studio)を使って設定することができる。
- SSMSで暗号化されたカラムを復号化し表示するには、SSMSで「Enable Always Encrypted」を選択しログインすればよい。
