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