以前、SQL DatabaseのカラムをAlways Encryptedで暗号化するのに、SSMS(SQL Server Management Studio)を使っていたが、バージョンが5以上のPower Shellを利用してもAlways Encryptedによる暗号化を行うこともできる。
今回は、SQL DatabaseのカラムをPowerShellを使ってAlways Encryptedで暗号化してみたので、その手順を共有する。
前提条件
下記記事の実装が完了していること。
また、PowerShellのバージョンは5以上で、SqlServerモジュールがインストールされているものとする。
なお、SqlServerモジュールのインストール方法は、以下のサイトを参照のこと。
https://docs.microsoft.com/ja-jp/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15
やってみたこと
- USER_PASSテーブルの再作成
- Azure Key Vault上のキー再作成
- SSMS上でマスターキー・暗号化キーの削除
- PowerShellを利用したAlways Encryptedの暗号化
- 暗号化文字列の更新と確認
USER_PASSテーブルの再作成
USER_PASSテーブルの再作成は、以下の記事の「USER_PASSテーブルの再作成」の項番1)～4)を実施することによって行う。
Azure Key Vault上のキー再作成
Azure Key Vault上のキー再作成は、Azure Portalにログインして行う。その手順は以下の通り。
1) Azure Portalにログインし、作成済のKey Vault「keyPurinit」を選択する。
2) 「キー」メニューを選択する。
3) 作成済のキーが一覧で表示されるので、表示された「CMKAuto1」を選択する。
4) このキーを削除するため、「削除」ボタンを押下する。
5) 削除確認メッセージが表示されるため、「はい」ボタンを押下する。
6) 削除が完了すると、右上にメッセージが表示される。
7) 次に新たなキーを作成するため、「生成/インポート」ボタンを押下する。
8) キーの名前を指定し、他はそのまま表示された状態で「作成」ボタンを押下する。
9) キーが作成されると、一覧に作成されたキーが表示される。ここで一覧の「CMK1」を押下する。
10) 一覧に表示されたバージョンを押下する。
11) 以下のように、作成したキーの識別子や許可された操作が確認できる。
SSMS上でマスターキー・暗号化キーの削除
SSMS(SQL Server Management Studio)上で、Always Encryptedで暗号化する際に必要なマスターキー・暗号化キーを削除する。その手順は以下の通り。
1) 暗号化キー(CEK_Auto1)を選択し右クリックし、「Delete」メニューを選択する。
2) 削除するオブジェクトの確認画面が表示されるため、「OK」ボタンを押下する。
3) 以下のように、暗号化キー(CEK_Auto1)が削除されたことが確認できる。
4) マスタキー(CMK_Auto1)を選択し右クリックし、「Delete」メニューを選択する。
5) 削除するオブジェクトの確認画面が表示されるため、「OK」ボタンを押下する。
6) 以下のように、マスターキー(CMK_Auto1)が削除されたことが確認できる。
PowerShellを利用したAlways Encryptedの暗号化
PowerShellを利用して、再作成したUSER_PASSテーブルの「pass_encrypted」を暗号化する。その手順は以下の通り。
1) 「Windows PowerShell」メニューから、「Windows PowerShell」を選択し、PowerShellを起動する。
起動したPowerShellの画面は、以下の通り。
2) SqlServerモジュールをインポートする。
1 | Import-Module "SqlServer" |
3) Azure上のSQLデータベースに接続する。
1 2 | $connStr = "Server=tcp:azure-db-purinit.database.windows.net,1433;Initial Catalog=azureSqlDatabase;Persist Security Info=False;User ID=purinit;Password=[DB接続のパスワード];MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" $database = Get-SqlDatabase -ConnectionString $connStr |
なお、上記コマンドで指定しているDB接続先($connStr)は、Azure Portal上のSQLデータベースの「接続先」メニュー、「ADO.NET」で確認できる。
4) Always Encryptedによる暗号化に利用する、マスターキーを作成する。
1 2 3 | $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl "https://keypurinit.vault.azure.net/keys/CMK1/2291631db4e7416b83aee2c86560bb27" $cmkName = "CMK1" New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings |
なお、$cmkSettings設定時のKeyUrlは、以下のAzure Portal上のキーのバージョンを表示することで確認できる「キー識別子」となる。
5) Azureにサインインする。
1 | Add-SqlAzureAuthenticationContext -Interactive |
上記コマンドを指定すると、サインインするための画面が表示されるため、Azure Portalにログインするためのメールアドレスを指定し、「次へ」ボタンを押下する。
パスワードの入力を求められるため、パスワードを入力し「サインイン」ボタンを押下する。
サインインが完了すると、以下のように、プロンプトが終了する。
6) Always Encryptedによる暗号化に利用する、暗号化キーを作成する。
1 2 | $cekName = "CEK1" New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName |
7) SSMS(SQL Server Management Studio)上で確認すると、以下のように、マスターキー(CMK1)・暗号化キー(CEK1)が作成されていることが確認できる。
8) 作成した暗号化キー「CEK1」を利用して、Always Encryptedにより列の暗号化を実施する。
1 2 3 | $ces = @() $ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.USER_PASS.pass_encrypted" -EncryptionType "Deterministic" -EncryptionKey "CEK1" Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory . |
なお、Always Encryptedにより列の暗号化中は、以下の画面が表示される。
9) SSMS(SQL Server Management Studio)上でUSER_PASSテーブルを確認すると、以下のように、列「pass_encrypted」が暗号化されていることが確認できる。
1 | select * from dbo.USER_PASS |
暗号化文字列の更新と確認
暗号化文字列の更新と確認結果は、以下の記事の「暗号化文字列の更新」と同じ結果となる。
要点まとめ
- SSMSを使わず、バージョンが5以上のPower Shellを利用しても、SQL DatabaseのカラムをAlways Encryptedで暗号化できる。
- Power Shellを利用する場合のAzure Key Vault上のキーは、Azure Portal上で実施する。
- Power Shellを利用する場合のDB接続先は、Azure Portal上のSQLデータベースの「接続先」メニュー、「ADO.NET」で確認できる。
