Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail


Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail

Author
Message
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Great article and here comes another dumb question!

After signing the procedure with certificate can the procedure be executed only by the login/user created from the certificate? Can other users execute the procedure.

"Keep Trying"
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Hi anyone there!!

"Keep Trying"
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 1807
ChiragNS (1/18/2010)
Great article and here comes another dumb question!

After signing the procedure with certificate can the procedure be executed only by the login/user created from the certificate? Can other users execute the procedure.


No, you can GRANT EXECUTE on the stored procedure that is signed by the certificate to any database user. That's the point behind using the certificate signed stored procedure to grant access. The users don't have to have elevated rights, they only need the ability to EXECUTE the signed stored procedure, or a parent procedure in the ownership chain.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 1807
FreeHansje (1/18/2010)
Jonathan,
I wish to say thanks again. I must have done something wrong, different then you describe in your solution. I could have sworn I used the same construction to test permission on the DMV's, but could not get it working. But as I said, I must have donw something different, and since there is no1 at my current position with the knowledge to talk this over I could not pinpoint the error.
Also tnx for showing that my initial 'solution' did not work. With your suggestion I can get things working.

Tnx again, I have learned a lot today.


Did you figure your problem out? If not, DM me or shoot me a contact through my blog on SQLBlog and I'll see if I can help you troubleshoot the issue further.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Jonathan Kehayias (1/22/2010)
ChiragNS (1/18/2010)
Great article and here comes another dumb question!

After signing the procedure with certificate can the procedure be executed only by the login/user created from the certificate? Can other users execute the procedure.


No, you can GRANT EXECUTE on the stored procedure that is signed by the certificate to any database user. That's the point behind using the certificate signed stored procedure to grant access. The users don't have to have elevated rights, they only need the ability to EXECUTE the signed stored procedure, or a parent procedure in the ownership chain.


Thank you.

"Keep Trying"
Rachel Byford
Rachel Byford
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1620 Visits: 952
Brilliant article.

Only one thing which is a bit of a shame - this only works if the SP is execute as owner, not if it's execute as caller.

As a result, if you look in msdb.dbo.sysmail_allitems, all items sent with this method say they were sent by sa. Also it's allowed to send using private profiles to which nobody has been granted access.

If you send the mail by adding the user to the msdb role instead, then msdb.dbo.sysmail_allitems details the correct sender.

DatabaseMail is great, but the permissions are a real struggle. In addition to the sp_send_dbmail permission problem, it's hard to get the permissions sorted out for private profiles. All the documentation says that you can grant permissions on private profiles to database roles in msdb, but in actual fact it won't let you do this.

Rachel.
FreeHansje
FreeHansje
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 810
Another question, I'm stupified by the following:

I wish to allow non-sa users to run a bcp string with xp_cmdshell. The option to use this XP is by default turned off. I can get these non-sa users to run the xp_cmdshell, it's the turning on and off of the configuration which I cannot resolve.

What I'm looking at is a SP like this:

CREATE PROCEDURE testSP1
BEGIN
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;

EXEC XP_CMDSHELL 'DIR c:\' -- some VBCP command, but for a simple example...

EXEC SP_CONFIGURE 'xp_cmdshell', 0;
RECONFIGURE WITH OVERRIDE;
END
GO



I have tried constructions with EXECUTE AS and Certificates, but sofar nada. Scoured the Internet, no example specifically on permissions on SP_CONFIGURE.
Any suggestions?

Greetz,
Hans Brouwer
sam bryant
sam bryant
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 701
I have created a stored procedure in the msdb that will basically start a job and wait for completion. I have implemented the certificate signed stored procedure in one database successfully, but would now like to incorporate it into a second database. I was thinking I would be able to use the same certificate to make this happen (create in second database from file).

This does not seem to be the case though. Doing a select on sys.certificates in the second database shows NA for pvt_key_encryption_type and NO_PRIVATE_KEY for pvt_key_encryption_type_desc. I receive the following error when trying to sign the stored procedure in the second database from this certificate

Msg 15556, Level 16, State 1, Line 1
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.


I was wondering if this is possible. And if so if there are any reasons not to do this.

I could see this expanding to other databases in the future and would like to use one certificate (since everything will be targeting the same stored procedure in the msdb database) instead of having to create numerous certificates, logins, users, etc.

Any advise/information is much appreciated.
Thanks,
Sam
Jonathan Kehayias
Jonathan Kehayias
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1822 Visits: 1807
ssb-402814 (3/30/2011)
I have created a stored procedure in the msdb that will basically start a job and wait for completion. I have implemented the certificate signed stored procedure in one database successfully, but would now like to incorporate it into a second database. I was thinking I would be able to use the same certificate to make this happen (create in second database from file).

This does not seem to be the case though. Doing a select on sys.certificates in the second database shows NA for pvt_key_encryption_type and NO_PRIVATE_KEY for pvt_key_encryption_type_desc. I receive the following error when trying to sign the stored procedure in the second database from this certificate

Msg 15556, Level 16, State 1, Line 1
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.


I was wondering if this is possible. And if so if there are any reasons not to do this.

I could see this expanding to other databases in the future and would like to use one certificate (since everything will be targeting the same stored procedure in the msdb database) instead of having to create numerous certificates, logins, users, etc.

Any advise/information is much appreciated.
Thanks,
Sam


Sam,

While you and I have passed emails to resolve the problem, I wanted to post an update here in the comments as well so that anyone else encountering this issue has the reference to how to resolve it. I posted a blog post that details the problem with a repro and then shows how to fix the issue by backing up the private key for the certificate when the certificate is backed up so that the private key can be created with the certificate from the backup files in the new database.

Certificate Signing Stored Procedures in Multiple Databases

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Bob hopw
Bob hopw
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 29
Excellent article.

I was having a permission denied on db_mail when sending a email from a sp that was being executed by Service Broker, and this was despite the user who was was executing existing in msdb.DatabaseMailUserRole and set trustworthy was on.

Ran through the article, created the certificate and assigned it .... success.

ty

Murtagh
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search