GD Bloggers

This is the blog site for Microsoft Global Delivery Communities focused in sharing the technical knowledge about devices, apps and cloud.
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

How to configure SQL Database mail to send emails using Office 365 (Exchange Online): A walkthrough

How to configure SQL Database mail to send emails using Office 365 (Exchange Online): A walkthrough

  • Comments 13
  • Likes

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 http://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 http://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]

3-      Click on the “clip_image003[4]” icon and then click options
clip_image005[4]

4-      Click on “Settings for POP or IMAP access…”
clip_image007[4]

5-      Take note of the SMTP server settings
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]

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]

4-      Click “Outbound Security” and enter the login credentials you use for the Exchange online (and office 365) as below
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]
then click “OK”

6-      Click on “Outgoing connections” and set the port correctly to 587 (or depending on your SMTP settings)
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]

3-      Follow the wizard and the critical part is to configure the access account as per the below screen
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.

Comments
  • Hi,

    Thanks for this workaround to configure an Office 365 account in SQL Server... in my case, that's the 2012 version.

    This instructions are useful if you use only 1 account in SQL Server 2012, correct? The sender of the email is always the SMTP Office365 account configured in the "Outbound Security" of the SMTP Virtual Server... right?

    The problem (for me) is that I need to configure multiple accounts in SQL Server 2012 and each account, it has a different email sender...

    In the past I have this way but this was not a problem because I had an Exchange server in the same network but now with the upgrade to Office365, I can NOT configure any of the accounts in SQL Server because of the SMTP by TLS, neither one sender address for each account in SQL...

    Can you help me with this one please?

    andre.pereira [at] softconcept.pt

    Thanks a lot.

    andre.pereira@softconcept.pt

  • Andre,
    You got any response or solution on this. I have exactly the same issue.

  • For anyone who finds this and can't seem to get mail to send: I had to make an allowance for 127.0.0.1 (localhost) in the Relay section of my SMTP server for my server to be able to relay mail successfully (and not allow ALL connections). I also had the check box enabled to allow any authenticated user to be able to send mail.

  • Can anyone tell me the Databasemail settings for mailEnable as i am unable to send emails through mailEnablr but i have sent them through office365

  • Could we use it as the email router for Dynamics CRM?

  • Can you explain why we need an SMTP relay? Why can't we send directly to Exchange online?

  • Actually - you can send directly and do not need an SMTP relay. You need to do the following to make it work:

    1) determine your SMTP fqdn and port
    a. Open Office 365 portal and go to outlook (use any one of the accounts you have set up)
    b. Click on the sprocket in the upper right corner and choose Options
    c. Locate the "Settings for IMAP and POP" link at the bottom of the page and click on it
    d. Scroll down to the SMTP section and make note of the fqdn and port

    2) Configure your Database Mail Account
    a. Open the Database Mail Configuration Wizard and navigate until you are editing the account you want to enable for Office 365/Exchange Online
    b. Set the Email Address and Reply Email Address to the address you want to use
    c. Set the server name and port to the values found in step 1 above
    d. Make sure "This server requires a secure connection" is checked
    e. Select basic authentication
    f. Set the login id to the login id (or delegated account) that matches the email address you are sending from
    g. Enter the account password and confirmation password for the account
    h. Save the account

    3) You should now be able to send a Database test email

    Couple of notes to think about.....
    1. The email from address can be that of a user, group or shared account; for the group or shared account you have to make sure that the user you will log in with has been delegated authority to send as the group or shared account
    2. Given this, it is possible to set up a single service account and mail enable it in Office 365/Exchange online and have it be the only set of login credentials used - just make sure you delegate appropriately
    3. Allows you to set up as many Database Mail Accounts as you have email addresses you want to send from.

    Have fun!

  • Edit - it worked for a day... then started kicking out as "unauthorized user". The TLS requirement appears to be the issue, which implies the SMTP server noted above.

  • The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2014-10-06T18:02:34). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for *******@****.com).

  • I didn't bother setting up an SMTP relay either and it worked great for me. I just followed Pinal's blog here: http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

    Also, if you get the 5.7.1 error it probably means you are using a "from" email address that is not authorized to send on behalf of the email address you are using to authorize the connection. Either use the same email address as you use to connect with or, if using a shared mailbox, make sure the email user is in the list of users that can send on behalf of the shared mailbox.

  • SQL Database Mail can be configured to send directly to Office 365 (Exchange online). No need of SMTP relay server.
    The server name should be set to outlook.office365.com and the port number to 587. Make sure the check box for "This server requires a secure connection (SSL)" is checked.
    Email address must be a valid Office 365 email address.
    For SMTP authentication use Basic authentication with your Office 365 email as "User name" and your password (the same you used in the "E-mail address" field).
    Make sure your firewall is not blocking outbound connections on port TCP 587.
    Enjoy.

  • Marin you are awesome!!! that worked and saved my ass! thank you so much!!1

  • How do i check that my firewall is blocking outbound connections on port?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment