SQL Server で証明書を利用したデータの暗号化（EncryptByKey：対称キー）

SQL Server 2008

SQL Server 2005 以降の EncryptByKey 関数では、証明書（と対称キー）を利用して暗号化することもできます。これは次のように利用します。

-- ログイン アカウント作成（user1）
USE master
CREATE LOGIN user1 WITH PASSWORD = 'P@ssword'

-- データベースの作成
CREATE DATABASE CerSymKeyTest
go
-- データベース ユーザー作成（user1）
USE CerSymKeyTest
CREATE USER user1 FOR LOGIN user1
go

-- テーブルの作成
CREATE TABLE テスト1
(  id int IDENTITY(1, 1)
  ,ClearText nvarchar(100) 
  ,EncryptData varbinary(max) ) 
go
-- user1 へ INSERT と SELECT 権限を GRANT
GRANT INSERT, SELECT ON テスト1 TO user1

-- 証明書の作成（証明書の所有者を user1 へ設定、パスワードの設定）
CREATE CERTIFICATE Cert1
 AUTHORIZATION user1
 ENCRYPTION BY PASSWORD = '<StrongPassword>'
 WITH SUBJECT = 'テスト1_暗号化用'
     ,START_DATE = '2009/10/20'
go

-- 対称キーの作成 (証明書を利用、アルゴリズムは AES_128)
CREATE SYMMETRIC KEY Sym_Key1
 AUTHORIZATION user1
 WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE Cert1
go

-- user1 でのログインをシミュレート
EXECUTE AS LOGIN = 'user1'

-- 対称キーのオープン
OPEN SYMMETRIC KEY Sym_Key1
 DECRYPTION BY CERTIFICATE Cert1 WITH PASSWORD = '<StrongPassword>'

-- 対称キーの GUID を Key_GUID 関数で取得して、
-- EncryptByKey でデータを暗号化して INSERT
DECLARE @kGuid UNIQUEIDENTIFIER
SET @kGuid = Key_GUID('Sym_Key1')

INSERT INTO テスト1
 VALUES ( '平文', EncryptByKey(@kGuid, N'暗号化される？') )

-- 対称キーの CLOSE
CLOSE SYMMETRIC KEY Sym_Key1

-- 普通に SELECT すると暗号化されていることを確認できる
SELECT * FROM テスト1



-- 復号化
-- 対称キーの OPEN
OPEN SYMMETRIC KEY Sym_Key1
 DECRYPTION BY CERTIFICATE Cert1 WITH PASSWORD = '<StrongPassword>'

-- DecryptByKey で復号化
SELECT ID, ClearText
	  ,CONVERT( nvarchar, DecryptByKey(EncryptData) )
 FROM テスト1

-- 対称キーの CLOSE
CLOSE SYMMETRIC KEY Sym_Key1

-- シミュレートの終了
REVERT

他のユーザー user2 から対称キーを利用してみると、

USE master
CREATE LOGIN user2 WITH PASSWORD = 'P@ssword'
USE CerSymKeyTest
CREATE USER user2 FOR LOGIN user2
go

-- user2 でのログインをシミュレート
EXECUTE AS LOGIN = 'user2'

-- 対称キーの OPEN でエラーが発生する
OPEN SYMMETRIC KEY Sym_Key1
 DECRYPTION BY CERTIFICATE Cert1 WITH PASSWORD = '<StrongPassword>'
/*
メッセージ 15151、レベル 16、状態 1、行 4
対称キー 'Sym_Key1' を 検索 できません。存在しないか、権限がありません。
*/
-- シミュレートの終了
REVERT

対称キーのアルゴリズムで指定できるのは、次のとおりです。
DES、TRIPLE_DES、TRIPLE_DES_3KEY、RC2、RC4、RC4_128、DESX、AES_128、AES_192、AES_256


対称キーの OPEN で証明書のパスワードを間違えた場合には、次のエラーが発生します。

メッセージ 15466、レベル 16、状態 1、行 1
暗号化解除中にエラーが発生しました。


EncryptByKey の詳細（ヘルプ）はこちら
http://msdn.microsoft.com/ja-jp/library/ms174361.aspx

DecryptByKey の詳細（ヘルプ）はこちら
http://technet.microsoft.com/ja-jp/library/ms181860.aspx

CREATE CERTIFICATE の詳細（ヘルプ）はこちら
http://technet.microsoft.com/ja-jp/library/ms187798.aspx

CREATE SYMMETRIC KEY の詳細（ヘルプ）はこちら
http://technet.microsoft.com/ja-jp/library/ms188357.aspx

OPEN SYMMETRIC KEY の詳細（ヘルプ）はこちら
http://msdn.microsoft.com/ja-jp/library/ms190499.aspx

暗号化アルゴリズムの選択の原則についてはこちら
http://msdn.microsoft.com/ja-jp/library/ms345262.aspx

SQL Server の暗号化階層はこちら
http://msdn.microsoft.com/ja-jp/library/ms189586.aspx