none
Problem restoring database with encrypted columns to different database or server with encrypted columns. RRS feed

  • 问题

  • 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 ?

    2012年3月17日 6:11

答案

  • 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>


    2012年3月17日 17:16

全部回复

  • 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其余属性和原来一致,创建完成后,无法看到原来表的解密数据了,有没有什么办法可以看到啊 ?

    2012年3月12日 12:25
  • 重新创建是不行的。只能备份原来的证书并还原。


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

    2012年3月12日 13:05
    版主
  • 数据库我已经backup,并且还原了证书也是原来的。你的意思是,我需要把restore后的数据库的证书和key delete,然后从原来的database 备份证书和key,然后还原?
    2012年3月12日 13:40
  • 我要以哪种方式进行备份?

    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

    2012年3月12日 13:54
  • 你好,

    请问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.

    2012年3月14日 2:54
    版主
  • 我的问题仍然没有解决。

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

    2012年3月17日 6:13
  • 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>


    2012年3月17日 17:16
  • 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.

    2012年3月18日 16:05
  • Source server is the server where you backup db.
    2012年3月18日 22:01