My Favorite Blogs

Friday, June 15, 2012

Cannot find server certificate with thumbprint Errors

When I'm trying to setup log shipping for a database, I got the below certificate error and I understood that the database got TDE enabled and encrypted with Master key (private key). After struggled for a while and got the steps to fix it.

So before restore that database on the secondary database backup the certificate with master key (private key) and restore it on the secondary  server. Without certificate with Master key on any server and if you try to restore the TDE enabled database you will encounter the below errors.


Message
Executed as user: Domain\ServiceAccount. Cannot find server certificate with thumbprint '0x62A3B87C122C170DFEEBFC6AFCB14B4E5BB5543B'. [SQLSTATE 42000] (Error 33111)  RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

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


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

This will resolve the issue and can restore the database with out any errors.

No comments:

Post a Comment