Always Encryptedで暗号化されたカラムの暗号化キーを保護するための列マスターキーを、Azure Key Vault上に作成している場合、SSMS(SQL Server Management Studio)からAzure Portalに接続できない場合は、SSMS上でAlways Encryptedで暗号化されたカラムを更新することができない。
ただし、PowerShellからAzure Portalに接続できる場合は、PowerShellを用いてAlways Encryptedで暗号化されたカラムの暗号化を一時解除した後で、SSMS上でデータ更新を行い、PowerShellを用いて再度暗号化することで、Always Encryptedで暗号化されたカラムを更新することができる。
今回は、その手順でAlways Encryptedで暗号化されたカラムを一時的に復号化して更新してみたので、共有する。
前提条件
下記記事の実装が完了していること。
また、PowerShellのバージョンは5以上で、SqlServerモジュールがインストールされているものとする。
なお、SqlServerモジュールのインストール方法は、以下のサイトを参照のこと。
https://docs.microsoft.com/ja-jp/sql/powershell/download-sql-server-ps-module?view=sql-server-ver15
Always Encryptedで暗号化されたカラムの更新手順
SSMS上でAlways Encryptedで暗号化されたカラムを更新することができない場合は、PowerShellを用いてAlways Encryptedで暗号化されたカラムの暗号化を一時解除した後で、SSMS上でデータ更新を行い、PowerShellを用いて再度暗号化する。その手順は、以下の通り。
1) SSMS(SQL Server Management Studio)上でUSER_PASSテーブルを確認すると、以下のように、列「pass_encrypted」が暗号化されていることが確認できる。
1 | select * from dbo.USER_PASS |
2) 「Windows PowerShell」メニューから、「Windows PowerShell」を選択し、PowerShellを起動する。
起動したPowerShellの画面は、以下の通り。
3) SqlServerモジュールをインポートする。
1 | Import-Module "SqlServer" |
4) 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」で確認できる。
5) Azureにサインインする。
1 | Add-SqlAzureAuthenticationContext -Interactive |
上記コマンドを指定すると、サインインするための画面が表示されるため、Azure Portalにログインするためのメールアドレスを指定し、「次へ」ボタンを押下する。
パスワードの入力を求められるため、パスワードを入力し「サインイン」ボタンを押下する。
サインインが完了すると、以下のように、プロンプトが終了する。
6) Always Encryptedにより暗号化された列の復号化を実施する。
1 2 3 | $ces = @() $ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.USER_PASS.pass_encrypted" -EncryptionType "Plaintext" Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -LogFileDirectory . |
なお、Always Encryptedにより列の復号化中は、以下の画面が表示される。
7) SSMS上でUSER_PASSテーブルを確認すると、以下のように、列「pass_encrypted」の暗号化が解除されていることが確認できる。
1 | select * from dbo.USER_PASS |
また、Always Encryptedのマスターキー、暗号化キーは、以下のように残ったままであることが確認できる。
8) SSMS上で、USER_PASSテーブルのデータ更新を行う。
1 | update dbo.USER_PASS set pass='changePass', pass_encrypted='changePass' where id = 1 |
9) SSMS上でUSER_PASSテーブルを確認すると、以下のように、列「pass」「pass_encrypted」が更新されていることが確認できる。
1 | select * from dbo.USER_PASS |
10) 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により列の暗号化中は、以下の画面が表示される。
11) SSMS上でUSER_PASSテーブルを確認すると、以下のように、列「pass_encrypted」が再び暗号化されていることが確認できる。
1 | select * from dbo.USER_PASS |
12) 以下の記事のプログラムをローカル環境で実行し確認する。
ローカル環境のApp Service、Azure Functionsを起動し、「http:// (ホスト名):(ポート番号)」とアクセス後、「getUserPassの値を取得」ボタンを押下すると、以下のように、更新後のパスワードが表示されることが確認できる。
要点まとめ
- Always Encryptedで暗号化されたカラムを一時的に復号化して更新するには、PowerShellを用いてAlways Encryptedで暗号化されたカラムの暗号化を一時解除した後で、SSMS上でデータ更新を行い、PowerShellを用いて再度暗号化すればよい。
