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

One first rate feature of SQL Server is the capability to get indicators  hotmail sign up when there are problems. The alert process can ship e mail notifications the use of Database Mail after you have configured your SMTP settings and setup your jobs and indicators to apply this account. In some instances, you may not have a mail server, however still want to setup indicators. In this tip we are able to stroll via how you can setup Database Mail to apply e-mail offerings like Gmail, Hotmail or Outlook.

Solution
In this demo, we can use details precise to a Hotmail account so as to test the Database Mail configuration.

Configuring SQL Server Database Mail to apply Hotmail
If you aren't acquainted with setting up Database Mail you can check with this link. This will paintings for SQL Server 2005 and later.

When navigating thru the Database Mail configuration wizard, you may come upon the below screen wherein the applicable info need to be entered. Let's consider the example of the usage of the SMTP server information for Hotmail. Enter the information as shown under.

Setup new database mail account
Outgoing Mail Server (SMTP)
E-mail Address - Enter your Hotmail or Live account
Server Name - Enter as shown in screenshot
Port variety - Enter as shown in screenshot
The server calls for a comfortable connection (SSL) - check this container

If this is left unchecked you'll encounter this error message, 'The mail couldn't be despatched to the recipients because of the mail server failure. (Sending Mail using Account four (2011-12-14T23:36:13). Exception Message: Cannot ship mails to mail server. (The SMTP server requires a at ease connection or the purchaser became no longer authenticated. The server reaction became: 5.7.0 Must trouble 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 underneath desk outlines the important SMTP server information and port numbers for Hotmail, Gmail and Outlook.

Mail Service    SMTP Server    Port Number
Hotmail    smtp.Stay.Com    587
Gmail    smtp.Gmail.Com    587
Outlook    smtp.Office365.Com    587
Sending a Test Email Using SQL Server Database Mail
Once your Database Mail profile has been configured you could send check emails for validation to verify setup is configured efficiently. Right click on on Database Mail and pick out "Send Test E-Mail" as proven below.

Send check database mail
You can also execute the Database Mail system system sp_send_dbmail with the perfect parameters the use of this sample script under.

EXEC msdb.Dbo.Sp_send_dbmail
    @profile_name = 'Enter legitimate database profile name',
    @recipients = 'Enter Valid Email Address',
    @body = 'This is a take a look at email despatched from TEST server',
    @difficulty = 'TEST EMAIL',
    @significance ='HIGH'

Gmail Additional Steps Required when the use of SQL Server Database Mail
Database Mail works pleasant the use of the Hotmail or Outlook SMTP server information. However, an additional step is required to get Gmail to work efficiently.

Gmail mechanically increases a protection flag while the Gmail SMTP server info is used for this reason. Below is a screenshot of the alert generated once I used the Gmail SMTP server information.

Gmail blocked for related account
You can also evaluation additional data about this at the Database Mail logs. You can get this statistics by using proper clicking on the Database Mail characteristic and deciding on the "View Database Mail Log" option as proven underneath.

View database mail log
In order to fix the problem with Gmail, you want to enable the option to "Allow less comfy apps". This is a placing on your Gmail account that needs to be enabled.Permit gmail permit less comfortable apps
Once this Gmail putting is enabled, Gmail SMTP server will paintings as predicted.

Database Mail Troubleshooting
After trying out, in case you are not able to receive notification emails, you may use the underneath Database Mail perspectives for troubleshooting.

Find efficiently sent e mail
This contains one row for every message efficiently sent by Database Mail.

Pick * from msdb.Dbo.Sysmail_sentitems
Find unsent electronic mail
This incorporates one row for each message that has an unsent or retrying status.

Pick * from msdb.Dbo.Sysmail_unsentitems
Find failed electronic mail tries
This incorporates one row for each message that has a failed popularity.

Pick out * from msdb.Dbo.Sysmail_faileditems
Additional options
Here are some additional perspectives sysmail_event_log and sysmail_allitems.

There is likewise a Database Mail machine saved manner msdb.Dbo.Sysmail_help_queue_sp which could be used as well.

For designated steps on troubleshooting Database Mail issues, refer to this hyperlink.

Summary
The above steps had been achieved on SQL Server 2019 Developer edition and I tested correctly the usage of my Gmail, Hotmail and Outlook bills, however this ought to work with SQL Server 2005 and later.

Note: If your organisation has an SMTP server, it's far encouraged to apply your corporation’s SMTP server and handiest use these examples in which you don't have get right of entry to to an SMTP server or in case you want to test Database Mail.