Can't restore encrypted database on another server

  • --Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

    USE master;

    --DROP CERTIFICATE MyServerCert

    --go

    --DROP MASTER KEY

    --GO

    --CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ABCDEFg1@!';

    --go

    SELECT @@VERSION

    CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TDE Certificate'

    go

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'ABCDEFg1@!';

    BACKUP MASTER KEY TO FILE = 'C:\Projects\Certificate\MasterKey.dat'

    ENCRYPTION BY PASSWORD = 'p@$$w0rd';

    BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\Projects\Certificate\MyServerCert.dat';

    go

    CREATE DATABASE TestDB

    go

    use TestDB

    go

    CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_128

    ENCRYPTION BY SERVER CERTIFICATE MyServerCert

    GO

    ALTER DATABASE TestDB

    SET ENCRYPTION ON

    GO

    BACKUP DATABASE TestDB TO DISK = 'C:\Shared\TestDB.bak'

    go

    USE master

    go

    DROP DATABASE TestDB

    go

    RESTORE DATABASE TestDB FROM DISK = 'C:\Shared\TestDB.bak'

    go

    /* RESTORE DATABASE successfully processed 179 pages... */

    /* trying to restore cert on another server

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)

    */

    USE MASTER

    go

    --DROP CERTIFICATE MyServerCert

    --go

    --DROP MASTER KEY

    --GO

    RESTORE MASTER KEY FROM FILE = 'G:\BackUp\MasterKey.dat'

    DECRYPTION BY PASSWORD = 'p@$$w0rd'

    ENCRYPTION BY PASSWORD = 'ABCDEFg1@!';

    go

    CREATE CERTIFICATE MyServerCert

    FROM FILE = 'G:\BackUp\MyServerCert.dat';

    go

    RESTORE DATABASE TestDB FROM DISK = 'G:\BackUp\TestDB.bak'

    Msg 15507, Level 16, State 1, Line 1

    A key required by this operation appears to be corrupted.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    How can I restore encrypted database?

    Thank you

  • BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\Projects\Certificate\MyServerCert.dat'

    WITH PRIVATE KEY ( FILE = 'C:\Projects\Certificate\MyServerCertKey.dat', ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh');

    CREATE CERTIFICATE MyServerCert FROM FILE = 'G:\BackUp\MyServerCert.dat' WITH PRIVATE KEY

    ( FILE = 'G:\BackUp\MyServerCertKey.dat', DECRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh');

    go

  • Check this one,

    http://sqlblogcasts.com/blogs/sqldbatips/archive/2008/06/24/new-in-sql-2008-transparent-data-encryption-part-ii.aspx

    EnjoY!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply