3

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.

CCNabey
  • 31
  • 4
  • SQL Server has the ability to send e-mails, but it is a very expensive SMTP server. Have you considered using an external program to get the data from SQL Server and send it? Seems like that would be easier to troubleshoot (at the very least it should get more detailed logs and you can point it at different SMTP servers quite easily). – Aaron Bertrand Oct 27 '21 at 12:02
  • In my experience the log output generated by the DatabaseMail.exe helper process is almost useless. Better to get onto your outbound SMTP server and grep the logs there to see what the actual problem(s) is(are). – AlwaysLearning Oct 27 '21 at 12:22
  • @AaronBertrand - Even if we did, that wouldn't happen overnight as that would go up to the authority to eventually make it happen. That would follow up with not substantiating the issue we are having now being a roadblock since the question I would be given is if I don't know why it's not working, how do we know the third party app will work, and also they aren't too keen on third party apps here without establishing a BAA since we deal with HIPAA data. – CCNabey Oct 27 '21 at 12:25
  • @AlwaysLearning - going to see if our helpdesk guy can get and sort through the outlook365 smtp log. – CCNabey Oct 27 '21 at 12:33
  • Yep. I wasn't suggesting that as an immediate fix, but surely you can _try_ sending e-mail to this SMTP server with something else other than SQL Server without getting CEO approval. In any case, you need to be looking at the SMTP server, not SQL Server, SQL Server is just a dumb relay of "something went wrong." – Aaron Bertrand Oct 27 '21 at 12:33
  • @AaronBertrand oh I agree, this error message of what is essentially "an error occurred" is annoying as can be. What I have been trying to do is understand or see if I can get visibility in the handoff stage of the object successfully completing in the SQL job and then the mail object being handed off to databasemail to see if there might be some smtp connection choking for any reason perhaps since it is linking up to office365 smtp or maybe something recently in office365 may have been updated that is creating issues with the sync. – CCNabey Oct 27 '21 at 12:43
  • You can rule that out if any of dozens of SMTP guns out there are also unable to send mail through this same server. Again: dbmail was bolted on in SQL Server; it's not what it's designed to do, and it certainly wasn't designed with mail server troubleshooting in mind. – Aaron Bertrand Oct 27 '21 at 12:45

1 Answers1

1

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.

jbz
  • 163
  • 7