How to send database emails using sendgrid SMTP T-Sql

Now this is tricky and pretty impulsive when you try to send emails from database using SendGrid and the mail fails to get send and the log reads out a generic message as ‘The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2016-07-19T14:37:38). Exception Message: Cannot send mails to mail server. (Failure sending mail.).’ Additionally you get very limited help from the most trusted search partner for all our needs, GOOGLE! (at least as of now)

 Well, SendGrid free account provides you around 12k free emails per month and this service can be used to send alerts and notifications to a particular individual or group of individuals as and when required. But, a major mistake that most of us do is that we try to configure the database email using the environment credentials that we setup inside the SendGrid settings section. Instead we have to make use of SendGrid account credentials that we used at the time of registration of SendGrid.

 The below T-SQL query can be used to setup database email using sendgrid SMTP details:

USE master;

EXECUTE sp_configure 'show advanced options', 1;

RECONFIGURE WITH OVERRIDE;

EXECUTE sp_configure 'Database Mail XPs', 1;

RECONFIGURE;

GO

USE msdb;

EXECUTE msdb.dbo.sysmail_add_profile_sp

        @profile_name = 'EmailProfile',

        @description = 'Profile for sending Automated DBA Notifications';

GO

EXECUTE msdb.dbo.sysmail_add_account_sp

        @account_name = 'SampleSQLAlerts',

        @description = 'Account for Automated DBA Notifications',

        @email_address = '[email protected]',

        @display_name = 'EV SQL Alerts',

 @replyto_address = '[email protected]',

        @mailserver_name = 'smtp.sendgrid.net',

        @username = '######',--username used at the time of sendgrid registration

        @password = '######',--Password used at the time of sendgrid registration

        @port = 587;-- 25 can be used but some corporates had firewalls in place for mailers and newsletters that are sent via port 25

GO

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

        @profile_name = 'EmailProfile',

        @account_name = 'SampleSQLAlerts',

        @sequence_number = 1;

GO

USE [msdb];

EXEC msdb.dbo.sp_set_sqlagent_properties

     @databasemail_profile = N'EmailProfile';

EXEC msdb.dbo.sp_add_operator

     @name = N'SampleEmailOperator',

     @enabled = 1,

     @pager_days = 0,

     @email_address = N'';

 

Post your comment if this helps.