SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Azure Key Vault を使用した Always Encrypted を PowerShell から使用してみる

leave a comment

以前、Azure Key Vault を使用した Always Encrypted による暗号化 という投稿を書いたのですが、Key Vault を使用した、Always Encrypted のデータ操作を PowerShell から実行するための方法をざっくりと。
Key Vault を使用して暗号化されたテーブルの操作は、何も考えずに操作しようとすると、以下のようなエラーとなりますので、これの対応です。

"0" 個の引数を指定して "ExecuteReader" を呼び出し中に例外が発生しました: "キー ストア プロバイダー 'AZURE_KEY_VAULT' を使用して列暗号化キーを暗号化解除できませんでした。
データーベースにある列暗号化キーのプロパティと列マスター キーを確認してください。
暗号化された列暗号化キーの最後の 10 バイトは 'ほげほげ'
です。

本投稿で使用しているソースについては、以下のサンプルを使わせていただいています。
Using the Azure Key Vault Key Store Provider for Always Encrypted

SQL Database を対象として、Always Encrypted を使用する場合、証明書か Key Vault に CMK (列マスターキー) を格納して使用することになるかと。
接続元が複数台あると、各環境に証明書を配置するのは手間がかかりますので、複数台にリニアにスケールするような環境を使用する場合は、Key Vault を使用することになるかと思います。
暗号化のプロバイダーとして、Azure Key Vault は組み込まれていないため、Key Vault 経由で暗号化をしているものについては、プロバイダーを組み込んでからデータにアクセスする必要があります。
現状、プロバイダーの組み込みについては、クラスライブラリを作り、PowerShell 側ではそれをロードする形で実施するのがシンプルっぽいですね。
すべて PowerShell で実施しようとしていたところ、中々うまくできなかったのですよね…。
Always Encrypted は .NET Framework 4.6 以降で使用できるものですので、作成するクラスライブラリは、「.NET Framework 4.6」向けに作成する必要があります。
image
また、以下のパッケージが必要となりますので NuGet でインストールしておきます。
# 2016 の SSMS の Extensions にも入っているのですが、Microsoft.IdentityModel.Clients.ActiveDirectory が古いのでNugetの最新のものを使用します。

Install-Package Microsoft.Azure.KeyVault
Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.19.208020213

Microsoft Azure Key Vault Library
Active Directory Authentication Library
また、SQL Server 2016 の SSMS をインストールすると追加されている「c:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.dll」も参照設定を追加しておきます。
これで、以下のようなソースで、Azure AD に登録した、「アプリケーションのクライアント ID」と「アプリケーションのキー」を称して、Key Vault プロバイダーを生成することができるようになります。

// Install-Package Microsoft.Azure.KeyVault
// Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory -Version 2.19.208020213
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider;
using System.Data.SqlClient;
namespace AzureKeyVault
{
    public static class Keyvault
    {
        private static ClientCredential _clientCredential;
        public static Dictionary<string, SqlColumnEncryptionKeyStoreProvider> InitializeAzureKeyVaultProvider(string clientId, string clientSecret)
        {
            _clientCredential = new ClientCredential(clientId, clientSecret);
            // Direct the provider to the authentication delegate
            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);
            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
            providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
            // register the provider with ADO.net
            return providers;
        }
        public async static Task<string> GetToken(string authority, string resource, string scope)
        {
            var authContext = new AuthenticationContext(authority);
            AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);
            if (result == null)
                throw new InvalidOperationException("Failed to obtain the JWT token");
            return result.AccessToken;
        }
    }
}

 
あとは、作成したクラスからプロバイダーを作成して、「RegisterColumnEncryptionKeyStoreProviders」を使用してKey Vault をプロバイダーとして登録します。
データの挿入を行う場合は、以下のような形でしょうか。

