Thursday, October 24, 2013

Cannot find server certificate with thumbprint error ... TDE error

Situation 

  1. Primary SQL Server's database has TDE (Transparent Data Encryption) enabled and encrypted with Master key (private key). 
  2. The database backup has been made and backup file was copied to a secondary SQL Server. 
  3. When restoring the database on a secondary SQL Server, "Cannot find server certificate with thumbprint error" happens.
Solution:

1. Backup the certificate with master key on the Primary Server
  • BACKUP CERTIFICATE  [EncryptionCertificate]
    TO FILE = 'Certificate File path'
    WITH PRIVATE KEY (FILE = 'Master Key File path.dat', ENCRYPTION BY PASSWORD ='password')

2. Restore the certificate with master key password on the Secondary Server
  • CREATE CERTIFICATE [EncryptionCertificate]
    FROM FILE='Certificate File path'
    WITH PRIVATE KEY ( FILE =  'Master Key File path.dat' , DECRYPTION BY PASSWORD ='password')

No comments: