CODEWITHSUNDEEP
sql-serversp-send-dbmaildatabase-mail
CCNabey
CCNabey

Reputation: 31

SQL Server DatabaseMail failing very frequently all of a sudden

We have automated emails with attachments sent every day, and it's been this way for years. Last Wednesday the email side of things began failing frequently. If it was failing 100%, it might be easier to troubleshoot. But it seems very miss with some hits, so trying to figure out a pattern there has been difficult.

The description from the event log is: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2021-10-27T07:43:28). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

The same profile/account/parameters have been in place the last couple of years. The SMTP server is Office 365, port 25, and using basic auth for the account. Nothing has changed for the SQL Server Jobs Agent account either.

Things I have tried to no avail- creating another account/profile for databasemail account, upping the number of retries, changing the minimum lifetime of the executable to 12 hours instead of 10 minutes. I tried to personally run the sp_send_dbmail script (both with and without an attachment) and it is also hit or miss (more often miss) from my own account, so I'm thinking it's not an existing permissions/access issue.

I have tried to scour the internet for all ideas that could be related to this. I have patched SQL Server 2017 to CU26 hoping it might have been something in an update that fixed this, but no luck. I am at a complete loss at how this has popped up like this unexpectedly. I'm open to suggestions. Our operation is small and simple, thus we don't have anything in the infrastructure that is very complex.

Thanks in advance.

On edit: I did try running a powershell script from my local machine (windows 10) connecting to the office 365 smtp and using the credentials of the email profile for database mail. It works from my local machine just fine. Though on added note, when I try to execute the powershell script from the machine the sql server is on (windows server 2016), it gives me an error about 4 arguments in send, which with some google searching turns up that it's essentially a generic error with trying to connect to the smtp client. I'm hoping with this info it might shed more light on how this matter is encapsulated as to this point I've just been at a loss with everything I've tried so far.

Upvotes: 3

Views: 2508

Answers (1)

jbz
jbz

Reputation: 163

I discovered this week that we have been having the same problem. It started really around 10/1 and had been on and off, but not serious enough to come to my attention. This week, though, it had gotten much more severe. Yesterday I couldn't get a single email out from any of our 3 DBs, all routing through SMTP at Office 365.

Doing some digging, the one fix I could find that I believe addresses this issue is here: https://dba.stackexchange.com/a/233249/131993

Once we used that registry executable on the machine with SQL Server I was able to reliably send email on the first try. I sent around 150 messages after that fix with no issues. Because it's intermittent, I can't say with 100% certainty the issue is fixed, but so far it looks good.

Upvotes: 1

Related Questions