Sql-server – Sharing certificates encryped by password between DBs and instances

certificateSecuritysignaturesql serversql-server-2005

Question:

I am trying to create a single (self signed) certificate and deploy across many databases and instances.

I create and backup the cert with:

USE MASTER
CREATE CERTIFICATE DavesCert ENCRYPTION BY PASSWORD ='S3creT!' WITH SUBJECT = 'The master cert'

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

I restore with 

USE FOO
GO

CREATE CERTIFICATE ERecruitStatsGatheringCert       
FROM FILE = 'd:\DavesCert.cer'      
WITH PRIVATE KEY (FILE = 'd:\DavesCert.pvk', 
DECRYPTION BY PASSWORD = 'S3creT!')

and get the following error: "Please create a master key in the database or open the master key in the session before performing this operation."

I don't want to create a database master key. I'm happy to decrypt the cert by password as needed.

Background:
SaaS application. Many to Many relationship between DB's and instances.

I need each DB to be able to query it's own stats by executing a stored procedure that wraps up some calls to DMV's to return the stats.

App runs under a low-privileged account. DMV's require VIEW SERVER STATE permission, therefore, I'm implementing signing of the stored procedures using certs.

Basic way of setting this up is to:

  1. Create a cert in the user DB.
  2. Backup/restore that cert to master.
  3. Create login in master, assigned that permissions, etc.
  4. Add Certificate to stored procedure.

I have test code for the above and it works well, however the model does not scale very well for deployment across multiple instances/dbs.

Therefore, I think I want to use the same cert across all DB's/instances.

Happy for other suggestions/approaches.

–Originally posted to Stack Exchange then moved on suggestion from another use

Best Answer

You only specify the password to decrypt the private key file with. You need to add a password to store the certificate with:

CREATE CERTIFICATE ERecruitStatsGatheringCert        
ENCRYPTION BY PASSWORD = 'S3creT!
FROM FILE = 'd:\DavesCert.cer'       
WITH PRIVATE KEY (
    FILE = 'd:\DavesCert.pvk',  
    DECRYPTION BY PASSWORD = 'S3creT!');

But your Background information makes the whole exercise futile. You're doing it wrong. The correct sequence of actions is:

  1. Create a cert in the user DB
  2. Add the signature to the store procedure(s)
  3. Drop the private key of the certificate
  4. Backup/restore that cert to master (public key only!)
  5. Create login in master, assigned that permission, etc.

Notice that not only the private key never leaves the database, is in fact explicitly dropped right after signing the procedure. This is required in order to prevent further use of this certificate to sign other procedures and abuse the login permissions created at step 5. You repeat these steps on each database and use a different certificate on each database. You repeat these steps each time you modify any of the signed procedure and generate a new certificate each time.

As a general rule, any signature/encryption public/private key (RSA) scheme that requires a copy of the private key is broken. This is why is called private and this is what gives value to the signature: the knowledge that there exists only one instance of this private key in the world, therefore anything signed by it is proof that it came from the one and only owner/holder of the unique private key.

I know there are some who shun my recommendation of dropping the private key right after is used to sign the procedure. I stand by my recommendation, but the important thing for your problem is that it is not required to copy the private key into [master] in order to leverage the the code signing permission you desire. You can (and should) create the login derived from the certificate using just the public key.

Regarding automation: As I see it, basically it is a tool (script, app) that takes two inputs: a securable (procedure, function, module) and a permission (VIEW SERVER STATE). Everything else is an automated process (create a one-time-use throw-away cert, sign, export cert, import cert in [master], create login, grant permission). The names implied (cert name, login name) can all be generated.

Proliferation of names is a valid concern. An alternative would be to use only one certificate and associated private key, and the signature tool could add the private key when needed (when signing the procedure), then remove it after the signing. Is really a matter of how you run your shop and how important is the asset you're protecting. But the important thing is that you do not need to import the private key in [master].

Related Question