Sql-server – Follow up to certificate sharing question

sql serversql-server-2005

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

I created a certificate with a password 

CREATE CERTIFICATE testcert
ENCRYPTION BY PASSWORD = 'test123' 
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

ALTER CERTIFICATE testcert REMOVE PRIVATE KEY

Finally, I attempt to backup the certificate 

BACKUP CERTIFICATE testcert TO FILE = 'd:\mssql\testcert.cer' 
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'S3creT!' ,
FILE = 'd:\mssql\testcert.pvk' , 
ENCRYPTION BY PASSWORD = 'test123' );

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       
ENCRYPTION BY PASSWORD = 'test123',
FROM FILE = 'd:\mssql\testcert.cer'     
WITH PRIVATE KEY ....

Thanks.

Best Answer

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'
Related Question