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

以前、SQL DatabaseのカラムをAlways Encryptedで暗号化するのに、SSMS(SQL Server Management Studio)を使っていたが、バージョンが5以上のPower Shellを利用してもAlways Encryptedによる暗号化を行うこともできる。

今回は、SQL DatabaseのカラムをPowerShellを使ってAlways Encryptedで暗号化してみたので、その手順を共有する。

前提条件

下記記事の実装が完了していること。

Always Encryptedで暗号化されたカラムを更新してみたJDBC接続文字列に「columnEncryptionSetting=Enabled」を追加し、Azure Key Vault に対する...

また、PowerShellのバージョンは5以上で、SqlServerモジュールがインストールされているものとする。

なお、SqlServerモジュールのインストール方法は、以下のサイトを参照のこと。
https://docs.microsoft.com/ja-jp/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15

USER_PASSテーブルの再作成

USER_PASSテーブルの再作成は、以下の記事の「USER_PASSテーブルの再作成」の項番1)～4)を実施することによって行う。

Always Encryptedで暗号化されたカラムを格納するバイト数を調べてみたAlways Encryptedで暗号化されたカラムを格納するのに必要なバイト数の情報は、以下のサイトに記載されている。 https:...

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モジュールをインポートする。

Import-Module "SqlServer"

3) Azure上のSQLデータベースに接続する。

$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による暗号化に利用する、マスターキーを作成する。

$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にサインインする。

Add-SqlAzureAuthenticationContext -Interactive

上記コマンドを指定すると、サインインするための画面が表示されるため、Azure Portalにログインするためのメールアドレスを指定し、「次へ」ボタンを押下する。

パスワードの入力を求められるため、パスワードを入力し「サインイン」ボタンを押下する。

サインインが完了すると、以下のように、プロンプトが終了する。

6) Always Encryptedによる暗号化に利用する、暗号化キーを作成する。

$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

7) SSMS(SQL Server Management Studio)上で確認すると、以下のように、マスターキー(CMK1)・暗号化キー(CEK1)が作成されていることが確認できる。

8) 作成した暗号化キー「CEK1」を利用して、Always Encryptedにより列の暗号化を実施する。

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.USER_PASS.pass_encrypted" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .