November 19, 2009 at 10:54 am
NOTE: Because this falls under several Forums sections from Development to Security to Strategy I decided to drop it under the GENERAL section.
NOTE 2: I hope I’m not beating a dead horse with this one b/c I know there are numerous posts on the internet on this with a variety of replies but when I search the SQL Server Central Forums I get zero hits so apologies if the answer is already listed here somewhere.
What if any is the Best Practices approach to using the sp_send_dbmail STORED PROC in the MSDB database on SQL Server (2005/2008)?? Currently I have a custom SP that among a few other tasks will call this MSDB Stored Procedure to send an email alert that is raised by a Table Update Trigger. I am of course getting the same PERMISSION DENIED error msg that every other SQL Server users get’s when they have not explicitly done some kind of change to security for this SP so that it can be called.
The error of course comes from the fact that you have to be of a certain Role or have specific permissions for this SP in the MSDB database. That makes sense in that it follows the whole security paradigm in SQL Server however this particular SP is in my opinion not the same as any other because of what it does. Now I’ve seen various suggestions (see below List) but what I’d like to know is if any of you have any suggestions to add or thoughts comments on the ones below as well as on any that others add, specifically commenting on what you feel is a BEST PRACTICES approach to this common problem.
Thanks to all
Options for dealing with the Permission Denied error received when using the MSDB Stored procedure sp_Send_DBMail.
1) SQL Email Job – Create a table to store Email msgs/alerts and have a job check the table for new items and when it finds any, use the job to send these out thru the sp_Send_DBMail SP. By setting up the job and giving the Job owner permissions to the Stored Procedure in the MSDB database you can allow for sending emails via SQL Server.
PROS: It works. While not easy it is also not too terribly hard to implement. Minimizes potential Security issues
CONS: Unless the job runs all the time it’s not live or real time.
2) Run with the EXECUTE AS option - Setup the SP to run under a security context that has access to execute the SP
PROS: Easy to implement
CONS: May not always work (at least that’s what I read in various posts); Possible Security concerns raised
3) Grant Security Access Users - Explicitly grant EXECUTE priveledges to the sp_Send_DBMail SP to all users/roles that will call the SP
PROS: If you use Roles then so long as any new users are added to the role you need only set this up once for it to run for existing and new users alike.
CONS: Not easy to implement unless you have a few users; Raises serious security concerns depending on your environment.
Kindest Regards,
Just say No to Facebook!November 19, 2009 at 2:16 pm
UPDATE:
Well it appears Option 2 'EXECUTE AS' is no't reeally of any use by iteslf because it doesn't appear to work. In my testing, the only values that work with EXECUTE AS as CALLER and OWNER and I can't use OWNER because it aparently doesn't work with the implied or defualt 'dbo' database owner.
In our DB all objects are owned by the dbo or by the Login that is the Database Owner. When I set this to run as OWNER it fails. When I set to to CALLER it works but the login I am running the SP (the one in my DB that calls sp_Send_DBMail) has to have been given explict access to the msdb DB and to that STored Procedure. This means that every distinct login in my DB must also be granted access to the msdb database since you can't assign DB Roles (from other DB's) to msdb and there are no seevre level custom roles.
This is a reall pain in the arse. Is it just me or does it seem like Microsoft made the DB Mail functionality a nightmare to use?
Kindest Regards,
Just say No to Facebook!November 20, 2009 at 6:32 am
There is a role, DatabaseMailUser, in msdb that you can put users in.
I'm a fan of option 1 in your case, even though Database Mail is asynchronous, I just don't like sending mail from a trigger, you could do it.
I'm doing some research on issues like this using signed modules. I'll have to see if it will work here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 20, 2009 at 12:24 pm
Jack Corbett (11/20/2009)
There is a role, DatabaseMailUser, in msdb that you can put users in.I'm a fan of option 1 in your case, even though Database Mail is asynchronous, I just don't like sending mail from a trigger, you could do it.
I'm doing some research on issues like this using signed modules. I'll have to see if it will work here.
Thanks for the tip on the role Jack. I thought I had tried using this already and ity didn't work but I also may just have it mixed up with something else. Eve have one of those times where you've tried so many various methods you can't remember specifics on each?
I know that Option #1 is a working option and seems to have a lot of backing among users from internet posts I've seen on this but it just also seems like more of a hack then a solution. Its not a bad hack but still a hack. Any idea if this issue is fixed in 2008?
Kindest Regards,
Just say No to Facebook!November 20, 2009 at 12:44 pm
In a trigger, I'd definitely be more inclined towards inserting into a queue and then having a job send the e-mails. Schedule for once a minute, and you're not getting too far from "realtime".
I use this one a lot in things like my maintenance procs. They send me alerts about a variety of things going on in the databases that I like to know about. With those, I send directly from the procs. But those are running under DBA authority, and need to do so, so there's no permissions issue there.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 20, 2009 at 2:30 pm
YSLGuru (11/20/2009)
Jack Corbett (11/20/2009)
There is a role, DatabaseMailUser, in msdb that you can put users in.I'm a fan of option 1 in your case, even though Database Mail is asynchronous, I just don't like sending mail from a trigger, you could do it.
I'm doing some research on issues like this using signed modules. I'll have to see if it will work here.
Thanks for the tip on the role Jack. I thought I had tried using this already and ity didn't work but I also may just have it mixed up with something else. Eve have one of those times where you've tried so many various methods you can't remember specifics on each?
I know that Option #1 is a working option and seems to have a lot of backing among users from internet posts I've seen on this but it just also seems like more of a hack then a solution. Its not a bad hack but still a hack. Any idea if this issue is fixed in 2008?
Yup, been there when you can't remember if you tried everything or not.
Option 1 is the option of choice because SQL Mail was/is synchronous and should never have been in a trigger, but Database Mail is asynchronous so it can be used in a trigger, but I still prefer not to it (old habits and prejudices die hard). This has not been changed in 2008, you still need rights on Database Mail to send mail.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2009 at 10:58 am
GSquared / Jack Corbett,
Thansk to both of you for chipping in on this one. I'm suprised the response wasn't larger; maybe I'm one of the few who have to deal with this. Do most not even use SQL Server's email capabilities?
Do either of you have any outline code (T-SQL) for a Message Queue Job you;d be willing to share?
Thanks
Ed
Kindest Regards,
Just say No to Facebook!November 24, 2009 at 6:50 pm
One way to hack around this problem is to turn TRUSTWORTHY ON for the database that has your wrapper stored procedure in it. However, the best way to do this is to use EXECUTE AS OWNER, and sign the procedure with a Certificate. To do this requires the following steps:
First create the stored procedure.
Then create a certificate in the database with a strong password.
Backup the certificate to disk.
Restore the certificate in master and create a login from the certificate.
Grant Authenticate Server to the login.
Add a database user in msdb for the certificate login.
Then add the DatabaseMailUserRole to the msdb user for the certificate login.
Finally sign the stored procedure in your database with the certificate.
A full repro for this is below:
CREATE DATABASE TestDBMail
GO
USE [TestDBMail]
GO
CREATE PROCEDURE [dbo].[TestSendMail]
(
@To NVARCHAR(1000),
@Subject NVARCHAR(100),
@Body NVARCHAR(MAX)
)
WITH EXECUTE AS OWNER
AS
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'Database Mail Profile',
@recipients = @To,
@subject = @Subject,
@body = @Body
END
GO
-- This should fail
EXECUTE [dbo].[TestSendMail] 'someemail@domain.com', 'test', 'body'
-- Create a certificate to sign stored procedures with
CREATE CERTIFICATE [DBMailCertificate]
ENCRYPTION BY PASSWORD = '$tr0ngp@$$w0rd'
WITH SUBJECT = 'Certificate for signing TestSendMail Stored Procedure';
GO
-- Backup certificate so it can be create in master database
BACKUP CERTIFICATE [DBMailCertificate]
TO FILE = 'd:\Backup\DBMailCertificate.CER';
GO
-- Add Certificate to Master Database
USE [master]
GO
CREATE CERTIFICATE [DBMailCertificate]
FROM FILE = 'd:\Backup\DBMailCertificate.CER';
GO
-- Create a login from the certificate
CREATE LOGIN [DBMailLogin]
FROM CERTIFICATE [DBMailCertificate];
GO
-- The Login must have Authenticate Sever to access server scoped system tables
-- per http://msdn.microsoft.com/en-us/library/ms190785.aspx
GRANT AUTHENTICATE SERVER TO [DBMailLogin]
GO
-- Create a MSDB User for the Login
USE [msdb]
GO
CREATE USER [DBMailLogin] FROM LOGIN [DBMailLogin]
GO
-- Add msdb login/user to the DatabaseMailUserRole
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = 'DBMailLogin';
GO
USE [TestDBMail]
GO
-- Sign the procedure with the certificate's private key
ADD SIGNATURE TO OBJECT::[TestSendMail]
BY CERTIFICATE [DBMailCertificate]
WITH PASSWORD = '$tr0ngp@$$w0rd';
GO
-- This will succeed
EXECUTE [dbo].[TestSendMail] 'someemail@domain.com', 'test', 'body'
/*
-- Cleanup
USE [msdb]
GO
DROP USER [DBMailLogin]
GO
USE [master]
GO
DROP LOGIN [DBMailLogin]
DROP CERTIFICATE [DBMailCertificate]
DROP DATABASE [TestDBMail]
-- Delete the certificate backup from disk
*/
This will allow you to use permission chaining the database that the wrapper procedure exists in while enforcing best security practices in design.
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[/url]
November 24, 2009 at 6:54 pm
Great Jonathan. That's the blog post I've been working on for a couple weeks as I figure it all out:-D
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 24, 2009 at 7:01 pm
Jack Corbett (11/24/2009)
Great Jonathan. That's the blog post I've been working on for a couple weeks as I figure it all out:-D
That's the first article I just sumbitted to SSC as a part of a series I have been working on for almost a month. Look for some really fun stuff here soon. 😉
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[/url]
December 1, 2009 at 10:53 am
Thanks Johnathon I'll certianly gove that a try.
Kindest Regards,
Just say No to Facebook!December 15, 2009 at 5:05 pm
One last interesting piece on this.
I just did some more work with this and I found something I consider to be rather odd. No matter how I setup the EXECUTE AS be it OWNER or CALLER or 'dbo' or even a regular SQL Login/DB User that is a member of my database's db_Owner role, I can get this to work if I place within the trigger a call to the Email SP in MSDB.
If however I go first thru my own custom SP in my DB which then calls MSDB.dbo.sp_Send_dbMail STored Procedure it fails every time. ALl objects in my DB are owned by the implied 'dbo' and every user connecting t9o the DB is connected as the dbo so security is very simple. And yet for some reason if I call the MSDB Email SP directly it works but if I go thru my own SP first then it fails.
Any ideas why?
I was going thru my own SP first because I wanted to create a generic Email SP/Routine in my DB that would handle a variety of scenarios and that way each trigger would make a simple call to my Email SP whcih would then process teh info and call the MSDB Email SP directly.
Kindest Regards,
Just say No to Facebook!March 12, 2010 at 12:44 pm
Johnathan,
I just wanted to check back on this and see if you had done anything else with the whole issue of security and using DB mail on SQL Server? I'm getting ready to implement a live queue using a setup I put together with the sample code you provided along with some modifications specific to our setup. Before I do that I wanted to check and see if you or Jack (if he is still monitoring thsi thread) have any additional suggestions on this.
The sample code Jonathan provoided worked perfectly; a lot more then I can say about sample code in most peridocials and SQL magazines. Soo often I fond the code as provided doesn't work right because they leave out something they assume was implied. You even provided clean up code whihc was even better.
Any more on this by you?
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply