xp_sendmail: failed with mail error 0×80040111

I ran into this error today while diagnosing a sql alert that was sending mail utilizing xp_sendmail on a clustered SQL Server 2000 machine.  The funny thing was that I was told that when SQL gets failed over to the other node it worked fine.  That seemed really strange to me…so I decided to investigate to see if I could get it fixed once and for all.  Sure enough when I tried to send a test xp_sendmail message I got the following error:

Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0×80040111

Since this was 2000, the mail must be configured to use a MAPI profile in order to send mail.  Because of this I decided I should take a look at the mail profile itself.  So I opened Outlook and took a look at the mail profile.  Everything seemed to be working fine for me.  That’s funny, it works for me, but when SQL fires it on one node it works, but on the other one it doesn’t.  What could be causing this to stop working?  I failed the cluster over and I took a look at the email profile over on the side where it worked.  Everything was the same on the email profile.  This got me thinking, that perhaps it is permissions.  So I looked at the SQL Server service account and compared that to the other node.  It turns out that they were using two different accounts for the SQL Server service.   When I logged in as the service account where the errors were occuring and I opened up Outlook, I got the error stating that the exchange server was unavailable.  This confirmed that is must have due to the login credentials of the SQL Server service account.  I changed the service account to use the same service account that worked on the other node and all the email alerts were back to normal again.  So, if you ever run into an  0×80040111 error with xp_sendmail, we should make sure that the sql server service account that is running has access to the mail account used in the MAPI.  Once that is set and the account can freely send and recieve mail, then everything should work beautifully.

UPDATE:

I got a great tip in an email from Tim Edwards BLOG|Twitter so I am copying it in here now:

“Unfortunately, because the MAPI protocol isn’t cluster aware, SQL Mail is not fully supported by Microsoft on Windows clusters (see http://support.microsoft.com/kb/298723/EN-US/).  If you have an SMTP server available, the preferable method in that situation would be to go send your alerts out via SMTP (good article from SQLServerCentral.com on how to accomplish that here: http://www.sqlservercentral.com/scripts/Miscellaneous/30510/).  The only downside is that this means you have to create an extra job step for notification since SMTP is not native in SQL Server 2000 and thus is not integrated with the SQL Agent notifications and alerts.”

%d bloggers like this: