Sql-server – How to send secure emails using sp_send_dbmail

Well, after a dozen phone calls to Microsoft via Paid Support & a 1.5 hour conversation with their connectivity team & 3 weeks of traces, procmon analysis and what not i was AMAZED to be told that this is a known issue:

https://connect.microsoft.com/SQLServer/feedback/details/753426/dbmail-fails-when-using-a-linked-server-query

Basically, Microsoft advised me to create a DSN using windows authentication & the SQL native client. I then had to create a 2nd linked server using that ODBC DSN using NO security context - this works & somehow executes the remote query as the correct user.

Microsoft won’t be fixing this as it’s a small issue in the grand scheme of things.

They said that they'll get back to me if they find a fix - but i won’t be holding my breath.

I would suggest one of the below:

Method 1

1. For each error condition, create an inline, table-valued user-defined function which generates the erroneous results.
2. Make a table which contains all of the function names, with a field for the email recipients.
3. Build a procedure (MailErrorResults) which takes a function name as a parameter. If will retrieve the email recipients from the table and execute msdb.dbo.sp_send_dbmail, sending in 'SELECT * FROM ' + FunctionName + '();' as the query for the attachment.

4. Build a procedure (MailErrorResultsAll) which uses a cursor to loop over the table with the function names. In the loop, you will execute dynamic SQL along these lines:

   'IF EXISTS(SELECT * FROM ' + FunctionName + '()) BEGIN EXEC MailErrorResults @FunctionName = ' + @FunctionName + '; END'

(Beware of SQL injection attacks possible by adjusting the FunctionName values in the table.)

You could omit building the separate procedure MailErrorResults, but then you are writing the entire msdb.dbo.sp_send_dbmail call in dynamic SQL, which is ugly and error prone.

You could avoid MailErrorResults hitting the function table again by sending it everything it needs (e.g. the recipients), but your dynamic SQL in MailErrorResultsAll would become more complicated, and the function table will likely be too small for this to matter. This is especially true if you end up having more information in the function table, such as a field describing the problem along with information on how to fix it.

The advantages of this method are that (1) the SQL is only written once; (2) the error detection SQL can be written using validation (no dynamic SQL quotation mark issues); (3) a reusable execution plan can be generated; and (4) eventually the check can be embedded into your application logic. The disadvantage is that the query is run twice for each check-- to see if there are errors and then during the error sending process. That disadvantage can be mitigated with Method 2.

Method 2

You would essentially reproduce Method 1, but you could eliminate the double call to the function by creating a permanent table to store the error results. Your dynamic SQL would look something like this:

'INSERT INTO ErrorList (ErrorRunNumber, a, b, c) SELECT ' + CONVERT(varchar(50),@ErrorRunNumber) + ', a, b, c FROM ' + @FunctionName + '();'

You would have a separate table called ErrorRuns which would store each execution of this process for a particular function. You wouldn't need two procedures. Your single procedure would loop over the function table, running the above dynamic SQL for each function (getting a separate run number for each, of course), and then your msdb.dbo.sp_send_dbmail call would be passed a query like this:

'SELECT a, b, c FROM ErrorList WHERE ErrorRunNumber = ' + CONVERT(varchar(50),@ErrorRunNumber) + ';'

This trades storage space and extra writes for the double reads of the existence check in Method 1. This could be superior if your error detection is resource intensive.

Method 3

One other option, which I don't recommend, is to simply put the raw SQL statements into a functions table. You could then pass them into dynamic SQL without worrying about the single quote issues, because they could be retrieved from the table as native strings. I would only explore this if you had an aversion to user-defined functions for some reason.

For example, if your end users were really writing the queries themselves and did not have user-defined function creation rights, you might have to explore this.

Another twist to this method would be to have the procedure responsible for saving the user-specific SQL execute dynamic SQL to create the user-defined function, and then use one of the methods above.

The easier you make it for end users to execute their own SQL in your environment, though, the more security concerns you will have.