Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have spent about 30 hours so far trying to get sp_send_dbmail to work. I finally got SOMETHING to work, but I have serious doubts as to its security. I would like know a method which is better yet quick and easy to setup (I'm severely limited on time at this point), and the 'ideal' (best practice) solution.

I found this post suggesting the "correct" solution and I read up on this article where I discovered that it's a pretty involved solution with too many things that can go wrong it seems, not to mention difficult to maintain.

My thoughts for an alternative solution are these.

1. Setup an Application Role in msdb (this would be my first time using one
and not even sure if it IS a solution :P), and use it to ensure that email
can ONLY be generated from the app and not by connecting directly to the server.
2. Abandon the trigger and instead run a job on the table to send email. Definitely
NOT ideal as I would prefer the email to go out instantly, but at least this
way I would be able to control the user context. Additionally this is similar
to the current mechanism, except it would use a Stored Procedure instead of a
DTS package.

The issues I've been battling these 30 hours and the reason for my inquiry are related to the security context under which the trigger was being executed. When I did my testing, everything worked great because I am a DBA, but when my users used the app responsible for generating the email, it would break because their credentials were not sufficiently high to for the trigger to execute the sp_send_dbmail proc, and therefore it broke the app. (Unfortunately my error handling as the SQL Server level is less than desirable at the moment).

2 Answers
2

You are far better off simply inserting into a mail queue table in the trigger and having a separate email process handling the mail - either in SQL Server Agent or an external program.

This gives you the benefit of using the trigger as you desire, relatively low latency as the other job can see the mail once it's committed, scalability of managing the mail load independently etc.

You can wrap the mail queueing into the transaction properly. Then the queueing app is reliable if the mail server is temporarily unavailable or if you have email address issues. If mail needs to be resent, it's simply a matter of resetting the sent flag. You can make expiration on mail, so that mail which couldn't be sent to the SMTP won't be retried after a certain time. You can quickly route all mail to a test mailbox. You can do quite interesting things with attachments and other things based on data in the queue without tying up the trigger for extended periods - things like generating reports etc.

So the app offloads the mail responsibility to another component and is free to complete the database transaction and return success to the users, while the mail subsystem gets on with its work ensuring all you want to do with the mail gets done.

However, in this way I cannot send email immediately, correct? If so that's very unfortunate, but it seems like you are making the point that that would be a good thing for load management?
–
ChiramisuDec 19 '12 at 1:03

4

@Chiramisu How "immediate" does it need to be? Given that there is always latency as it bounces from SMTP server to SMTP server, gets spam checked along the way (potentially multiple times), and given that most normal people don't read their e-mail the microsecond it arrives, is a minute later really too slow? Is 30 seconds later too slow? What is the content of this e-mail that has to be so immediate, and is there really going to be a business impact if the e-mail gets there in 10 seconds or 40 seconds?
–
Aaron Bertrand♦Dec 19 '12 at 1:07

1

@Chiramisu Like Aaron said - I'm not sure what immediate means for email. It already has to go to your SMTP server and then to their designated MX server and then they have to read it. Another thing that can happen is that the mail is safely in a queue and so wrapped in a transaction. You really can't wrap SMTP into your transaction. What if your mail server goes down - should your entire database transaction fail? Triggers shouldn't fail unless there's a good reason to fail the whole transaction.
–
Cade RouxDec 19 '12 at 1:16

1

@Chiramisu Same issue with calling a proc (although you can elevate the permissions on the procedure). Trust me, I've done this and we had an SMTP relay on the network with the web server and SQL Server and sometimes you just want things a little decoupled.
–
Cade RouxDec 19 '12 at 1:18

Don't reinvent the wheel. Use sp_send_dbmail from the trigger and sign the trigger with a certificate as per the very article you linked. This is the correct way. It is transactionally consistent, secure, and uses a queue to disconnect the coupling between the trigger and the mailer.

A queue table, however, can be used to archive mails that have been sent. sp_send_dbmail is "send it and forget it" unless you parse logs or are constantly looking at the sysmail tables. Not saying that's needed here, but it is a difference that I found useful in past projects.
–
Aaron Bertrand♦Dec 19 '12 at 14:00

@AaronBertrand: that is indeed a valid point. Is also a valid point that, like many other 'moving pieces' of SQL Server, DB Mail could benefit from better logging, audit and status report(s). But once you go down that path the biggest 'monitoring' hole is usually the SMTP and beyond. If mail delivery is crucial for business then parsing those mail logs is inevitable. None the less, I agree that sp_send_dbmail can 'leave no trace behind'.
–
Remus RusanuDec 19 '12 at 14:16

I'm not necessarily talking about auditing delivery etc. In my specific case it was making sure that an alert was raised and that an e-mail was actually attempted (and an e-mail for any specific condition was only attempted once over a certain period).
–
Aaron Bertrand♦Dec 19 '12 at 14:17

@RemusRusanu I see that now - because of my past experience pre-2005, I never got into using it when it was released. I guess the choice comes down to how integral is email to the application that you want some part of the email tracking on the application side and out of system tables.
–
Cade RouxDec 19 '12 at 14:41