How to configure SQL Database mail so send emails using Office 365 (Exchange Online): A walkthrough
Introduction
SQL Server has a feature called database mail. This feature allows the database server to send emails to any external entity using SMTP server. The problem happens if you have installed an on-premise SQL server and an online (Office 365) Exchange server in the cloud. How can you use this Exchange server in the cloud to send database emails?
This blog post provides a complete walkthrough on how to configure this. This is based on the description provided in the KB article https://support.microsoft.com/kb/2600912.
The Walkthrough
The steps start with the following.
Step 1: Get the SMTP settings for your Exchange online server
1- Go to the address https://dev.office.com to sign up for a trial account for office 365.
2- After the exchange service is provisioned go to the tenant administration page and click on Outlook
![clip_image002[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/1212.clip_image0024_3E3E7712.jpg "clip_image002[4]")
3- Click on the “![clip_image003[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/3858.clip_image0034_1FC4105C.jpg "clip_image003[4]")
” icon and then click options
![clip_image005[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/3872.clip_image0054_05177443.jpg "clip_image005[4]")
4- Click on “Settings for POP or IMAP access…”
![clip_image007[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/7585.clip_image0074_07FCB329.jpg "clip_image007[4]")
5- Take note of the SMTP server settings
![clip_image009[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/6507.clip_image0094_3475BD42.jpg "clip_image009[4]")
In this case the Server settings are noted as it will be used in the next step.
Step 2: Install and configure an On-prem SMTP server
Next you will need to install an SMTP server in your network to relay to the Exchange online. I am using Windows Server 2012 but you can use any SMTP server.
1- Configure the SMTP server role on your local server.
![clip_image011[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/4861.clip_image0114_7C935351.jpg "clip_image011[4]")
2- Open the IIS 6.0 management console. Right click on the SMTP server and open the properties window
3- Click on the delivery tab
![clip_image013[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/3343.clip_image0134_405761DC.jpg "clip_image013[4]")
4- Click “Outbound Security” and enter the login credentials you use for the Exchange online (and office 365) as below
![clip_image015[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/6505.clip_image0154_483EDE71.jpg "clip_image015[4]")
Remember to enable “TLS encryption”
5- Click “OK” then click “Advanced”. Enter the SMTP server URL you got in the previous step in the Smart host edit box
![clip_image017[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/0652.clip_image0174_300B4E49.jpg "clip_image017[4]")
then click “OK”
6- Click on “Outgoing connections” and set the port correctly to 587 (or depending on your SMTP settings)
![clip_image019[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/8168.clip_image0194_4A3B91A0.jpg "clip_image019[4]")
7- Click “Ok” twice to apply the settings on the SMTP local server.
Step 3: Configure the SQL Mail
1- Open the SQL management studio and connect to your local server
2- Expand the “Management node” and then right click the “Database Mail” node and click “Configure Database Mail”
![clip_image021[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/8737.clip_image0214_3756E829.jpg "clip_image021[4]")
3- Follow the wizard and the critical part is to configure the access account as per the below screen
![clip_image023[4]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/01/39/29/metablogapi/0638.clip_image0234_045731F5.jpg "clip_image023[4]")
Please note that you enter the server to send to as localhost and the email address as the email you have on the office 365 Exchange online for the same account you used to configure the delivery configuration of the local SMTP server.
4- Once finished the configuration test the email sending and you should now be able to send emails to any external recipient using you Exchange online as the relay.
Conclusion
I have showed you in this post how to have an on premise SQL server connect and use an in the Cloud Exchange server to be able to send SQL database Emails.