Can't restore encrypted database on another server

• Dmitriy Burtsev

  Ten Centuries

  Points: 1188

  More actions

  March 26, 2010 at 9:35 am

  #222348

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

• Dmitriy Burtsev

  Ten Centuries

  Points: 1188

  More actions

  March 26, 2010 at 1:12 pm

  #1140695

  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

• GTR

  SSCrazy Eights

  Points: 8007

  More actions

  March 26, 2010 at 4:20 pm

  #1140818

  Check this one,

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

  EnjoY!