Problem restoring database with encrypted columns to different database or server with encrypted columns.

Question

create database test;

CREATE TABLE test(id INT IDENTITY(1,1),NAME VARCHAR(100),sn VARBINARY(256));
INSERT INTO dbo.test
        ( NAME)
SELECT '23cao'
UNION
SELECT 'cxp'
UNION
SELECT 'ferrero11'
UNION
SELECT '12cxp'
UNION
SELECT '2cao'
UNION
SELECT 'ferrero'
UNION
SELECT 'test20120315'

CREATE CERTIFICATE testCert
ENCRYPTION BY PASSWORD = '3caNpGybRZmkbwnt1Y0Pih'
WITH SUBJECT = 'testCert',
START_DATE ='3/17/2012',
EXPIRY_DATE = '3/22/2012'

CREATE SYMMETRIC KEY
testCert_Key AUTHORIZATION EncryptUser WITH
ALGORITHM= AES_128
ENCRYPTION BY CERTIFICATE testCert

back this database, restore to new database.

OPEN SYMMETRIC KEY testCert_Key DECRYPTION BY CERTIFICATE
testCert WITH password='3caNpGybRZmkbwnt1Y0Pih';
SELECT NAME,sn, CAST(DECRYPTBYKEY(sn) AS VARCHAR)
  FROM test

CLOSE SYMMETRIC KEY testCert_Key;

query data, data is fine.

if i want to modified expired for certificate, after done,

OPEN SYMMETRIC KEY testCert_Key DECRYPTION BY CERTIFICATE
testCert WITH password='3caNpGybRZmkbwnt1Y0Pih';
SELECT NAME,sn, CAST(DECRYPTBYKEY(sn) AS VARCHAR)
  FROM test

CLOSE SYMMETRIC KEY testCert_Key;

all CAST(DECRYPTBYKEY(sn) AS VARCHAR) column are null.

question: why? how to do, not lost encryption data to modified certificate ?

Answer 1

CREATE CERTIFICATE testCert
ENCRYPTION BY PASSWORD = 'aaaaaa'
WITH SUBJECT = 'testCert',
START_DATE ='5/13/2011',
EXPIRY_DATE = '3/12/2014'

CREATE SYMMETRIC KEY
testCert_Key AUTHORIZATION EncryptUser WITH
ALGORITHM= AES_128,
KEY_SOURCE=‘test KeySource',
IDENTITY_VALUE = ’ test IdentityValue'
ENCRYPTION BY CERTIFICATE testCert

在这台服务器这个database中，我并没有创建database master key。 当备份此数据库，还原到另外一台server。打开表，用原来的证书打开可以看到数据。如果此时重新在这台server上创建新证书。仅修改expiry—date其余属性和原来一致，创建完成后，无法看到原来表的解密数据了，有没有什么办法可以看到啊 ？

Answer 2

重新创建是不行的。只能备份原来的证书并还原。

---

想不想时已是想，不如不想都不想。

Answer 3

数据库我已经backup，并且还原了证书也是原来的。你的意思是，我需要把restore后的数据库的证书和key delete，然后从原来的database 备份证书和key，然后还原？

Answer 4

我要以哪种方式进行备份？

BACKUP CERTIFICATE sales05 TO FILE = 'c:\storedcerts\sales05cert' WITH PRIVATE KEY ( FILE = 'c:\storedkeys\sales05key' , ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' ); GO or BACKUP CERTIFICATE sales09 TO FILE = 'c:\storedcerts\sales09cert' WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = '9875t6#6rfid7vble7r' , FILE = 'c:\storedkeys\sales09key' , ENCRYPTION BY PASSWORD = '9n34khUbhk$w4ecJH5gh' ); GO

Answer 5

你好，

请问CERTIFICATE 中的PK是否加密了呢？ 是的话就用下面语句：

BACKUP CERTIFICATE sales09 TO FILE = 'c:\storedcerts\sales09cert' 
    WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = '9875t6#6rfid7vble7r' ,
    FILE = 'c:\storedkeys\sales09key' , 
    ENCRYPTION BY PASSWORD = '9n34khUbhk$w4ecJH5gh' );
GO

如果你要加密CERTIFICATE 和PK 就用另外一个语句。

BACKUP CERTIFICATE : http://msdn.microsoft.com/en-us/library/ms178578(v=sql.100).aspx

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 6

我的问题仍然没有解决。

question：如果我想修改证书过期日期而又不丢掉加密数据，在一台机器上如何实现？ backup database and restore to new database，new database又如何实现呢?

Answer 7

This is from books online:

Requirement for Restoring an Encrypted Database

To restore a database that is encrypted, you must have access to the
certificate or asymmetric key that was used to encrypt the database. Without the
certificate or asymmetric key, the database cannot be restored. As a result, the
certificate that is used to encrypt the database encryption key must be retained
as long as the backup is needed.</content>

That means you can't modify certificate on target server. If need change expiry date, should do it on source server then restore to target server again. </content>

Answer 8

Same as http://social.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/6737d668-9c2f-41e3-b6f8-14ef9a532c2a.

Answer 9

if i change exiry date. i need to drop certificiate and key, then recreate again.

i not use KEY_SOURCE and IDENTITY_VALUE, so on source server, i recreate certificate and key, it's may be lost encryption data.

Answer 10

Source server is the server where you backup db.