SE の雑記

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

SQL Server Management Studio 17.0 以降の Always Encrypted の対応

leave a comment

久しぶりに、Always Encrypted を設定をしていて、「そういえば、SSMS が Always Encrypted 対応していたな」ということを思い出して、ちょっと書いておこうかと。

SSMS としては、17.0 以降が、Always Encrypted のパラメーター化クエリに対応しているバージョンとなります。
それ以前のバージョンでは、「column encryption setting=enabled」の接続オプションの利用は可能なのですが、パラメーター化クエリが暗号化されたものに対応していないため、暗号化していない項目を検索条件に使用してのアクセスや暗号化の解除はできたのですが、暗号化されている列を使用した検索や、データの追加 / 更新ができないという制限があります。
詳細な情報については、次の情報を確認していただければと。
Always Encrypted (Database Engine) (Always Encrypted (データベース エンジン))
Parameterization for Always Encrypted ? Using SSMS to Insert into, Update and Filter by Encrypted Columns

SSMS 17.0 より前のバージョンでは、パラメーター化クエリを実行しても、Always Encrypted に対応したクエリとしては実行されていなかったため、暗号化された列を使用したデータの取り扱いができませんでした。
そのため、INSERT 文も実行することができず、テスト用データの作成についてもコード経由にする必要があり、管理者観点の利便性がいまいちということがありました。
そこで、SSMS 17.0 から、SSMS のオプションとして「Always Encrypted パラメーター化を有効にする」というオプションが追加されています。
image
このオプションが有効な状態で、「Column Encryption Setting=enabled」となっている接続を使用することで、Always Encrypted に対応したパラメーター化されたクエリが SSMS でも実行できるようになります。
具体的には以下のようなクエリが実行可能となります。

USE SecurityData
GO
DECLARE @CustName nvarchar(60) = 'Jim Gray'
DECLARE @PersonalId varchar(11) = '111-22-3333'
DECLARE @Age int = 63
INSERT INTO [AlwaysEncrypted] VALUES(@CustName, @PersonalId, @Age)

オプションが有効な状態でクエリを実行すると、上記のクエリが次のクエリ用のうな形式に自動的に変換され実行が行われます。

exec sp_describe_parameter_encryption N'DECLARE @CustName AS NVARCHAR (60) = @p5839770f68b04751b84ab938eb6ff1dc;
DECLARE @PersonalId AS VARCHAR (11) = @p4d33a22461f448088f2f343965eb6d8b;
DECLARE @Age AS INT = @p6f3d63376414417988a667fa9bacc1cc;
INSERT  INTO [AlwaysEncrypted]
VALUES (@CustName, @PersonalId, @Age);
',N'@p5839770f68b04751b84ab938eb6ff1dc nvarchar(60),@p4d33a22461f448088f2f343965eb6d8b varchar(11),@p6f3d63376414417988a667fa9bacc1cc int'

 
Always Encrypted の大きな特徴としては、「実行されるクエリや、メモリ上にキャッシュされるデータに関して、暗号化された列の情報は常に暗号化されている」という特徴があります。

そのため、SSMS から実行した場合も、実際に指定したクエリの文字列をそのまま使用するのではなく、パラメーター部分の実際の値については変換されて実行されることになります。
メモリ上にキャッシュされるクエリに関しても、パラメーターについては、以下のようなキャッシュとなっているようですので、コンパイル時のパラメーターを含めて判別できないようになっているかと。

                              <convert DataType="nvarchar(60) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK', column_encryption_key_database_name = 'SecurityData')" Length="177" Style="0" Implicit="1">
                                <scalarOperator>
                                  <identifier>
                                    <columnReference Column="@CustName" />
                                  </identifier>
                                </scalarOperator>
                              </convert>
Share

Written by Masayuki.Ozawa

7月 2nd, 2017 at 10:47 pm

Posted in SQL Server

Tagged with ,

Leave a Reply