SQL SERVER 2005 使用证书恢复后，解密功能失效

Question

我用如下的SQL 语句创建MASTER KEY ，CERTIFICATE ，然后用CERTIFICATE 做加密解密的测试，一切正常。

-- Use the ReportServerTempDB database
USE ReportServerTempDB ; -- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION  BY PASSWORD = 'p@ssw0rd';

-- Create a Test Certificate, encrypted by the DMK
CREATE CERTIFICATE TestCertificate1
WITH SUBJECT = 'Test Certificate',
START_DATE = '1/1/2006',
EXPIRY_DATE = '10/31/2011';
 
declare @b as varchar(200)
declare @d as varchar(200)
set @b='123';
set   @d=EncryptByCert(Cert_ID('TestCertificate1'), @b)
select  CONVERT(VARCHAR, DecryptByCert(Cert_ID('TestCertificate1'), @d) )

但是我BACKUP CERTIFICATE ，并手工删除了 CERTIFICATE TestCertificate1 ，然后再重新创建CERTIFICATE ，结果却无法解密了，

BACKUP CERTIFICATE TestCertificate1 TO FILE = 'C:\dell\cc2';
GO
CREATE CERTIFICATE TestCertificate1 FROM FILE = 'C:\dell\cc2'

declare @b as varchar(200)
declare @d as varchar(200)
set @b='123';
set   @d=EncryptByCert(Cert_ID('TestCertificate1'), @b)
select  CONVERT(VARCHAR, DecryptByCert(Cert_ID('TestCertificate1'), @d) )

上面的语句老是返回NULL而不是期望的123，这是怎么回事啊？

 

Answer 1

没有private key的证书是无法加密的。

所以语句执行到

declare @b as varchar(200)
declare @d as varchar(200)
set @b='123';
set   @d=EncryptByCert(Cert_ID('TestCertificate1'), @b)
这时候@d已经是个null，所以接下来揭秘得到的也是null,

需要在备份是将priate key也显示的备份先来...然后还原时加上private key

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'
    [ WITH PRIVATE KEY 
 

 

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 2

谢谢你的回复， 你用的方法加上WITH PRIVATE KEY 是可以解密成功。 不过为什么我直接用新创建的证书来加密跟解密都是可以的呢，但是用我的方式来备份证书，再用恢复的证书就不能解密了呢？ 另外你说的“set @d=EncryptByCert(Cert_ID('TestCertificate1'), @b) 这时候@d已经是个null，所以接下来揭秘得到的也是null,” 这个@d，无论是用新建的证书加密，还是用恢复的证书加密，都不会是NULL的

Answer 3

create certificate创建的证书是由private key，private key用于加密。

 

backup certificate xx to file='xx'这个语法只备份证书的public key。

这时候还原出来的证书也只有public key,无法加密

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 4

create certificate创建的证书是由private key，private key用于加密。

 

backup certificate xx to file='xx'这个语法只备份证书的public key。

这时候还原出来的证书也只有public key,无法加密

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

非常感谢，

不过我的疑问是，为什么我用下面的方法来创建证书的时候，加密解密都是正常的呢，我也没有设置它的private key跟public key啊

-- Create a Test Certificate, encrypted by the DMK
CREATE CERTIFICATE TestCertificate1
WITH SUBJECT = 'Test Certificate',
START_DATE = '1/1/2006',
EXPIRY_DATE = '10/31/2011';
 

Answer 5

create certificate创建的证书是由private key，private key用于加密。

 

backup certificate xx to file='xx'这个语法只备份证书的public key。

这时候还原出来的证书也只有public key,无法加密

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

非常感谢，

不过我的疑问是，为什么我用下面的方法来创建证书的时候，加密解密都是正常的呢，我也没有设置它的private key跟public key啊

-- Create a Test Certificate, encrypted by the DMK
CREATE CERTIFICATE TestCertificate1
WITH SUBJECT = 'Test Certificate',
START_DATE = '1/1/2006',
EXPIRY_DATE = '10/31/2011';
 

create certficate创建出的证书包含private key和public key 相关的信息可以通过sys.certificates查到:pvt_key_encryption_type , pvt_key_encryption_type_desc

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 6

create certificate创建的证书是由private key，private key用于加密。

 

backup certificate xx to file='xx'这个语法只备份证书的public key。

这时候还原出来的证书也只有public key,无法加密

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

非常感谢，

不过我的疑问是，为什么我用下面的方法来创建证书的时候，加密解密都是正常的呢，我也没有设置它的private key跟public key啊

-- Create a Test Certificate, encrypted by the DMK
CREATE CERTIFICATE TestCertificate1
WITH SUBJECT = 'Test Certificate',
START_DATE = '1/1/2006',
EXPIRY_DATE = '10/31/2011';
 

create certficate创建出的证书包含private key和public key 相关的信息可以通过sys.certificates查到:pvt_key_encryption_type , pvt_key_encryption_type_desc

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Thank you very much!