SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Procedure Signing using Certificates (SS 2005)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

toddriley
Starting Member

1 Posts

Posted - 08/11/2008 :  14:36:42  Show Profile  Send toddriley a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000