3

My situation is little weird--I have no access to the source database server, but I do have the database backups of a database that is encrypted with TDE. When I restore it to another server, I am getting an error about the certificate.

I cannot access the source master database. How can I restore the encrypted database on another server?

  • 4
    If you don't have the certificates or a backup of them, then you cannot restore an encrypted database. If you can get access to the source server then you can back them up again, but if it's truly lost or gone and you can't find the certificates then you simply cannot restore or access these databases. If you have a data security team or system administration team then I would ask them if they have the certificates (it's common to store them separate from the backups for security). – Jonathan Fite Oct 22 '18 at 14:00
  • Without certificate I believe it is not possible to restore. – Shanky Oct 22 '18 at 14:00
3

The primary purpose of TDE is that someone can't just take the backup or database files and start up the database on another server. Accordingly, the answer is no, you cannot restore it without restoring the certificate that was used to encrypt it.

0

I belive that you backup the certificate like this:

BACKUP CERTIFICATE mycertificate TO FILE = 'C:\temp\mycert.cer'
GO    
BACKUP SERVICE MASTER KEY TO FILE = 'C:\temp\mymasterkey.cer' ENCRYPTION BY PASSWORD = 'mypassword'  

on the new server:

RESTORE SERVICE MASTER KEY FROM FILE = 'C:\temp\mymasterKey.cer' DECRYPTION BY PASSWORD = 'mypassword' --[FORCE]  

CREATE CERTIFICATE mycert FROM FILE = 'C:\temp\mycert.cer'

but would love to know if I'm wrong. It is a bit difficult to do a proper test.

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.