利用SQL Server的對稱金鑰資料加/解密時，如果客戶需要3個月變更一次密碼的話，是否會影響到原本已被加密的資料呢?
答案是「不會」有影響! 那要如何變更密碼呢?
前言
今天再跟同事介紹利用SQL Server的對稱金鑰資料加/解密時，同事問說，如果客戶需要3個月變更一次密碼的話，是否會影響到原本已被加密的資料呢?
答案是「不會」有影響!
那要如何變更密碼呢?
實作
一、如果對稱金鑰是ENCRYPTION BY PASSWORD的話，那變更密碼的方式如下(Step 2~7)，
1.建立對稱金鑰DB_KEY1，及測試資料，如下，
1.1.建立對稱金鑰DB_KEY1
WITH ALGORITHM = AES_128, KEY_SOURCE = 'RM Key source', IDENTITY_VALUE = 'RM Key identity' ENCRYPTION BY PASSWORD = 'rainmaker'; GO
1.2.將加密資料儲存起來
, EN_VAL varbinary(8000) , AFTER_VAL nvarchar(128)); GO OPEN SYMMETRIC KEY DB_KEY1 DECRYPTION BY PASSWORD = 'rainmaker'; DECLARE @OrgVal nvarchar(128), @EnVal VARBINARY(8000), @DeVal NVARCHAR(128); SET @OrgVal = N'我是亂馬客!'; SET @EnVal = ENCRYPTBYKEY(KEY_GUID('DB_KEY1'), @OrgVal); SET @DeVal = CONVERT(NVARCHAR(128), DECRYPTBYKEY(@EnVal)); INSERT INTO t1 VALUES(@OrgVal, @EnVal, @DeVal); SELECT * FROM dbo.t1 AS T; CLOSE SYMMETRIC KEY DB_KEY1;
2.用舊的密碼，OPEN SYMMETRIC KEY，如下，
3.加入新的密碼到對稱金鑰之中，如下，
ADD ENCRYPTION BY PASSWORD = 'rmok';
4.CLOSE SYMMETRIC KEY，如下，
5.用新的密碼，OPEN SYMMETRIC KEY，如下，
6.將舊的密碼刪除，如下，
DROP ENCRYPTION BY PASSWORD = 'rainmaker';
7.CLOSE SYMMETRIC KEY，如下，
以上就將 對稱金鑰 DB_KEY1 ENCRYPTION BY PASSWORD的密碼由「rainmaker」換成了「rmok」。
針對ENCRYPTION BY PASSWORD，請記得「開舊密，加新密，開新密，刪舊密」。
所以這時，要用舊的密碼來開啟對稱金鑰，會發生以下的錯誤。
Msg 15313, Level 16, State 1, Line 1
The key is not encrypted using the specified decryptor.
只能使用新的密碼來開啟對稱金鑰，如下，
同時，之前被加密的資料，也可以正常被解密出來，如下，
FROM dbo.t1 AS T;
二、如果您的對稱金鑰是ENCRYPTION BY CERTIFICATE的話，那變更密碼的方式就只要變成CERTIFICATE的密碼就可以了，如下(Step 2)，
1.建立憑證DB_CER1、對稱金鑰DB_KEY2，及測試資料，如下，
1.1.建立憑證 DB_CER1
ENCRYPTION BY PASSWORD = 'rainmaker' WITH SUBJECT = 'RM Encryption', START_DATE='2006/01/01', EXPIRY_DATE = '2999/12/31'; GO
1.2.建立對稱金鑰 DB_KEY2，ENCRYPTION BY CERTIFICATE
WITH ALGORITHM = AES_128, KEY_SOURCE = 'RM Key source2', IDENTITY_VALUE = 'RM Key identity2' ENCRYPTION BY CERTIFICATE DB_CER1; GO
1.2.將加密資料儲存起來
, EN_VAL varbinary(8000) , AFTER_VAL nvarchar(128)); GO OPEN SYMMETRIC KEY DB_KEY2 DECRYPTION BY CERTIFICATE DB_CER1 WITH PASSWORD = 'rainmaker'; DECLARE @OrgVal nvarchar(128), @EnVal VARBINARY(8000), @DeVal NVARCHAR(128); SET @OrgVal = N'我是亂馬客!'; SET @EnVal = ENCRYPTBYKEY(KEY_GUID('DB_KEY2'), @OrgVal); SET @DeVal = CONVERT(NVARCHAR(128), DECRYPTBYKEY(@EnVal)); INSERT INTO t2 VALUES(@OrgVal, @EnVal, @DeVal); SELECT * FROM dbo.t2 AS T; CLOSE SYMMETRIC KEY DB_KEY2;
2.修改憑證的密碼，如下，
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'rainmaker', ENCRYPTION BY PASSWORD = 'rmok'); GO
以上因為對稱金鑰是ENCRYPTION BY CERTIFICATE，而密碼在CERTIFICATE上，所以只要改CERTIFICATE DB_CER1 的密碼由「rainmaker」換成了「rmok」，就可以了!
所以這時，要用舊的密碼來開啟對稱金鑰，會發生以下的錯誤。
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
只能使用新的密碼來開啟對稱金鑰，如下，
DECRYPTION BY CERTIFICATE DB_CER1 WITH PASSWORD = 'rmok';
同時，之前被加密的資料，也可以正常被解密出來，如下，
FROM dbo.t2 AS T;
結論
從上面的方式來看，對稱金鑰使用ENCRYPTION BY CERTIFICATE之後如果要更改密碼時，似乎是來的比較方便。
在會議中還有人提到，對稱金鑰使用ENCRYPTION BY CERTIFICATE時，如果CERTIFICATE過期的話，資料可以被解開嗎?
答案是「可以」正常被解開的哦!
請參考MSDN CREATE CERTIFICATE (Transact-SQL)的說明:用於加密及簽署的內建函數不會檢查憑證的到期日期。 這些函數的使用者必須決定何時檢查憑證到期日期。
另外，在「如何在不同的資料庫中建立相同的SYMMETRIC KEY」這篇中，建議在建立對稱金鑰時，一併設定KEY_SOURCE及IDENTITY_VALUE的值，而IDENTITY_VALUE的值只能給一個SYMMETRIC KEY使用。
如果要建立另一個Key的話，IDENTITY_VALUE的值不能跟已建立SYMMETRIC KEY的IDENTITY_VALUE所設定的相同! 不然會有如下的錯誤哦!
Msg 15282, Level 16, State 1, Line 1
A key with name 'DB_KEY3' or user defined unique identifier already exists or you do not have permissions to create it.
參考資料
CREATE SYMMETRIC KEY (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
