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:
- Create a cert in the user DB.
- Backup/restore that cert to master.
- Create login in master, assigned that permissions, etc.
- 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