SQL Server Database Mail configured with the Send Grid Email Service in the Azure Market Place

By:   |   Updated: 2016-10-13   |   Comments (4)   |   Related: 1 | 2 | 3 | > Database Mail


Problem

How can I setup database mail for SQL Server 2016 running on Azure's Infrastructure As A Service (IAAS)?

Solution

The Azure Marketplace has the SendGrid email delivery service that supplies your virtual machine with an SMTP relay. Leverage this service to setup database mail for your alerting or reporting needs.

Business Problem

Your manager has asked you to install and configure database mail for the test system you just built. This tip builds upon the virtual machine that was created for the Contoso Retail BI sample database. Please read this previous tip for more details.

High Level Tasks

There are four steps to accomplish this simple task.

  1. Install and configure the SendGrid service.
  2. Validate service settings.
  3. Install and configure database mail.
  4. Send a test message to confirm the deployment.

Create Send Grid Service

There are several steps that need to be executed to install and configure this service. First, log into the Azure portal and bring up the Azure dashboard. On the dashboard, click the plus sign (new service) under the main menu and search the Azure Marketplace for the SendGrid email delivery service. Double click the selection to move onto the next screen (blade).

Azure Market Place

The second step allows the end user to read more about this service. If your application sends less than 25K emails a month, the service is absolutely FREE. That is a great deal! Find out more information about pricing options if your company will exceed this limit. Click the create button to move onto the next screen (blade).

Create SendGrid Service

The third step is to fill in all the required information and accept the terms of the agreement.

The name of the service, grid4mail, is the object name that will show up under your subscription. I choose a strong password and placed the service in the same resource group, rg4tips16, as the virtual machine SQL16DEV. Contact information, pricing tier and agreement acceptance are all required to create the service. If you are satisfied with your choices, click the create button. This will close the screen and process your request.

Supply Information

Validate Send Grid Settings

It is always wise to check the settings of an Azure object that you have just deployed. On the Azure dashboard, the click the cube image (resource groups) under the main menu. Double click the name of the only resource group. The following screen should show up. We can see that the grid4mail service has been deployed under the rg4tips16 resource group. Double clicking this item brings up details on the service.

Azure Objects By Resource Group

The general section under the SendGrid setting supplies us with the information we entered when creating the service. The properties screen can be used to determine the pricing tier and data center location of the service.

Send Grid Properties

The configurations screen can be used to get the User Name and SMTP Server URL. These are very important pieces of information that we will need when configuring database mail. Use the copy icon to save this information to a handy text file opened in Notepad.

Send Grid Configuration

Last but not least, the contact screen shows the end user information that was entered before creating the service.

Send Grid Contact Information

Install SQL Server Database Mail

The next set of tasks require you to use the remote desktop protocol (RDP) to log onto the virtual machine named SQL16DEV and launch SQL Server Management Studio (SSMS).

The quickest way to setup and configure database mail is to execute T-SQL scripts. By default, database mail is not installed. Please see sp_configure for server options that you can set via code.

The TSQL script below turns on 'show advance options' and enables 'database mail extended stored procedures'.

/*
   Turn on database mail
*/

-- Select the correct database
USE [msdb]
GO

-- Just shows standard options
sp_configure
GO

-- Turn on advance options
sp_configure 'show advanced options', 1;
GO

-- Reconfigure server
RECONFIGURE;
GO

-- Turn on database xp's
sp_configure 'Database Mail XPs', 1;
GO

-- Reconfigure server
RECONFIGURE
GO

The first step in configuring database mail is to create a mail account.

The sysmail_add_account_sp system stored procedure will do this work for us given the correct inputs. Make sure you supply the @mailserver_name and @username that you copied from the SendGrid configuration screen. This information is saved in our text file. The @password is the string you supplied when you created the service. The @email_address is the actual from address while the @display_name is what is shown in Outlook. The rest of the parameters are pretty straight forward.

The T-SQL script below creates a mail account called act_Default_Email.

/*
   Creating mail account with Send Grid SMTP server
*/

-- Create a Database Mail account 1
EXEC msdb.dbo.sysmail_add_account_sp
  @account_name = 'act_Default_Email',
  @description = 'Mail account for use by all database users.',
  @email_address = '[email protected]',
  @replyto_address = '[email protected]',
  @display_name = 'SQL SERVER (IAAS-SQL16DEV)',
  @mailserver_name = 'smtp.sendgrid.net',
  @username = '[email protected]',
  @password = 'enter your unique password';
GO

-- Show the new mail accounts
EXEC msdb.dbo.sysmail_help_account_sp;
GO

The second step in configuring SQL Server database mail is to create a mail profile.

The sysmail_add_profile_sp system stored procedure will perform this action given the correct inputs. Mail profiles allow a database administrator to logically group one or more accounts into a mail service. In our example, we are using only one SMTP service. Therefore, it is susceptible to outages by our one vendor. Adding additional accounts/vendors makes the mail service more resilient.

