Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

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'
share|improve this answer
    
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? –  Craig Efrein Jul 25 '12 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). –  Remus Rusanu Jul 25 '12 at 12:45
    
Ok, thank you for explaining. I understand now what you meant when you said that he was doing it all wrong. –  Craig Efrein Jul 25 '12 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). –  Remus Rusanu Jul 25 '12 at 12:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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