How to Configure Mail Alerts for SQL Server Job Failures

Alerting  the Admin for SQL Server Scheduled Job failure involves the following Configurations

==> Configure Database Mail.
==> Create a SQL Server job.
==> Configure Alert system in SQL Server Agent properties
==> Create an Operator.
==> Adjust the SQL Server job to send on Failure.

 Step-1: Open SSMS ---> Go to Management ---> Go to SQL Server logs ---> Go to database mail ---> Right Click ---> Configure Database Mail ---> Click on "Next"

Step-2: Create a profile by providing a suitable name and click on "Add"

Step-3: Add an account to the specified profile or else you can access the existing account from the drop down list.
Click on "New Account" and click "OK"

Step-4: 

• ==> Provide an Account name and your Mail ID.
• ==> Here i am using my gmail account.
• ==> The default port number for gmail is 587
• ==> Provide Server name as smtp.gmail.com
• ==> Check "This server requires a secure connection(SSL)"
• ==> Check Basic authentication.
• ==> In the user name provide your Mail ID and provide the password of your Mail account.
• ==> Click OK

Step-5: Click on Next to continue.

Step-6: Check the Profile you have created and set the default profile to yes

Step-7: You find the Configure System Parameters Window. Click on Next

Step-8: Click Finish.

Step-9: Click Close.

Step-10: Right Click on Database mail and select Send Test E-mail.

Provide the Mail ID to send the alert of Job failure and Click on Send Test E-Mail and Close.

Step-11: Now open your Mail account. You can find an email alert in your Inbox.

This indicates that our Mail account has been configured correctly with SQL Server.

Step-12: Click OK when you get a mail in your inbox.

Step-13: Create a Backup job and Schedule the job

==> Provide name to backup job.

==> In the Steps tab Click on New.

==> Provide the relevant step name and give the appropriate Transact SQL for backup of a database. Select the name of the database to backup from the drop down.

==> Click on Schedule tab and provide the name to schedule. Change the fields according to the requirement. Click OK

==> Click OK.

Step-14: 

• ==> Go to SQL Server Agent--->Right Click--->Properties--->Alert System
• ==> Check Enable mail profile.
• ==> Mail system-->Select Database Mail.
• ==> Mail Profile-->Profile you have created 
• ==> Check Include body of email in notification message.
• ==> Check Replacetokens for all job responses to alerts

Step-15: 

• Create an operator
• Go to SQL Server Agent--->Right Click--->New Operator
• Provide the name of the Operator
• Provide the Email address.

Step-16: 

Go to  the SQL Server Agent--->Expand--->Jobs--->Backup job created-->Right Click--->Properties--->Go to Notifications--->Check E-mail--->Select the respective operator name from the drop down--->When the job fails--->Click OK

Here i have selected “When the job succeeds” for Test Purpose.

Step-17: 

Open mail Account.

Here we find a below mail in the Inbox.

Possible Errors while Configuring Email for database job failure alerts

Error 1:

Message:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2016-08-09T11:04:36). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it ----------).

Solution 1:

• ==> First check whether smtp.gmail.com is enbled
• ==> Open cmd
• ==> Then type telnet smtp.gmail.com 587
• ==> 587 is the port number of gmail here
• ==> If u get the following error “Telnet is not recognized as the internal or external command”
• ==> Then enable telnet
• ==> Go to control panel-->programs and features-->Turn Windows features on or off-->Check the telnet client (& telnet server) checkbox.
• ==> Click ok
• ==> Now again navigate to cmd prompt and give the command
• ==> telnet smtp.gmail.com 587
• ==> Now again try creating a profile and configure mail.

Error 2:

Message

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2016-08-09T15:12:03). 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.5.1 Authentication Required.

Solution 2:

• ==> Be sure that your windows firewall is off.
• ==> Be sure that you are disconnected from VPN.
• ==> Go to your gmail-->settings-->https://www.google.com/settings/security/lesssecureapps
• ==> turn on access for less secure apps
• ==> or else some times also try gmail-->settings-->forwarding and pop /imap-->enable pop for all mail and keeps gmail's copy in the inbox