SQL Server 2016 CTP 2.0 の Always Encrypted を使ってみる at SE の雑記

SQL Server 2016 では、常に暗号化した状態でデータをを扱う Always Encrypted を使用することができます。
この機能を少し試してみました。

詳細については、Always Encrypted (Database Engine) を参照してください。

基本的な考えとしては、証明書を使用した暗号化となるかなと思います。

CREATE COLUMN MASTER KEY DEFINITION (Transact-SQL) を実行して、暗号化に使用するマスターキーがどこにあるかを定義する
CREATE COLUMN ENCRYPTION KEY (Transact-SQL) を実行して、列の暗号化に使用するキーを定義する
テーブルの列に対して、暗号化の属性を付与する

の 3 段階で設定をすることができます。

冒頭で紹介した技術情報はクエリベースで設定をしているのですが、これが少しわかりずらいのではと感じました。

Contents

1
2
3 ■列のマスターキー定義
4 ■列の暗号化キーの設定
5
6 ■暗号化されたデータを格納するテーブルの作成
7 ■データの挿入
8 ■データの検索

■列のマスターキー定義

最初に「列のマスターキー定義」で CMK の場所を設定するのですが、使用する証明書は事前に準備をしておく必要があります。
# このステートメントは証明書の場所を示すものとなりますので、実際に証明書が指定した場所に存在しなくてもエラーにはなりません。

テスト時には SSMS で自己署名証明書が作成できますのでこれを使用するとよいかと思います。

自己署名証明書の作成は、機能を使用したい対象のデータベースの「セキュリティ」→「常に暗号化されたキー」→「列のマスターキー定義」から作成することができます。

ここで自己署名証明書の生成ができますので、まずは証明書を作成します。
今回は証明書ストアについては、「ローカルコンピューター」を指定しています。
これにより、暗号化されたデータにアクセスしたいコンピューターについては、「ローカル証明書」の「個人」に今回作成した自己署名証明書が格納されている必要があります。

今回作成した自己署名証明書を使用した MASTER KEY DEFINITION については以下のようになります。
# 証明書の拇印については証明書毎に代わりますので適切なものを設定します。

USE [ENCTEST]
CREATE COLUMN MASTER KEY DEFINITION [MyCMK]
WITH
(
	KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	KEY_PATH = N'LocalMachine/My/A83CA70AC81CCB0ACE71069294BA92E910A73056'
)
GO

この辺のスクリプトの生成は、適切な証明書を SQL Server 側においてからスクリプト生成機能で設定するとよいかと思います。

今回は Windows 証明書ストアを使用していますが、KeyVault も使えるようなので、どちらが良いかは考慮する必要が出てきそうですね。

CMK 証明書を MakeCert で作成する場合は以下のようなコマンドとなります。

makecert -r -pe -n "CN=Always Encrypted Certificate" -b 01/01/2000 -e 01/01/2100 -eku 1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11 -len 2048 -a sha256 -sp "Microsoft Enhanced RSA and AES Cryptographic Provider" -sy 24 -sky exchange "c:\certtemp\testcert.cer" -sv "c:\certtemp\testcert.pvk"
pvk2pfx -pvk "c:\certtemp\testcert.pvk" -spc "c:\certtemp\testcert.cer" -pfx "c:\certtemp\testcert.pfx"

■列の暗号化キーの設定

列のマスターキー定義でどこにある証明書を使用して、アプリケーションが暗号化を解除するかを設定できたら、次に列の暗号化に実際に使用するキーの定義を行います。

こちらも SSMS から実行したほうがわかりやすいかと。

先ほどと同じ場所の「列の暗号化キー」から設定することができます。

ここでは、前の作業で作成をした CMK を使用して CEK を作成します。

そのため、このタイミングで、CMK の「KEY_PATH」で指定した場所の証明書にアクセスが可能である必要があります。

このタイミングで CMK を作成することもできますので先ほどの作業はここで実施することも可能です。

クエリとしては以下のようなクエリになります。

USE [ENCTEST]
CREATE COLUMN ENCRYPTION KEY [MyCEK]
WITH VALUES
(
	COLUMN MASTER KEY DEFINITION = [MyCMK],
	ALGORITHM = 'RSA_OAEP',
	ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0066006400330036003800620034006600320061003200360039003800340036006400650061006300650038003700330039006600340033006200350038003500370031003500310062003700370037008C730B88DEE99FE6B5675DAC93ACBF79150ED9912C4988EA17505DCFBEE84362B01924A7061833D62B15D38EE80EF5CEEF83F6839C30F9EE856D6EF9AD124068AB5C57AEDA09A5D74DF2EAC3398FAEB7C74FA13475D93B8DA467C3461E0261CA50A3EB9A82B90A4634BCB842C5EE1E2C051D65FAB7A72833EE05F667A905CA7DAAD4910DEA3B20E8CC6955CB8091494053198C835356BC1A52515939DB30399ACF8446697B46CC2867F2C09117C9CB1B3511F888B9FBB06CCDB8550176740D29DC6F3451C39322BBE8B80049B8085D14DC910AC21909516CF3D710FB10274059CA78BEAC78966B0B041554B97D8B5233644B7BB9977AEFAFD07EC22E8604693359B22F287B9A3ED727E3CD4B11423D44EA1983C9634EDF8117C3E7BFAA5BBF9126E78160BF3C8E02C7D827C99A0020C4CCABCADE66866E7838906D9AC8557FBF1DFEC81CCD80D214D5F6C439B4921CC44BD2A9D7D90CE417B3646F827CAFEAC30B76B09A96DDC727226C7A2F833EA2F6519CBFBA6DE047F10202F5876636BDA1B89425EDEC2FCBAA192843394436994D53C5B819C603951CB30BCD1B0DB1D2810690EE94FE1244EBEB8C95A459CEF40451C5675A08BD0395A3981C09CFF635E95824C9B5EF26D428133D153FD9E2207DC62A208F74132CB3E882F70D1A1863D341D8EC98E6D1D980F618ED3B9BA01AA60123ADFAA03B173B7A8ADEA1125ED75D
)
GO

