Yesterday, it was high-time we did the migration of the database server which hosts the P.O.S database from a SQL Server 2000 (
yes, it’s not a typo) instance to a “new” instance of SQL Server 2008.
We have checked everything or we thought we did anyway. A sneaky detail was left out to be tackled at 02:00 in the morning… :-)
The story is like this: there were some procedures that used the old
SQLMail-way of sending mails by the use of
master..xp_sendmail system stored procedure. This call to the system procedure was encapsulated within the user-stored procedure when a certain business event occurred to alert specific users and/or group of recipients. There laid the catch: “group of recipients” also called “distribution list” or “
DL” for short.
But let’s step back for a bit to refresh our memory…
SQLMail uses
Extended MAPI in order to send and receive e-mails. This means you need to install an application that supports this, i.e.
Microsoft Outlook, on the SQL server host in order for the
SQLMail to attach and assign a MAPI profile to it.
On the other hand…
Database Mail, the “new” way that we use to send/receive mails on SQL, doesn’t work like this at all. It’s far more simple to setup and configure, more versatile and for sure more robust than its ancestor, namely
SQLMail. It is based on standard
SMTP protocol and is reliable because as a process it is isolated from the engine, which means even if your
SMTP connection is “dead”, SQL Server will still queue the messages and when the connection with the
SMTP server “rises from its ashes”, it will pick-up the queue and move on to deliver the mail items. These are a couple of “goodies” there are more on scalability, security and such that you can find in the external links section at the bottom for further reading.
So, since
SQLMail is
not supported on x64-bit versions, its stored procedures cannot be installed on x64-bit platform, there was no other way but to change from the
xp_sendmail to the new one
sp_send_dbmail. That meant first of all that the user should be a member of the
DatabaseMailUser role of the
msdb database. Users that belong to this role are granted by default execution rights on the
sp_send_dbmail stored procedure. OK, fine.. done!
Everything seemed to work fine, until we tried to send to a DL (check at the beginning of the post for this acronym) instead of individual recipients. A member of my team tried to send to this DL through normal Outlook-client and it went through.
“Hmm.... that sucks!” I thought and started to dig-around the internet for an answer. After a quick research I stumbled upon a couple of other folks had the same issue and it was due to the fact that the DL that is setup on our mail server (Microsoft Exchange in our case) has the following option checked: “Require that all senders are authenticated” as the illustration shows below:
After un-checking this option, the sp_send_dbmail encapsulation from within the user-stored procedure went through smoothly and as expected.
Now, why this happened? I mean, why SQLMail could send to this DL without a problem even with this option checked? The answer lies to the MAPI profile that SQLMail uses to send/receive mails which in reality it authenticates to the mail server through i.e. Outlook while Database Mail use just the SMTP transport to send mails.
That’s all, I hope you find this useful or at least a nice light-reading while eating something for lunch-break.
TNT,
Vassilis.
External links: