SE の雑記

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

SQL Database でのセル (列) レベル暗号化 (CLE) をサポート

leave a comment

Ignite で SQL Server / SQL Database のセキュリティ機能のアップデートのセッションとして、Overview and Roadmap for Microsoft SQL Server Security が開催されていました。

その中で「CLE for SQL DB」(CLE : Cell Level Encryption) の紹介があり、試してみたらすでに利用可能でした。

image

What’s new in SQL Database V12 にも記載されていますね。

Transact-SQL certificates, keys, and cryptographic functions
Certificates, symmetric keys, and asymmetric keys can now be created in Azure SQL Database V12. Most cryptographic functions are now supported.
For more information, see:
CREATE CERTIFICATE (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)
CREATE ASYMMETRIC KEY (Transact-SQL)
Cryptographic Functions (Transact-SQL)

今まで知らなかったのですが、Security Center for SQL Server Database Engine and Azure SQL Database というページがあり、SQL Server と SQL Database で使用できるセキュリティ系の機能を俯瞰することができます。

image

この中で、「Column,  Data & Key Encryption」というカテゴリがあり、「SQL Server」「SQL Database」ともにサポートとなっています。

英語版のページを見ないと表記はないのですが、各種機能の英語版の BOL を確認すると、

image

というように、SQL Database v12 でもサポートされていることが記載されています。

証明書を使用した暗号化であれば以下のようなクエリで実行することができます。

-- 証明書の作成
CREATE CERTIFICATE ColumnDataEncryption
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
   WITH SUBJECT = 'Column Data Encryption'
GO
-- 文字列を暗号化
DECLARE @EncryptData varbinary(max)
SELECT @EncryptData = EncryptByCert(Cert_ID('ColumnDataEncryption'), N'ABCDE')
SELECT @EncryptData
-- 文字列を複合化
SELECT
CONVERT(nvarchar(max),
DecryptByCert(Cert_ID('ColumnDataEncryption')
, @EncryptData
, N'pGFD4bb925DGvbd2439587y'))

 

これで以下のようにデータの暗号化と複合化を行うことができますので、暗号化された状態で列に格納しておけば、複合しない限り、正常なデータを表示することができなくなります。

image

マスターキーの作成もできますので、以下のようなクエリでの暗号化も可能です。

IF EXISTS (select * from sys.symmetric_keys where name = 'ENCRYPTKEY')
	DROP SYMMETRIC KEY ENCRYPTKEY
IF EXISTS (select * from sys.certificates where name = 'ColumnDataEncryption')
	DROP CERTIFICATE ColumnDataEncryption
IF EXISTS (select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
	DROP MASTER KEY
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
CREATE CERTIFICATE ColumnDataEncryption  WITH SUBJECT = 'Column Data Encryption'
-- 対称キーを使用した暗号化
CREATE SYMMETRIC KEY ENCRYPTKEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE ColumnDataEncryption;
GO
OPEN SYMMETRIC KEY ENCRYPTKEY
DECRYPTION BY CERTIFICATE ColumnDataEncryption
DECLARE @EncryptData varbinary(max)
SELECT @EncryptData = EncryptByKey(Key_GUID('ENCRYPTKEY'), N'ABCDE')
SELECT @EncryptData
SELECT CONVERT(nvarchar(max), DecryptByKey(@EncryptData))
CLOSE SYMMETRIC KEY ENCRYPTKEY
GO
-- 証明書を使用した暗号化
DECLARE @EncryptData varbinary(max)
SELECT @EncryptData = EncryptByCert(Cert_ID('ColumnDataEncryption'), N'ABCDE')
SELECT @EncryptData
SELECT CONVERT(nvarchar(max), DecryptByCert(Cert_ID('ColumnDataEncryption'), @EncryptData))

SQL Database では、ファイルベースでの暗号化キーのバックアップができないかとおもいますので、キー管理の方法については十分に留意する必要がありますが、SQL Server 2005 で実装された EncryptByxx/ DecryptBy 系のセル (または列) レベルの暗号化を SQL Database でも利用することができるようになっています。

Always Encrypted は EncryptByxx / DecryptByxx のような関数を介さなくても暗号化された状態でのデータ格納 / 複合ができるようになりますので、両方が実装されたらどちらを使うかを検討する必要が出るのかもしれないですね。

# Always Encrypted については ADO.NET のライブラリ側が対応している必要が出てくるかと思いますので、特定のバージョン以上の ADO.NET でないと使用できないというような制約があるかと思います。

Share

Written by Masayuki.Ozawa

5月 9th, 2015 at 6:20 pm

Posted in SQL Database

Tagged with

Leave a Reply