Procedure Signing using Certificates (SS 2005)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Procedure Signing using Certificates (SS 2005)

Author

Topic

toddriley
Starting Member

1 Post

Posted - 2008-08-11 : 14:36:42

All,

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:

USE [my_database]

CREATE PROCEDURE dbo.P_Send_Mail_Test
AS

BEGIN

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'Name',
@recipients = 'myemail@whatever.com',
@body = 'Will this certification test work?',
@subject = 'Certification Test'

END

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.

Thanks,

Todd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources