Follow up to the post Sharing certificates encryped by password between DBs and instances

I created a certificate with a password

WITH SUBJECT = 'Certificate for stored procedures using dynamic SQL',
START_DATE = '2010-01-01',
EXPIRY_DATE = '2100-01-01'

I then removed the private key


Finally, I attempt to backup the certificate

BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer' 
FILE = 'd:\mssql\testcert.pvk' , 

And I get this error:

Msg 15246, Level 16, State 1, Line 1 Cannot dump the private key of certificate 'testcert' because the private key cannot be found.

Which seems normal since I dropped the private key.

1st Question: Using the method explained in the previous post, does this mean that to backup the certificate I would use this statement?

BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer' 

2nd Question: What would you do to then to restore the certificate to another server and database?

Example :

CREATE CERTIFICATE testcert2       
FROM FILE = 'd:\mssql\testcert.cer'     


1 Answer 1


When you backup and restore a certificate for the purpose of moving the certificate you shouldn't have to include any clause related to the private key. I explained in the linked post the reasoning why the value of private key derives primarily from it's uniqueness and any operation that duplicates a private key dillutes its value:

  • it can no longer be used as proof of identiy (ie. signature) since multiple copies exists throughout the world and non-repudiation is busted
  • it can no loger be used as a secure means to achieve privacy (ie. encrypt with corresponding public key) because since multiple copies exists, a copy may had been compromised

Just to drive the point home, a certificate is a 'an electronic document which uses a digital signature to bind a public key with an identity' and the correct terminology is public key certificate. In other words a certificate does not have a private key. I myself am carefull to say 'certificate and the associated private key'.

The few operations when is acceptable to backup and restore a certificate and the associated private key are key escrow and backup for recovery purpose (not for move).

Now after this long dissertation, the answers:

  1. yes, use BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer'
  2. Use CREATE CERTIFICATE testcert2 FROM FILE = 'd:\mssql\testcert.cer'
  • Thank you Remus +1. If you were going to sign a stored procedure in the database where you restored the certificate, would you then add back a private key to the certificate? Jul 25, 2012 at 12:39
  • You cannot 'add back' a private key. The private key and public key form a cryptographic match. Once the private key is dropped one can never use the same 'certificate' (actually the same key-pair) to sign again a procedure. This is exaclty why I recommend dropping the private key after the procedure is signed: nobody can come later and sign a different procedure and use this as a measn to escalate priviledges. When a new procedure needs to be signed, generate a new certificate (a ney key pair). Jul 25, 2012 at 12:45
  • Ok, thank you for explaining. I understand now what you meant when you said that he was doing it all wrong. Jul 25, 2012 at 12:48
  • In the linked post question even if he chooses not to drop the private key after signing, there still is absoilutely no reason to share the private key between instances. Sharing between DBs may be argued for due do proliferation of certificate derived logins in [master], but once you do that you accept a compromise in the strenght of your security story (compromise of the private key in DB1 can lead to low priviledges users in DB1 to gain access to many more DBs and other attack vectors may be exposed). Jul 25, 2012 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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