「ENCRYPTED_VALUE」については、CMK を元に生成するものとなりますので、手で設定するということはない項目になるかと。

# CEK 作成時に選択した CMK を元にスクリプト生成により作成されたものを使用する形になります。

これで、暗号化に使用するキーが作成できました。

■暗号化されたデータを格納するテーブルの作成

最後に暗号化されたデータを格納するテーブルを作成します。

今回は以下のようなテーブルを作成しています。

CREATE TABLE Customers (
    CustName nvarchar(60)
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = RANDOMIZED,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
    PersonalId varchar(11)
        COLLATE  Japanese_XJIS_100_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = DETERMINISTIC ,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
    Age int NULL
)
GO

「CustName」については「RANDOMIZED」に設定しているため、同一のデータを入れてもランダムな情報の生成となるため、検索項目としては使用することができません。

「PersonalID」については、「DETERMINISTIC」にしているため、決定的な値となり、同一のデータであれば同一の値となるため、検索項目として使用することができます。

ただし、Always Encrypted (Database Engine) には以下のように記載されています。

Queries can perform equality comparison on columns encrypted using deterministic encryption, but no other operations (e.g. greater/less than, pattern matching using the LIKE operator, or arithmetical operations).

比較演算子については一致ぐらいしか使用できないようなので、暗号化した項目を検索条件として使用する場合には注意をしておいた方がよさそうですね。

ここまでの設定で暗号化の指定は完了です。

クエリの全体としては、以下のようになります。

CREATE COLUMN MASTER KEY DEFINITION [MyCMK]
WITH
(
	KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
	KEY_PATH = N'CurrentUser/My/FD368B4F2A269846DEACE8739F43B5857151B777'
)
GO
USE [ENCTEST]
CREATE COLUMN ENCRYPTION KEY [MyCEK]
WITH VALUES
(
	COLUMN MASTER KEY DEFINITION = [MyCMK],
	ALGORITHM = 'RSA_OAEP',
	ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0066006400330036003800620034006600320061003200360039003800340036006400650061006300650038003700330039006600340033006200350038003500370031003500310062003700370037008C730B88DEE99FE6B5675DAC93ACBF79150ED9912C4988EA17505DCFBEE84362B01924A7061833D62B15D38EE80EF5CEEF83F6839C30F9EE856D6EF9AD124068AB5C57AEDA09A5D74DF2EAC3398FAEB7C74FA13475D93B8DA467C3461E0261CA50A3EB9A82B90A4634BCB842C5EE1E2C051D65FAB7A72833EE05F667A905CA7DAAD4910DEA3B20E8CC6955CB8091494053198C835356BC1A52515939DB30399ACF8446697B46CC2867F2C09117C9CB1B3511F888B9FBB06CCDB8550176740D29DC6F3451C39322BBE8B80049B8085D14DC910AC21909516CF3D710FB10274059CA78BEAC78966B0B041554B97D8B5233644B7BB9977AEFAFD07EC22E8604693359B22F287B9A3ED727E3CD4B11423D44EA1983C9634EDF8117C3E7BFAA5BBF9126E78160BF3C8E02C7D827C99A0020C4CCABCADE66866E7838906D9AC8557FBF1DFEC81CCD80D214D5F6C439B4921CC44BD2A9D7D90CE417B3646F827CAFEAC30B76B09A96DDC727226C7A2F833EA2F6519CBFBA6DE047F10202F5876636BDA1B89425EDEC2FCBAA192843394436994D53C5B819C603951CB30BCD1B0DB1D2810690EE94FE1244EBEB8C95A459CEF40451C5675A08BD0395A3981C09CFF635E95824C9B5EF26D428133D153FD9E2207DC62A208F74132CB3E882F70D1A1863D341D8EC98E6D1D980F618ED3B9BA01AA60123ADFAA03B173B7A8ADEA1125ED75D
)
GO
CREATE TABLE Customers (
    CustName nvarchar(60)
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = RANDOMIZED,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
    PersonalId varchar(11)
        COLLATE  Japanese_XJIS_100_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
        ENCRYPTION_TYPE = DETERMINISTIC ,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
    Age int NULL
)
GO

