Database Mail will not send email to smtp.office365.com

Question

I am using SQL Server 2017, trying to configure Database Mail. If I use smtp.gmail.com Database Mail works fine. However a client is using smtp.office365.com and I cannot get it to work. The error I get is:

The mail could not be sent to the recipients because of the mail server failure."

I know the credentials/config info I am passing work because I have a C# app that can send emails using the same information. For the Database Mail configuration I am using:

Basic Authentication
PORT = 587
Use SSL = True
Server Name = smtp.office365.com

..and yes the "Username" I am using for authentication - does match the "E-mail address" entry.

Anyone have an idea of why smtp.office365.com doesn't work in Database Mail?

One other note - every once in a while it does work - I just cannot get it work most of the time.

Answer 1

Problem Diagnosis

Start by diagnosing DatabaseMail issues via SSMS with SQL Server > Management > Database Mail (right-click) > View Database Mail Log. Example error messages you're likely to see include:

The mail could not be sent to the recipients because of the mail server failure. (...
  Exception Message: Cannot send mails to mail server. (
    Failure sending mail.
  ).
)

This, unfortunately, is a very generic error message. It probably means that your local server, .NET Framework, or the DatabaseMail.exe process itself has not yet been configured to enable the TLS 1.2 protocol, so is failing to connect using TLS 1.0 or TLS 1.1 protocols.

The mail could not be sent to the recipients because of the mail server failure. (...
  Exception Message: Cannot send mails to mail server. (
   Error in processing. The server response was: 5.7.3 STARTTLS is required to send mail [FOO.BAR.prod.outlook.com]
  ).
)

This means that "This server requires a secure connection (SSL)" has not been ticked. This must be ticked to enable the STARTTLS command that establishes a secure communications channel over which SMTP Basic authentication gets sent.

The mail could not be sent to the recipients because of the mail server failure. (...
  Exception Message: Cannot send mails to mail server. (
    The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.57 Client not authenticated to send mail. Error: 535 5.7.139 Authentication unsuccessful, SmtpClientAuthentication is disabled for the Tenant. Visit https://aka.ms/smtp_auth_disabled for more information. [FOO.BAR.prod.outlook.com]
  ).
)

This means that the Office365 mailbox being used in the Basic authentication details has not yet had the SMTP AUTH property enabled on it.

SQL Server's DatabaseMail vs. smtp.office365.com requirements

1. SMTP AUTH must be enabled on the mailbox of the sending account.

How to set up a multifunction device or application to send email using Microsoft 365 or Office 365 says:

You must also verify that SMTP AUTH is enabled for the mailbox being used. SMTP AUTH is disabled for organizations created after January 2020 but can be enabled per-mailbox. For more information, see Enable or disable authenticated client SMTP submission (SMTP AUTH) in Exchange Online.

Speak to your organization's Exchange administrator to have this setting enabled or, if you have sufficient access yourself, you can do this via PowerShell:

PS> Import-Module ExchangeOnlineManagement

PS> Connect-ExchangeOnline -UserPrincipalName administrative_user@your_domain.com

PS> Get-CASMailbox -Identity sending_mailbox_user@your_domain.com
Name                 ActiveSyncEnabled OWAEnabled PopEnabled ImapEnabled MapiEnabled SmtpClientAuthenticationDisabled
----                 ----------------- ---------- ---------- ----------- ----------- --------------------------------
sending_mailbox_user True              True       True       True        True

PS> Set-CASMailbox -Identity sending_mailbox_user@your_domain.com -SmtpClientAuthenticationDisabled $false

2. TLS 1.2 is required.

How to set up a multifunction device or application to send email using Microsoft 365 or Office 365 also says:

Transport Layer Security (TLS): Your device must be able to use TLS version 1.2 and above.

• DatabaseMail.exe is built for .NET Framework 3.5, but you need a .NET Framework installed that supports TLS 1.2 (.NET Framework 4.5.2 or later).
• TLS 1.2 client protocol should be enabled at the machine level in Registry

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

• TLS 1.2 client protocol should be enabled for .NET Framework 4.x in the Registry

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]
"SchUseStrongCrypto"=dword:00000001

• An appropriate supportedRuntime should be in DatabaseMail.exe.config file, e.g.: with Microsoft .NET Framework 4.5.2 installed:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="DatabaseServerName" value="." />
    <add key="DatabaseName" value="msdb" />
  </appSettings>
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
</configuration>

3. Via the Database Mail settings in SSMS, configure the sending account appropriately:

• Server name: smtp.office365.com
• Port number: 587 (preferred, or 25)
• This server requires a secure connection (SSL): must be ticked (this enables STARTTLS)
• SMTP Authentication:
  • Basic authentication (selected)
    • User name: sending_mailbox_user@your_domain.com
    • Password: your_office365_password
    • Confirm password: your_office365_password_again

References:

• How to set up a multifunction device or application to send email using Microsoft 365 or Office 365
• SQL Server 2016 Database Mail cannot send email smtp.office365.com
• Resolving SQL Server DatabaseMail Error: Cannot send mails to mail server. (Failure sending mail.)

Answer 2

You have to add a RegEdit DWORD SchUseStrongCrypto with a value 1 at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319

This will resolve it.

Answer 3

I found two ways...

1. Microsoft gives us a strange alternative: use a local SMTP server

https://www.opwernby.com/Article.asp?id=DBMail365

2. I like this, for me it is simple

https://www.opwernby.com/Article.asp?id=DBMail365