[SQL]如何在不同的資料庫中建立相同的SYMMETRIC KEY

[SQL]如何在不同的資料庫中建立相同的SYMMETRIC KEY

之前有使用symmetric key為資料加密,可參考「為資料做加解密處理」。

那如果今天將該有加密過的資料轉到另一個Database之中,然後在該資料庫中建立symmetric key,您會發現無法順利將資料解開。如下,

image

 

如果您是將整個DB備份起來再Restore回來的話,是沒問題的哦。

image

 

那為何會有這樣的問題呢?

主要是因為當初建立symmetric key時,沒有指定KEY_SOURCE與IDENTITY_VALUE。

MSDN CREATE SYMMETRIC KEY (Transact-SQL) 解譯如下,

KEY_SOURCE : 指定要從其衍生金鑰的複雜密碼。
就是可以讓我們建立出一模一樣的symmetric key。

IDENTITY_VALUE : 指定要從中產生 GUID 來標記利用暫時金鑰加密的資料之識別片語。
就是透過KEY_GUID建立UNIQUEIDENTIFIER時,可以產生出一樣的UNIQUEIDENTIFIER。

 

比較詳細資訊,可參考以下2篇文章

SQL Server 2005: using symmetric keys to encrypt data

SQL Server 2005: How to regenerate the same symmetric key in two different databases

 

那接下來我們就來實際Demo一下,

先在一個DB中建立2個SYMMETRIC KEY,一個沒設定KEY_SOURCE與IDENTITY_VALUE,一個有設定,如下,

--=============================================================
-- DB_KEY1 沒有加上 KEY_SOURCE 及 IDENTITY_VALUE 
--=============================================================
CREATE SYMMETRIC KEY DB_KEY1 WITH ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY PASSWORD = 'rainmaker'
GO
 
--=============================================================
-- DB_KEY2 有加上 KEY_SOURCE 及 IDENTITY_VALUE 
--=============================================================
CREATE SYMMETRIC KEY DB_KEY2 WITH ALGORITHM = TRIPLE_DES 
	,KEY_SOURCE = 'RMKeySource'
	,IDENTITY_VALUE = 'RMIdValue'
	ENCRYPTION BY PASSWORD = 'rainmaker'
GO

 

然後透過sys.symmetric_keys來比較一下2個DB中的SYMMETRIC KEY是否相同

image

可以發現,有設定KEY_SOURCE與IDENTITY_VALUE的DB_KEY2,在2個DB中,key_guid欄位的值都相同。

所以建立SYMMETRIC KEY時,可視狀況來加入KEY_SOURCE與IDENTITY_VALUE的設定哦!

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活

請大家繼續支持 ^_^