SQL Mail Failing
I have a SQL 2016 server that has been sending emails out with no issues for years. I noticed back in October 2021 that some emails where not going through. I didn't think much of it as sometimes the email address is messed up. I also have several SSRS report subscriptions that run daily that email a PDF out to user(s). I've notice that some Subscriptions are emailed normally and a bunch are just failing. This is resent. When I go to send a test email from SSMS or run a simple script to send email, they are failing consistently.
I created an SSIS package that uses a C# script to send emails and that is working flawlessly. I'm really at a loss. I've gone through several hundred articles and tried several hundred methods; but nothing is fixing this.
Looking at the mail log on SQL Server I get the following failures ..
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2022-01-26T20:55:15). Exception Message: Cannot send mails to mail server. (Failure sending mail.).If I query msdb.dbo.sysmail_faileditems I get the following .. I changed some data elements for security.
mailitem_id profile_id recipients copy_recipients blind_copy_recipients subject body body_format importance sensitivity file_attachments attachment_encoding query execute_query_database attach_query_result_as_file query_result_header query_result_width query_result_separator exclude_query_output append_query_error send_request_date send_request_user sent_account_id sent_status sent_date last_mod_date last_mod_user
135092 1 <user name> NULL NULL Database Mail Test This is a test e-mail sent from Database Mail on <server name>. TEXT NORMAL NORMAL NULL MIME NULL NULL 0 1 256 0 0 2022-01-26 20:54:13.147 <account name> NULL failed 2022-01-26 20:55:15.000 2022-01-26 20:55:15.820 saAny ideas would be appreciated.
Does your SQL mail setup use the lcal iis SMTP or does it use a local LAN SMTP?
Try using iis SMTP while enabling logging to determine what is going on.
Have you checked whether the mailings windup in yourspam folder.
Use mxtoolbox.com's blacklist option to make sure your wan ip is not listed by one RBL and is being blocked by that.
But, depending on the email address you use as the message sender, the NDR fails to reach you.
Try using iis SMTP while enabling logging to determine what is going on.
Have you checked whether the mailings windup in yourspam folder.
Use mxtoolbox.com's blacklist option to make sure your wan ip is not listed by one RBL and is being blocked by that.
But, depending on the email address you use as the message sender, the NDR fails to reach you.
ASKER
we are using office365.
ok - the same tests still apply.
Are you trying to send through directly via o365 with login settings?
it is much simpler and straight forward, to add SMTP via Management console 6.0 and have your own internal relay server the message.
Issue using an external server deals with interruption when your system is sending the message.
logs much more detailed that the one the SQL Mail logs when a mailing attempt fails.
it is much simpler and straight forward, to add SMTP via Management console 6.0 and have your own internal relay server the message.
Issue using an external server deals with interruption when your system is sending the message.
logs much more detailed that the one the SQL Mail logs when a mailing attempt fails.
ASKER
There are several odd things to me.
1. this has been running find for years.
2. SSRS is using the same credentials and that seems to be running for the most part.
3. I created an SSIS package using a C# script to send mails out of the exact same server and this works flawlessly.
1. this has been running find for years.
2. SSRS is using the same credentials and that seems to be running for the most part.
3. I created an SSIS package using a C# script to send mails out of the exact same server and this works flawlessly.
We all run into things that worked for years, but do not work any more as consistently as before.
The detail needed to determine the underlyiing cause is not as robustly available in the SQL mail handler.
If memory serves, pulling the mail disposition queue
How many attempts will your SQL mail make to send the message as long as it did not get a permanent failure response (5xx)
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-mail-queued-not-delivered?view=sql-server-ver15
Using a real SMTP server that will accept all your SQL generated messages, will on failure generate an NDR with the reason why the message delivery attempt failed.
The option is totally up to you. Look at the SQL mail log to see if you can determine why the message was not accepted if that is the case, or if it was accepted, it is out of your hand as to why it was not delivered.
add the IIS SMTP (management console 6.0 smtp) and see if the issue can be identified.
What is the sender email used in the three scenarios? are you using the same one?
The detail needed to determine the underlyiing cause is not as robustly available in the SQL mail handler.
If memory serves, pulling the mail disposition queue
How many attempts will your SQL mail make to send the message as long as it did not get a permanent failure response (5xx)
https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-mail-queued-not-delivered?view=sql-server-ver15
Using a real SMTP server that will accept all your SQL generated messages, will on failure generate an NDR with the reason why the message delivery attempt failed.
The option is totally up to you. Look at the SQL mail log to see if you can determine why the message was not accepted if that is the case, or if it was accepted, it is out of your hand as to why it was not delivered.
add the IIS SMTP (management console 6.0 smtp) and see if the issue can be identified.
What is the sender email used in the three scenarios? are you using the same one?
ASKER
Yes, i've tried different intervals for sure.
the sender email is the same as the one i'm using.
the sender email is the same as the one i'm using.
Variance on size of the message? Unfortunately, the error for the message failure is not helpful to identify the cause.
Try the suggestion just for the SQL mail component. I think the SSRS uses a different interface for the mail handler.
Make sure to enable logging, and monitor the log space usage
the logs do not get cleared on their own.
The mail exchange is different the SMTP service will connect on port 25 to the mail of the sender versus authenticate to the o365 before trying to send the message.
Try the suggestion just for the SQL mail component. I think the SSRS uses a different interface for the mail handler.
Make sure to enable logging, and monitor the log space usage
the logs do not get cleared on their own.
The mail exchange is different the SMTP service will connect on port 25 to the mail of the sender versus authenticate to the o365 before trying to send the message.
ASKER
Hi all,
Found the issue. Microsoft hit again.
Check out the below link. Apparently this is new as of 2022.
https://www.reddit.com/r/usefulscripts/comments/rryka5/optin_to_the_exchange_online_endpoint_for_legacy/
Basically had to change the smtp server to smtp-legacy.office365.com
Found the issue. Microsoft hit again.
Check out the below link. Apparently this is new as of 2022.
https://www.reddit.com/r/usefulscripts/comments/rryka5/optin_to_the_exchange_online_endpoint_for_legacy/
Basically had to change the smtp server to smtp-legacy.office365.com
Why doesn't the same issue hit the SSRS?
ASKER
Not sure. I'm using outlook.office365.com for that smtp server. It maybe intermittent at this point and will need to be changed over as well. I've noticed in the past week that some subscription have failed where others have not.
normal SMTP mail exchanges are not subject to TLS transfer...
You have the option to decide what or how you want to handle things.
You have the option to decide what or how you want to handle things.
ASKER
It maybe being relayed though. I have to ask our network team.
The mailings you are sending , are they to the internal users on the office365 based domain, or you use the o365 to relay messages to a list/distribution group recipients whose mail providers you are not privy to?
Or this is a method to make sure to limit appearance of mailings without DKIm, and other markers...
Or this is a method to make sure to limit appearance of mailings without DKIm, and other markers...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I see, this is why you need that side. look at using an app password to potentially avoid the need for the two factor authentication.
Potentially could solve the issue.
Potentially could solve the issue.
https://www.sqlshack.com/troubleshooting-database-mail-failures/
you could send mail from the server using telnet - that may show you if there is a more generalised issue with sending mail from that server - the issue is, its not a true complete test - as its not sending mail in exactly the same way that sql mail is
telnet
set localecho
set logfile c:\TelnetTest.txt
OPEN mail.server.com 25
MAIL FROM: bob@bob.com
RCPT TO: whoever.company.com NOTIFY=success,failure
DATA
<enter>
<enter>
Quit