SQL 2019 Database Mail Setup to relay from an Office 365 Address-CodePudding

Summary: My organisation moved from Exchange to Office 365 For years the SQL Database Mail has used Local SMTP (with a spf record to stop mail being flagged as junk) The instant that Office 365 was enabled on the email address that SQL had being using (and obviously the DNS records updated), mail been to queue on the local SMTP Relay; only for some destinations but let us assume the emails that passed had limited reverse lookup. I have configured Database Mail to use [mydomain].mail.protection.outlook.com with port 25 and 587. I have created a Mail Connector using the Exchange Admin Centre. I have set local SMTP (through IIS6) to send directly (no smart host), and to use [mydomain].mail.protection.outlook.com (which should relay through the connector). Whatever I do mail does not leave my server. Support requests (both free and paid for) to Microsoft reach the "I'll get back to you" point and then go quiet.

I have struggled with Database Mail before and found a way past all the inherent protections against spam. This seems to be a problem around what Office 365 expects and I am unable to "tick the correct boxes".

CodePudding user response：

Office 365 doesn't allow anonymous SMTP relay. You must authenticate to send mail. Obviously the issue here is that would require using up a license. The preferred way is to pay for a third-party SMTP relay service. We use SendGrid to accommodate Database Mail because we also use it for email marketing campaigns. If your mail volume is small, like 100 or less emails per day, SendGrid is free.

CodePudding user response：

Thank you Stephen.I don't reject that as the path that may work; for the benefit of this post and others that go through the experience I am providing the solution that finally worked for me.

Problem: I historically used local SMTP as my relay (configured with IIS6). When my email host changed from an Exchange Server to Office 365 some destination emails (domains) received their mail but others did not; it seemed to be related to how the recipient mail was configured.

I enabled Database Mail in SQL 2019

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
sp_configure 'show advanced options', 0;
RECONFIGURE;

I ensured TLS1.2 was configured and enabled by performing a Registry Edit (as described in https://windowsreport.com/windows-server-enable-tls/ (you need to reboot afterwards). I missed this initially and had noticed a reference to [STARTTLS] in the mail send error without understanding what it referred to.

In SSMS I went to Management -> Database Mail -> (Right Click) Configure Database Mail -> Set up Database Mail, and follow the wizard.

The key is when adding the SMTP Account to use smtp.office365.com port 587, and to Check the [Use SSL] option.

There are changes required in Microsoft 365 Admin Centre; the Email User needs to have SMTP Authentication set ON, and Multi-Factor Authentication set OFF.

After that it worked.