Add-Type -Path "C:\KeyVault\KeyVault.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Threading.Tasks.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Threading.Tasks.Extensions.Desktop.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Threading.Tasks.Extensions.dll"
Add-Type -Path "C:\KeyVault\System.Net.Http.Extensions.dll"
Add-Type -Path "C:\KeyVault\System.Net.Http.Primitives.dll"
Add-Type -Path "C:\KeyVault\Newtonsoft.Json.dll"
Add-Type -Path "C:\KeyVault\Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.dll"
Add-Type -Path "C:\KeyVault\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
Add-Type -Path "C:\KeyVault\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
Add-Type -Path "C:\KeyVault\Hyak.Common.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Azure.Common.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Azure.Common.NetFramework.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Azure.KeyVault.dll"
$clientid = "<アプリケーションクライアントID>"
$secret = "<アプリケーションのキー>"
[System.Data.SqlClient.SqlConnection]::RegisterColumnEncryptionKeyStoreProviders([AzureKeyVault.KeyVault]::InitializeAzureKeyVaultProvider($clientid, $secret))
$connectionString = "Data Source=localhost; Integrated Security=true;Database=EncTEST; Column Encryption Setting=enabled";
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand
$command.Connection = $connection
$command.CommandType = [System.Data.CommandType]::Text
$command.CommandText = "INSERT INTO AlwaysEncrypted VALUES(@Name,@PersonalId,@Age)"
$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Name",[Data.SQLDBType]::NVarChar, 60))) | Out-Null
$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PersonalId",[Data.SQLDBType]::VarChar, 11))) | Out-Null
$command.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@Age",[Data.SQLDBType]::Int))) | Out-Null
$Command.Parameters[0].Value = "Jim Gray"
$Command.Parameters[1].Value = "111-22-3333"
$Command.Parameters[2].Value = 63
$command.ExecuteNonQuery()
$command.ExecuteNonQuery()

 
Key Vault を使用するために実施したいのは、以下の一行だけなのですよね。

[System.Data.SqlClient.SqlConnection]::RegisterColumnEncryptionKeyStoreProviders([AzureKeyVault.KeyVault]::InitializeAzureKeyVaultProvider($clientid, $secret))

 
これで、KeyVault を使用して、暗号化を行ったデータをつかすることができます。
検索の場合は、以下のような感じですね。

Add-Type -Path "C:\KeyVault\KeyVault.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Threading.Tasks.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Threading.Tasks.Extensions.Desktop.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Threading.Tasks.Extensions.dll"
Add-Type -Path "C:\KeyVault\System.Net.Http.Extensions.dll"
Add-Type -Path "C:\KeyVault\System.Net.Http.Primitives.dll"
Add-Type -Path "C:\KeyVault\Newtonsoft.Json.dll"
Add-Type -Path "C:\KeyVault\Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.dll"
Add-Type -Path "C:\KeyVault\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
Add-Type -Path "C:\KeyVault\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
Add-Type -Path "C:\KeyVault\Hyak.Common.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Azure.Common.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Azure.Common.NetFramework.dll"
Add-Type -Path "C:\KeyVault\Microsoft.Azure.KeyVault.dll"
$clientid = "<アプリケーションクライアントID>"
$secret = "<アプリケーションのキー>"
[System.Data.SqlClient.SqlConnection]::RegisterColumnEncryptionKeyStoreProviders([AzureKeyVault.KeyVault]::InitializeAzureKeyVaultProvider($clientid, $secret))
$connectionString = "Data Source=localhost; Integrated Security=true;Database=EncTEST; Column Encryption Setting=enabled";
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command4 = New-Object System.Data.SqlClient.SqlCommand
$command4.Connection = $connection
$command4.CommandType = [System.Data.CommandType]::Text
$command4.CommandText = "SELECT * FROM AlwaysEncrypted WHERE PersonalID = @PersonalID"
$command4.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PersonalId",[Data.SQLDBType]::VarChar, 11))) | Out-Null
$command4.Parameters[0].Value = "111-22-3333"
$reader = $command4.ExecuteReader()
$sb = New-Object System.Text.StringBuilder
while($reader.Read())
{
    Write-Output ("{0} {1} {2}" -f $reader[0], $reader[1], $reader[2])
 }
 $reader.Close()

 
