Setup SQL Server Database Mail to use a Gmail, Hotmail, or Outlook account

By: Mohammed Moinudheen   |   Updated: 2019-10-22   |   Comments (47)   |   Related: 1 | 2 | 3 | > Database Mail

Problem

One great feature of SQL Server is the ability to get alerts when there are issues. The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account. In some cases, you may not have a mail server, but still want to setup alerts. In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail or Outlook.

Solution

In this demo, we will use details specific to a Hotmail account in order to test the Database Mail configuration.

Configuring SQL Server Database Mail to use Hotmail

If you are not familiar with setting up Database Mail you can refer to this link. This will work for SQL Server 2005 and later.

When navigating through the Database Mail configuration wizard, you will encounter the below screen wherein the relevant details need to be entered. Let's consider the example of using the SMTP server details for Hotmail. Enter the details as shown below.

Outgoing Mail Server (SMTP)

• E-mail Address - Enter your Hotmail or Live account
• Server Name - Enter as shown in screenshot
• Port number - Enter as shown in screenshot
• The server requires a secure connection (SSL) - check this box
  
  If this is left unchecked you will encounter this error message, 'The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2011-12-14T23:36:13). 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.0 Must issue a STARTTLS command first).'

SMTP Authentication

• Select 'Basic authentication'
• User Name - Enter your Hotmail or Live account
• Password - Enter password of your Hotmail or Live account.
• Confirm Password - Enter password of your Hotmail or Live account.

The below table outlines the necessary SMTP server details and port numbers for Hotmail, Gmail and Outlook.

Sending a Test Email Using SQL Server Database Mail

Once your Database Mail profile has been configured you can send test emails for validation to verify setup is configured correctly. Right click on Database Mail and select "Send Test E-Mail" as shown below.

You could also execute the Database Mail system procedure sp_send_dbmail with the appropriate parameters using this sample script below.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Enter valid database profile name',
    @recipients = 'Enter Valid Email Address',
    @body = 'This is a test email sent from TEST server',
    @subject = 'TEST EMAIL',
    @importance ='HIGH'

Gmail Additional Steps Required when using SQL Server Database Mail

Database Mail works fine using the Hotmail or Outlook SMTP server details. However, an additional step is required to get Gmail to work correctly.

Gmail automatically raises a security flag when the Gmail SMTP server details is used for this purpose. Below is a screenshot of the alert generated when I used the Gmail SMTP server details.

You can also review additional information about this on the Database Mail logs. You can get this information by right clicking on the Database Mail feature and selecting the "View Database Mail Log" option as shown below.

In order to fix the issue with Gmail, you need to enable the option to "Allow less secure apps". This is a setting in your Gmail account that needs to be enabled.

Once this Gmail setting is enabled, Gmail SMTP server will work as expected.

Database Mail Troubleshooting

After testing, if you are unable to receive notification emails, you could use the below Database Mail views for troubleshooting.

Find successfully sent email

This contains one row for each message successfully sent by Database Mail.

select * from msdb.dbo.sysmail_sentitems

Find unsent email

This contains one row for each message that has an unsent or retrying status.

select * from msdb.dbo.sysmail_unsentitems

Find failed email attempts

This contains one row for each message that has a failed status.

select * from msdb.dbo.sysmail_faileditems

Additional options

Here are some additional views sysmail_event_log and sysmail_allitems.

There is also a Database Mail system stored procedure msdb.dbo.sysmail_help_queue_sp which could be used as well.

For detailed steps on troubleshooting Database Mail issues, refer to this link.

Summary

The above steps were performed on SQL Server 2019 Developer edition and I tested successfully using my Gmail, Hotmail and Outlook accounts, but this should work with SQL Server 2005 and later.

Note: If your organization has an SMTP server, it is recommended to use your company’s SMTP server and only use these examples where you don't have access to an SMTP server or if you need to test Database Mail.

Next Steps

• Get familiar with the Database Mail features, you can refer to this link for more information.
• Try testing this tip in your own test environment using your personal Hotmail, Gmail or Outlook accounts.

About the author

Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips