求救数据库加密

[Harrison_2009]

use Test
Create master Key Encryption by password ='1qazXSW@';
Go
Create certificate certtest
with subject ='test certificate'
go
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertTest;
GO
--备份master key和证书
BACKUP MASTER KEY TO FILE = 'd:\zhengshu\masterkey' ENCRYPTION BY PASSWORD = '1qazXSW@'
go
BACKUP CERTIFICATE certtest TO FILE = 'd:\zhengshu\MyServerCert'; 
--备份完成后删除原有的密钥 证书 master Key
Drop SYMMETRIC KEY SymmetricByCert
Drop CERTIFICATE certtest
Drop master Key;

--还原
RESTORE MASTER KEY 
    FROM FILE = 'd:\zhengshu\masterkey1' 
    DECRYPTION BY PASSWORD = '1qazXSW@' 
    ENCRYPTION BY PASSWORD = '1qazXSW@'
	Force
go

CREATE CERTIFICATE certtest
    FROM FILE = 'd:\zhengshu\MyServerCert1' 
GO 
--重建密钥
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertTest;
GO

在重建密钥时会报错
消息 15556，级别 16，状态 1，第 1 行
无法使用指定的 证书 进行解密或加密，因为它没有私钥或者为私钥提供的密码不正确。

[Harrison_2009]

不好意思，备份时我masterkey和masterkey1 MyServerCert 、MyServerCert1都写了的。后成还原时忘改回来了。还原代码中的master key 和证书文件是存在的。

[xdashewan]

貌似是说你的凭证没有私钥，你把凭证加私钥看看

[Harrison_2009]

我给凭证加了私钥，现在能通过还原master key、证书以及重建密钥，但此密钥却无法解密备份之前的数据

--备份master key、证书
BACKUP MASTER KEY TO FILE = 'd:\zhengshu\masterkey.cer' ENCRYPTION BY PASSWORD = '1qazXSW@'
go
BACKUP CERTIFICATE CertTest TO FILE = 'd:\zhengshu\MyServerCert.cer' --cert的保存地址 
WITH PRIVATE KEY (  
	DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y',
	FILE = 'd:\zhengshu\MyServerKey.pvk' , 
	ENCRYPTION BY PASSWORD = '1qazXSW@' );
--还原master key 、证书
open master key DECRYPTION BY PASSWORD = 'zxasqw!2'
CREATE CERTIFICATE CertTest
    FROM FILE = 'd:\zhengshu\MyServerCert.cer' 
	WITH PRIVATE KEY (FILE = 'd:\zhengshu\MyServerKey.pvk'
	, DECRYPTION BY PASSWORD = '1qazXSW@'
	);
GO 
--close master key
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertTest;
GO
--open master key DECRYPTION BY PASSWORD = 'zxasqw!2'
OPEN SYMMETRIC KEY SymmetricByCert DECRYPTION BY CERTIFICATE CertTest
select convert(nvarchar(max), DecryptByKey(CardNumber_encrypt)) from CreditCard_Encrypt;
Close SYMMETRIC KEY SymmetricByCert

[xdashewan]

你备份的路径是BACKUP CERTIFICATE certtest TO FILE = 'd:\zhengshu\MyServerCert'; 而你还原的路径是FROM FILE = 'd:\zhengshu\MyServerCert1' 你确定这样正确吗？