关于sql server 2005加密的密钥备份

huangya0 2009-07-10 04:30:53

问题背景:
DB有master key, 指定了密码, 再建证书,没指定密码, 再在所建证书基础上建Symmetric Key,也不指定密码, (没指定密码是会用master key的). 这些就是DB加密的方法.
现在有要求:
对DB进行备份,带到其它除了源电脑的电脑上还原使用, 在还原DB时,保证数据还能正确解密, 用的方法无论是重建master key还是备份master key和证书或symmetric key到文件再还原,都不能明文指定密码(客户怕密码外泄).

我用了以下两种办法:
1. 在备份还原操作后,执下以下代码,一般都是可以正确解密数据, 但是明文指定了密码,不符合要求.
--OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd'
--ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'p@ssw0rd'

2. 用备份master key等再还原的办法, 但这种办法,在备份还原到不同的电脑后, 数据不能正确的解密了, 当然也是明文指定了密码, 代码如下:

/*-----第一步-------建立------------------
drop SYMMETRIC KEY TestSymmetricKey
drop CERTIFICATE TestCertificate
drop master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'Adventureworks Test Certificate',
EXPIRY_DATE = '10/31/2010';

CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate;
*/--------------------------------------------

/*-----第2步-------测试加密和解密---------------------------
OPEN SYMMETRIC KEY TestSymmetricKey
DECRYPTION BY CERTIFICATE TestCertificate;

--加密
--open SYMMETRIC KEY ODMSSymmetricKey DECRYPTION BY CERTIFICATE ODMSCertificate;
declare @tempString nvarchar(max)
declare @AA nvarchar(max)
set @AA=N'te三通'
set @tempString='未加密源: ' + @AA
print @tempString
declare @BB varbinary(max)
set @BB=EncryptByKey(Key_GUID('TestSymmetricKey'), @AA)
set @tempString=N'加密后: '
print @tempString
print @BB
--解密
set @AA=CONVERT(nvarchar(max), DecryptByKey(@BB) )
set @tempString='解密后: ' + @AA
print @tempString

delete FROM Customer WHERE CustomerID = 6

INSERT INTO Customer
VALUES (6, 'John Doe', 'Fairbanks',
EncryptByKey(Key_GUID(
'TestSymmetricKey'), 'Amex'),
EncryptByKey(Key_GUID(
'TestSymmetricKey'),
'1234-5678-9009-8765'),
EncryptByKey(Key_GUID(
'TestSymmetricKey'),
'Window shopper. Spends $5 at most.'))

SELECT CustomerID, [Name], City,
CONVERT(VARCHAR, DecryptByKey(CreditCardType))
AS CardType,
CONVERT(VARCHAR, DecryptByKey(CreditCardNumber))
AS CardNumber,
CONVERT(VARCHAR, DecryptByKey(Notes))
AS Notes
FROM Customer WHERE CustomerID = 6

CLOSE SYMMETRIC KEY TestSymmetricKey
*/---------------------------------------------------------

/*--------第3步----------备份master key和证书等------------------------
BACKUP MASTER KEY TO FILE = 'E:\Work\ODMS\Temp\20090706\CertTest_MasterKey.pvk'
ENCRYPTION BY PASSWORD = 'p@ssw0rd'

BACKUP CERTIFICATE TestCertificate TO FILE = 'E:\Work\ODMS\Temp\20090706\CertTest.cer'
WITH PRIVATE KEY ( FILE = 'E:\Work\ODMS\Temp\20090706\CertTest.pvk' ,
ENCRYPTION BY PASSWORD = '123456' );
*/---------------------------------------------------------

/*--------第4步------------------------执行删除操作-------------
drop SYMMETRIC KEY TestSymmetricKey

drop CERTIFICATE TestCertificate

drop master key
*/-------------------------------------------------------------------------

/*------第5步-------从备份中恢复------------------------------------------------------------
RESTORE MASTER KEY FROM FILE = 'E:\Work\ODMS\Temp\20090706\CertTest_MasterKey.pvk'
DECRYPTION BY PASSWORD = 'p@ssw0rd'
ENCRYPTION BY PASSWORD = 'p@ssw0rd'

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd'

CREATE CERTIFICATE TestCertificate
FROM FILE = 'E:\Work\ODMS\Temp\20090706\CertTest.cer'
WITH PRIVATE KEY (FILE = 'E:\Work\ODMS\Temp\20090706\CertTest.pvk',
DECRYPTION BY PASSWORD = '123456');

CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE TestCertificate;
*/-------------------------------------------------------------------------

/*-----再执行第2步-----*/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
有大虾帮我看一下这个问题的解决办法吗？

问过微软的人,他们说,是可以通过备份master key等,再还原,还是可以成功解密数据的,但我这里又不可以,也没搞清楚,那里有问题.

...全文