5

This is my first time working with TDE so hopefully I haven't done anything horribly wrong.

I'm trying to move my TDE database from one server to another. I have created a master key on the master database of the new server. I then restored my certificate from backup using the following command:

CREATE CERTIFICATE MyServerCert
FROM FILE = 'CertBackupFile'

When I created it originally I did not have a private encryption key so I just encrypted it with a password.

Now when I try to restore the backup of my TDE I'm getting the following error

Msg 15507, Level 16, State 1, Line 2
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

I've tried looking at the create statement but don't see where I can put in my original password. Also when I look at sys.certificates on my original server the pvt_key_encryption_type_desc = ENCRYPTED_BY_MASTER_KEY but on the new one it says NO_PRIVATE_KEY. So I assume I'm doing something wrong with the create.

Can anyone point me in the right direction to fix this?

4

You will need to create a master key on the new server - if it does not exit already.

USE MASTER;
GO
if not exists (select 1 from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password';

Then you have to restore your certificate:

CREATE CERTIFICATE serverCert  FROM FILE = 'C:\cert_Backups\servercert.cer'     
WITH PRIVATE KEY (FILE = 'C:\cert_Backups\servercert.key'
    ,DECRYPTION BY PASSWORD = 'strong_Passw0rD')
GO 

But for this to work you will have to make sure that you made a backup of the original certificate with the private key.

Backup certificate ServerCert 
 to file = 'C:\cert_Backups\servercert.cer'
 with private key (file = 'C:\cert_Backups\servercert.key',
 encryption By Password = 'strong_Passw0rD')
GO

If you only have a cer file and not a key you will need to backup your certificate again

  • 1
    I didn't have to mess with the master key but it turned out the trick was backing up the private key along with the cert & then using both in the restore. Thanks! – Kenneth Fisher May 20 '15 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.