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.

Question: I am trying to create a single (self signed) certificate and deploy across many DB's 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 sproc 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 sprocs 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 Sproc.

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

share|improve this question

2 Answers 2

I haven't used certificates and keys much in production but the way I understand the concept is that creating a certificate with a private key, means using the database master key to encrypt the private key of the certificate.

I found this article on technet: http://technet.microsoft.com/en-us/library/bb964742.aspx

The database master key is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database.

To see if I could use your certificate on another server, I created your certificate without a private key.

CREATE CERTIFICATE DavesCert WITH SUBJECT = 'The master cert'

Then I backed it up

BACKUP CERTIFICATE DavesCert TO FILE = 'D:\MSSQL\davescert.cer'

I moved the certificate to another server and restored it.

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

View the certificate

SELECT name, certificate_id, pvt_key_encryption_type_desc from sys.certificates


name    certificate_id  pvt_key_encryption_type_desc
davescert   257 NO_PRIVATE_KEY

If you don't use a private key to encrypt your certificate they you don't need the database master key. If you do use a private key, then you will have to create a master key on each database where you use your certificate.

share|improve this 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 Backgroud 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 permissions, etc

Notice that not only the private key never leaves the database, is in fact explictly 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 repete these steps on each database and use a different certificate on each database. You repete these steps each time you modify any of the signed procedure and generate a new certificate each time.

As a general rule, any signature/ecnryption 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, there fore anything signed by it is proof that it came from the one and only owner/holder of the unique private key.

share|improve this answer
1  
BTW, I know there are some who shun my recomemndation of dropping the private key right after is used to sign the procedure. I stand by my recommenedation, 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. –  Remus Rusanu Jul 25 '12 at 10:41
    
Hello Remus, Would you be available to answer a quick question about this subject? Thanks –  Craig Efrein Jul 25 '12 at 11:41
    
@CraigEfrein: Well... why not ask the question, pehaps as a new DBA.SE post? –  Remus Rusanu Jul 25 '12 at 11:56
    
I just added this post: dba.stackexchange.com/questions/21466/… –  Craig Efrein Jul 25 '12 at 12:07
    
@RemusRusanu thank you for your reply and I understand what you are saying. What I'm seeing is a process that is not very scalable/automatable. With over 110 databases spread across 5 instances, that's a lot of certificates floating around that essentially grant permissions to do the same thing. (view server state). Are there any other options - how would you approch this problem? –  David Ames Jul 25 '12 at 20:49

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.