Sharing certificates encryped by password between DBs and instances

Question

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

Answer 1

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].

Answer 2

You should create a master key per server to avoid the error. Here's what I did to fix the same error on my secondary servers.

drop certificate [MyCertwithPK]
drop master key 

create master key encryption by password = '4_Str0ng!MasterKey'

--verify.
open master key decryption by password = '4_Str0ng!MasterKey';
close master key;

--next.
create certifate [MyCertwithPK] from file = '\\SQL1\Backups\MyCertwithPK.cer' 
with private key (file = '\\SQL1\Backups\MyCertwithPK.key' , decryption by 
password = '4_Str0ng!CertKey') ;

--Note: My cert doesn't encrypt by password. Uses encryption hierarchy & private key instead.     

--To clarify, these were my initial create/backup cmds on my **primary** svr.
create master key encryption by password = '4_Str0ng!MasterKey';
create certificate [MyCertwithPK] with subject = 'Certificate for SQL backups with Private Key' ;
backup certificate [MyCertwithPK] to file = '\\SQL1\Backups\MyCertwithPK.cer' with private key (file= '\\SQL1\Backups\MyCertwithPK.key', encryption by password = '4_Str0ng!CertKey') ;

Check this across the servers you wish to match databases with a master key that's encrypted by the server's service master key.

select name from sys.databases where is_master_key_encrypted_by_server=1

Reference: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine

This paragraph helped me:

The copy of the DMK stored in the master system database is silently updated whenever the DMK is changed. However, this default can be changed by using the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. A DMK that is not encrypted by the service master key must be opened by using the OPEN MASTER KEY statement and a password.

Answer 3

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.