How to configure SQL Database Mail to send emails using Office 365

I have been using SQL Server Database Mail for sending mails and have used various email providers. However, while troubleshooting and helping a client, I found that it does not work with Office 365 as expected and the configuration is not same. I read many posts, contacted fellow MVPs, and of course, with guidelines, finally I configured it with my machine.

We initially tried this with one of client's servers, since it did not work with default configurations, I tried with my machine which has Windows 8.1.

If you are trying to configure Office 365 for SQL Server Database Mail, there are couple of things you need to consider;

• .Net Framework 3.5
• SSL and TLS configuration
• Patches need for TLS support on SQL Server

Net Framework 3.5

You need to make sure that .Net Framework 3.5 is enabled in the OS because SQL Server Database Mail works with 3.5. If you have not enabled it, then enable.

SSL and TLS configuration

Remember, Office 365 uses TLS, not SSL. This is the biggest issue for the SQL Server. Microsoft SQL Server 2016 is shipped with necessary components related to TLS 1.2 but you need to disable SSL 3.0 if you have already configured. In most cases, you need to disable SSL for the server but you can try first disabling SSL only for client software. However, in my case, I did not have SSL 3.0, hence I did not disable anything. If you have, follow the below URL for getting instructions for disabling SSL 3.0. But, fist try without disabling.

https://technet.microsoft.com/en-us/library/security/3009008.aspx

You can read more on this at: http://www.ryanjadams.com/2016/07/database-mail-breaks-tls-1-0-disabled

Patches need for TLS support on SQL Server

Then you need to make sure required patches are installed. Since I use SQL Server 2016, I did not want to install anything on SQL Server but I had to install a patch to the OS. All patches related to this are listed here: https://support.microsoft.com/en-us/kb/3135244

Once everything required is done, I configured my Database mail again;

And yes, it worked without any issue.

EXEC msdb.dbo.sp_send_dbmail 
 @profile_name = 'Main'
 , @recipients = 'dineshpriyankara@gmail.com'
 , @body = 'Test'
 , @subject = 'Test';

SELECT * FROM msdb.dbo.sysmail_allitems;
SELECT * FROM msdb.dbo.sysmail_sentitems;
SELECT * FROM msdb.dbo.sysmail_unsentitems;
SELECT * FROM msdb.dbo.sysmail_faileditems;