Azure 側で回答のアプリケーションのKeyを削除/期限切れが発生した場合は、新しいキーを作成して、そのキーを使用するようにアプリケーション側を変更すればよい感じですかね。
CTP 3.1 になって、SSMS の検索については、Key Vault を使用して暗号化している場合は、以下のように認証のダイアログが表示され、キーにアクセスできるアカウントの情報を入れることで、複合化してデータを確認することができます。
image
ただし、データのインポート/エクスポートウィザードについては、対応されていないようで、非暗号化テーブルを暗号化テーブルにしたい場合などに、どのような動作になるかは確認が必要そうでした。
Key Vault は、証明書と異なり、各環境に何かをインポートする必要がなく、キーのローテーションをする際の変更対応も楽なのですが、カスタムプロバイダー扱いですので、各操作にどのように影響するかは気を付けておいた方がよさそうでした。
追記
PowerSHell 内で、C# のコードをコンパイルして使う場合は以下のような感じでしょうか。
参照設定で使用する DLL としては、以下があれば足りそうでした。

$clientid = "<アプリケーションクライアントID>"
$clientSecret = "<アプリケーションのキー>"
$assemblypath = "C:\KeyVault\"
$assembly = @(
"System.Data"
, "$assemblypath\Hyak.Common.dll"
, "$assemblypath\Newtonsoft.Json.dll"
, "$assemblypath\Microsoft.Azure.KeyVault.dll"
, "$assemblypath\Microsoft.Azure.Common.dll"
, "$assemblypath\Microsoft.Azure.Common.NetFramework.dll"
, "$assemblypath\Microsoft.Threading.Tasks.dll"
, "$assemblypath\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
, "$assemblypath\Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.dll"
)
$assembly -like "*.dll" | %{Add-Type -Path $_}
$script = @"
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider;
using System.Data.SqlClient;
namespace AzureKeyVault
{
    public static class Keyvault
    {
        private static ClientCredential _clientCredential;
        public static Dictionary<string, SqlColumnEncryptionKeyStoreProvider> InitializeAzureKeyVaultProvider(string clientId, string clientSecret)
        {
            _clientCredential = new ClientCredential(clientId, clientSecret);
            // Direct the provider to the authentication delegate
            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);
            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
            providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
            // register the provider with ADO.net
            return providers;
        }
        public async static Task<string> GetToken(string authority, string resource, string scope)
        {
            var authContext = new AuthenticationContext(authority);
            AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);
            if (result == null)
                throw new InvalidOperationException("Failed to obtain the JWT token");
            return result.AccessToken;
        }
    }
}
"@
Add-Type -TypeDefinition $script -Language CSharp -ReferencedAssemblies $assembly
[System.Data.SqlClient.SqlConnection]::RegisterColumnEncryptionKeyStoreProviders([AzureKeyVault.KeyVault]::InitializeAzureKeyVaultProvider($ClientId, $ClientSecret))
$connectionString = "Data Source=localhost; Integrated Security=true;Database=EncTEST; Column Encryption Setting=enabled";
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command4 = New-Object System.Data.SqlClient.SqlCommand
$command4.Connection = $connection
$command4.CommandType = [System.Data.CommandType]::Text
$command4.CommandText = "SELECT * FROM AlwaysEncrypted WHERE PersonalID = @PersonalID"
$command4.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@PersonalId",[Data.SQLDBType]::VarChar, 11))) | Out-Null
$command4.Parameters[0].Value = "111-22-3333"
$reader = $command4.ExecuteReader()
$sb = New-Object System.Text.StringBuilder
while($reader.Read())
{
    Write-Output ("{0} {1} {2}" -f $reader[0], $reader[1], $reader[2])
 }
 $reader.Close()
Share

Written by Masayuki.Ozawa

12月 5th, 2015 at 9:37 am

Posted in SQL Database,SQL Server

Tagged with

«
»

Leave a Reply