Tip Tuesday – Email Server Setup and Use Cases

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

By:   |   Updated: 2019-10-22   |   Comments (46)   |   Related: ane | 2 | iii | More than > Database Mail

Problem

One great feature of SQL Server is the ability to get alerts when at that place are issues. The alert process tin can send email notifications using Database Mail afterwards you have configured your SMTP settings and setup your jobs and alerts to utilize this business relationship. In some cases, you may not have a mail server, just still want to setup alerts. In this tip nosotros will walk through how you can setup Database Mail service to use e-mail services like Gmail, Hotmail or Outlook.

Solution

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

Configuring SQL Server Database Mail service to use Hotmail

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

When navigating through the Database Mail configuration sorcerer, you will encounter the below screen wherein the relevant details need to be entered. Let’s consider the instance of using the SMTP server details for Hotmail. Enter the details equally shown beneath.

Outgoing Post Server (SMTP)

  • E-mail Address – Enter your Hotmail or Live account
  • Server Proper name – Enter equally shown in screenshot
  • Port number – Enter as shown in screenshot
  • The server requires a secure connection (SSL) – cheque this box

    If this is left unchecked you will encounter this error message, ‘The mail service could non be sent to the recipients because of the mail server failure. (Sending Mail using Account iv (2011-12-14T23:36:thirteen). Exception Bulletin: Cannot transport mails to mail service server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must result a STARTTLS command start).’

Popular:   [Blooper] Setup Own Server via Digital-Ocean 01 : vokoscreenNG-2021-09-11_13-04-08.mkv

SMTP Hallmark

  • Select ‘Basic hallmark’
  • 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.

Mail Service SMTP Server Port Number
Hotmail smtp.alive.com 587
Gmail smtp.gmail.com 587
Outlook smtp.office365.com 587

Sending a Test Email Using SQL Server Database Mail service

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

send test database mail

Yous could also execute the Database Mail arrangement 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 E-mail Accost',     @torso = 'This is a test email sent from TEST server',     @subject field = 'Test Electronic mail',     @importance ='HIGH'

Gmail Additional Steps Required when using SQL Server Database Mail

Database Mail works fine using the Hotmail or Outlook SMTP server details. Notwithstanding, an additional step is required to get Gmail to piece of 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.

gmail blocked for linked account

Y’all can also review additional data most this on the Database Mail logs. You lot can get this information by right clicking on the Database Mail feature and selecting the “View Database Mail Log” option equally shown below.

Popular:   Difference Between Tesla and Edison

view database mail log

In order to fix the issue with Gmail, yous demand to enable the option to “Allow less secure apps”. This is a setting in your Gmail business relationship that needs to be enabled.

enable gmail allow less secure apps

Once this Gmail setting is enabled, Gmail SMTP server volition 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 bulletin that has an unsent or retrying condition.

select * from msdb.dbo.sysmail_unsentitems
          

Find failed email attempts

This contains i 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 too a Database Postal service organization stored procedure
msdb.dbo.sysmail_help_queue_sp
which could be used as well.

For detailed steps on troubleshooting Database Mail problems, 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 afterward.

Notation: If your organisation has an SMTP server, information technology is recommended to use your company’s SMTP server and only use these examples where you don’t take access to an SMTP server or if y’all need to test Database Mail.

Next Steps
  • Get familiar with the Database Postal service features, y’all can refer to this link for more information.
  • Endeavour testing this tip in your own test environment using your personal Hotmail, Gmail or Outlook accounts.
About the author

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

View all my tips

Article Terminal Updated: 2019-10-22

Tip Tuesday – Email Server Setup and Use Cases

Source: https://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-or-outlook-account/