■データの挿入

軽く触ってみた限りでは、データの操作は .NET Framework 4.6 以降で実施しないといけないかなという感じです。

現状の CTP 2.0 の SSMS で、データを操作しようとしても限定的な操作になりそうな気がしています。

暗号化したデータの操作には、接続文字列に「Column Encryption Setting=enabled」のオプションを指定する必要があります。

SSMS でこのオプションを指定して接続してみます。

この状態で以下のようなクエリでデータを挿入しようとしても失敗します。

INSERT INTO Customers VALUES(N'Jim Gray','111-22-3333', 63)
EXEC sp_executesql
N'INSERT INTO Customers VALUES(@Name,@PersonalId,@Age)',
N'@Name nvarchar(60), @PersonalId varchar(11), @Age int',
@Name = N'Jim Gray', @PersonalId = '111-22-3333', @Age= 63

メッセージ 206、レベル 16、状態 2、行 1

オペランド型の不整合: nvarchar は nvarchar(4000) encrypted with (encryption_type = ‘RANDOMIZED’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘MyCEK’, column_encryption_key_database_name = ‘ENCTEST’) と互換性がありません

暗号化した場合は、パラメーター化クエリでの操作が必要となるのですが、SSMS からは .NET Framework 4.6 相当のパラメーター化クエリの実行となっていないようでエラーとなってしまいます。

データを挿入する場合、.NET Framework 4.6 がインストールされている環境で以下のような PowerShell 等でデータ操作を行います。

# SQL Server 2016 CTP 2.0 では 4.6 がインストールされていますので、インストールされている環境では使えるかと。

$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 Customers 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()

このような形で

.NET Framework 4.6 がインストールされた環境
「Column Encryption Setting=enabled」で接続
パラメーター化クエリを使用
「CREATE COLUMN MASTER KEY DEFINITION」で指定した場所の CMK にアクセスができる

という条件を満たしていればデータを挿入することができます。

■データの検索

それでは、格納されたデータを確認してみたいと思います。

まずは、「Column Encryption Setting=enabled」を 指定していない SSMS でクエリを実行してみます。

この状態では暗号化は解除されませんので暗号化された状態のデータとなります。

ここで確認したいポイントとしては、「RANDOMIZED」「DETERMINISTIC 」の違いでしょうか。

「CustName 」は「RANDOMIZED」にしていますので暗号化されたデータは異なるバイナリ値として格納がされています。

しかし、「PersonalId」については、「DETERMINISTIC 」にしていますので暗号化されたデータのバイナリ値は同一となっていることが確認できます。

それでは、次に、「Column Encryption Setting=enabled」を 指定した SSMS でクエリを実行してみます。

この場合は暗号化が解除された状態でデータが取得されます。

ただし、挿入時と同様に、暗号化された項目を検索条件に入れるとうまくクエリ実行ができませんが…。

SELECT * FROM Customers WHERE PersonalID = '111-22-3333'
EXEC sp_executesql
N'SELECT * FROM Customers WHERE PersonalID = @PersonalID',
N'@PersonalID varchar(11)',
@PersonalID = '111-22-3333'

ということで、こちらも検索時はプログラム側で取得します。

$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 Customers 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()

そうすると以下のように結果が取得できます。

ここでもパラメーター化クエリとして実行する必要があり、以下のように単純なアドホッククエリで実行しようとするとエラーとなります。

$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()
$command3 = New-Object System.Data.SqlClient.SqlCommand
$command3.Connection = $connection
$command3.CommandType = [System.Data.CommandType]::Text
$command3.CommandText = "SELECT * FROM Customers WHERE PersonalID = N'111-22-3333'"
$reader = $command3.ExecuteReader()

Always Encrypted の特徴として含まれるのは、クエリが実際には暗号化された項目で検索がされていることでしょうか。

検索でエラーにならないクエリでは、プログラムとしては

exec sp_executesql N'SELECT * FROM Customers WHERE PersonalID = @PersonalID',N'@PersonalId varchar(11)',@PersonalId='111-22-3333'

というようなクエリで実行しています。

しかし SQL Server 側に送られたクエリは、

exec sp_executesql N'SELECT * FROM Customers WHERE PersonalID = @PersonalID',N'@PersonalId varchar(11)',@PersonalId=0x01E002BD345F00B196FF76A8D61E1D32F4869DBB4C8698C1CCBBAB36BBC3DFD7EAA91F2FD11B6DF221BD78D5947E86AD71F0F4D77E779A329A6B3D05C7F4C4AF20

というように暗号化された状態で実行されています。

これはプロファイラからも確認できます。

受信したデータについても暗号化された状態で来ているはずですので、Always が示す通り、実際に操作されている内容については暗号化されているが、プログラム等では意識をしないで扱えるというのが、この機能の特徴として挙げられるのかと。

# 検索やアクセス方法については、暗号化した項目を使用していることを意識する必要がありますが。

最新の .NET Framework (4.6 以降) の導入が必須となるので、既存システムへの適用はなかなか難しいかもしれませんが、新規システムで利用できるかは一考といった感じでしょうか。

SE の雑記