The T-SQL script below creates a mail profile called prf_Default_Email.

/*
   Creating a mail profile
*/

-- Create a Database Mail profile
EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'prf_Default_Email',
  @description = 'Profile used for administrative mail.' ;
GO

-- Show the new mail profile
EXEC msdb.dbo.sysmail_help_profile_sp;
GO

The third step in configuring database mail is to link our mail profile to one or more mail accounts. In our example, we have one mail account. The sysmail_add_profileaccount_sp system stored procedure will execute the task for us given the correct inputs.

The T-SQL script below links the prf_Default_Email profile to the act_Default_Email account. 

/*
  Linking the mail profile to the account
*/

-- Add the account 1 to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = 'prf_Default_Email',
  @account_name = 'act_Default_Email',
  @sequence_number = 1 ;
GO

-- Show the link between profile and accounts
EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'prf_Default_Email';

The fourth step in configuring database mail is to give public access to the mail profile. This allows database users to send mail. The sysmail_add_principalprofile_sp system stored procedure will complete the action for us given the correct inputs.

The T-SQL script below sets properties of the prf_Default_Email profile to public and default.

/*
   Given public access to profile
*/

-- Grant access to the profile to all users in the msdb database
EXEC msdb.dbo.sysmail_add_principalprofile_sp
  @profile_name = 'prf_Default_Email',
  @principal_name = 'public',
  @is_default = 1 ;

-- Show the new default profile
EXEC msdb.dbo.sysmail_help_principalprofile_sp

Again, it is prudent to verify the information that you coded in the T-SQL script matches the server settings. By browsing the object explorer in SQL Server Management Studio, you will find the management node that contains the Database Mail item. Right clicking the item allows you to configure Database Mail. Choose the manage Database Mail accounts and profiles to bring up the following screen. You now can double check your work.

Verify Database Mail Settings

Validate SQL Server Database Mail

The easiest way to validate this whole solution is to send an email message to an Outlook account from the database server. The following T-SQL code sends an email to my [email protected] email account. There are many different parameters that can be used with the sp_send_dbmail stored procedure. This includes sending HTML formatted messages, adding attachments and setting the priorities of the message. Use the above hyperlink to learn more.

/*
   Send test message
*/

-- Plain text message
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'prf_Default_Email',
  @recipients = '[email protected]',
  @body = 'The stored procedure finished successfully.',
  @subject = 'Automated Success Message' ;
GO

The image below shows the test message arriving correctly at the destination.

Automated Test Message

Summary

Today's information technology specialists have to be versed in both old and new techniques. Installing and configuring the SendGrid email delivery service is a new task that you can add your tool belt. However, deploying SQL Server Database Mail has been a task performed by Database Administrators for years. Knowing when and how to combine your skill sets to solve a business problem is key for today's employees.

Although the focus of the tip was on enhancing the SQL Server 2016 Virtual Machine, the SendGrid service can be leveraged by any product or language that supports a SMTP relay. For instance, an Automation Batch job that executes a PowerShell workflow can use this same service to send emails.

Next Steps
  • The next step is to install basic alerting on our test server.
    How can we configure alerts for severity levels 17 to 24?
    Please see Brian Kelly's tip on alerting.


  • Alerting is dependent upon MSDB database being available.
    Is there a way to make sure the DBA's get alerts when this database is down?
    Please see Ken Simmons's tip on the failsafe operator.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author John Miner John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

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

View all my tips


Article Last Updated: 2016-10-13

Comments For This Article




Tuesday, January 7, 2020 - 8:07:44 AM - John Harding Back To Top (83645)

This is the only article I could find on the subject. The Azure portal has changed significantly since the article, and there is no such username type setup anymore on Azure portal for SendGrid. 

If I look at Azure Portal -> SendGrid Accounts - what should I use as the username now that the username is not shown in the portal?

Thursday, April 12, 2018 - 4:51:50 AM - Ved Prakash Back To Top (75683)

 Hi Team,

Can we send the image in mail body using sendGrid SQL Server. Please provide answer on high priority.

 

Tuesday, July 4, 2017 - 6:59:41 AM - Rajashekhar Back To Top (58844)

Hi John,

I hear that ,Three part naming wont work in SQLAZURE.so referencing TEMPDB or any cross database queries wont work in azure. So in this scenario how it will work below statement,

 

EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'prf_Default_Email',
  @description = 'Profile used for administrative mail.' ;
GO

-- Show the new mail profile
EXEC msdb.dbo.sysmail_help_profile_sp;
GO

could you please explain above scenario.

Thanks.

Thursday, October 13, 2016 - 7:21:24 AM - Junior Galvão - MVP Back To Top (43550)

Hi, Jonh.

Great article! Thanks for sharing this information.

Regards.

 

 














get free sql tips
agree to terms