Posted - 08/11/2008 : 14:36:42
I am looking into using certificates and procedure signing as a temporary means of getting around granting direct permissions to the sp_OA procedures to our developers. Eventually, we will, of course, move to using CLR procedures instead of sp_OA but as it stands, I just need a way to give these permissions to our developers through stored procedure signing.
Here’s what I have been able to do and where I am running into issues. I started by creating a stored procedure that was similar to the situation we have in our real production environment. The procedure simply uses the sp_send_dbmail stored procedure to send me an email:
CREATE PROCEDURE dbo.P_Send_Mail_Test
@profile_name = 'Name',
@recipients = 'email@example.com',
@body = 'Will this certification test work?',
@subject = 'Certification Test'
Then I execute the proc to ensure that it works. It does. Then I grant permissions to a user in our database who is not a member of the sysadmin server role, impersonate him, then try to execute the procedure – “you do not have permissions to execute sp_send_dbmail”. This was expected. So I reverted to my own permissions and went to the msdb database. I created a master key that was encrypted by a password and then a certificate:
CREATE CERTIFICATE My_Certificate
WITH SUBJECT = 'User Certificate to Extend Impersonation',
EXPIRY_DATE = '12/31/2009';
Then I added a signature to sp_send_dbmail by the certificate I had created and backed the cert up to a file location. (I had also removed the private key before backing up a few of the times I tried this). I created a user from the cert in msdb, granted execute permissions on sp_send_dbmail to the user and also granted authenticate to the user.
I went back to my_database and created cert with same name from file location:
CREATE CERTIFICATE My_Certificate FROM FILE = 'C:\cert_sign.cer'
HERE IS WHERE I AM HAVING MY PROBLEM. When I then try to sign my procedure, P_Send_Mail_Test, I get an error:
ADD SIGNATURE TO P_Send_Mail_Test BY CERTIFICATE My_Certificate
Msg 15556, Level 16, State 1, Line 2
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
In other articles, I have seen examples where the author makes use of a private key and not the master key. I would be more than happy to try this but do not know how to create a private key. I even tried, when signing the procedure, using the signature from crypt_properties – didn’t work. And also tried providing the password I used when creating the master key:
ADD SIGNATURE TO P_Send_Mail_Test BY CERTIFICATE User_Certificate
WITH PASSWORD = 'WhateverItWas'
Without being able to sign my procedure, I don’t think this will work as P_Send_Mail_Test calls another procedure (namely sp_send_dbmail) from within it.
Any information you might be able to give me would be most helpful.