Configure SQL Server Database Mail on Amazon RDS


By:   |   Updated: 2020-11-10   |   Comments (1)   |   Related: More > Amazon AWS


Problem

In a traditional SQL Server, we use database mail for sending emails. It can contain informational messages, jobs failures, query output in the email body or as an attachment. It uses the Simple Mail Transfer Protocol (SMTP) protocol for sending the emails.

We plan on migrating an on-premises SQL Server database to AWS RDS SQL Server. Recently, AWS started support for Database Mail for SQL Server. How do we enable and configure it? Let's explore in this tip.

Solution

In this tip, we use the Amazon Simple Email Service (SES) for the SMTP server. You can use other SMTP servers if required.

Configure Amazon Simple Email Service (SES)

Log in to the AWS Web Console, go to Services and Simple Email Service. It opens the following home page for SES service.

Configure Amazon Simple Email Service (SES)

In the left-hand menu bar, click on SMTP settings. It displays an SMTP server name, port as per your AWS region.

SMTP configurations

For the authentication purpose, we need to create the SMTP credentials. We use these credentials while setting up the database mail profile for RDS. Click on Create My SMTP Credentials.

It creates an IAM User name and password for you.

Create My SMTP Credentials

You can copy the SMTP user name and password in database mail account.

View credentials

Verify a New email address

In the SES console, click on the Email address. We need to set up an email address and verify it. Therefore, make sure you have access to the email address to view and read the emails.

Click on Verify a New Email Address.

Verify  a New email address

Specify your email address and click on Verify this email address.

Verify this email address

It sends you a verification email.

verification email

Open your mailbox, and the email looks like below.

Click on the URL, and you get a Congratulations message after successful verification.

Congratulations message after successful verification

In the SES console, the status changes to Verified, as shown below.

SES console

Configure Database Mail for RDS SQL Server

In this article, I have the following RDS SQL Serve instance:

  • DB identifier: rdsdemo
  • Engine: SQL Server Standard edition
  • Region: ap-south-1c
  • size: db.t3.large
  • Status: Available
Configure DB mail for RDS SQL Server

AWS Parameter Group

RDS uses the parameters groups to enable the Database mail on RDS SQL Server. In the RDS dashboard, click on the parameter group, and you get a list of default and previously created parameter groups.

DB parameter group

RDS does not allow you to modify a default parameter group. Therefore, create a new parameter group and select your SQL instance version and edition in the parameter group family.

default parameter group

We have created a new parameter group [dbmail].

new parameter group

Currently, this parameter group does not contain any user configurations. Click on the parameter group and add database mail xps from the available parameters.

database mail xps

Click on Edit parameters, change its value to 1 (to enable) and save the changes.

Edit parameters

Now, we need to integrate this parameter group with our RDS instance. Select the RDS instance and click on Modify.

integrate this parameter group with RDS

In the modify RDS instance page, navigate to additional configuration, Database Options and change the parameter group to [DBmail].

Database Options

You can schedule the changes to be applied during the next maintenance window or apply it immediately.

Apply change during the next maintenance window

It modifies your RDS instance as per the new parameter group.

Modified instance

At this point, our RDS instance is capable of using the Database Mail feature. However, we need to set the Database Mail profile and account with our SMTP settings. We cannot use the GUI for Database Mail profile creation. It needs to be done using T-SQL.

Configuring Database Mail

To configure the Database Mail, connect to RDS using SQL Server Management Studio, and run the following queries.

use MSDB
GO 
EXECUTE msdb.dbo.sysmail_add_profile_sp 
@profile_name = 'RDSMail', 
@description = 'This profile is for RDS DB mail testing purpose';

Use the stored procedure msdb.dbo.sysmail_add_principalprofile_sp to grant permissions for database mail profile. In this procedure, we use the public role in the MSDB database association. Your profile name should be same as you specified in the previous step.

use msdb
go 
 
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'RDSMail', 
@principal_name = 'public', 
@is_default = 1 ;

In this step, we create a new Database Mail account with the SMTP information.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyAccount', 
@description = 'Test the DB mail feature of RDS SQL Server', 
@email_address = '[email protected]', 
@display_name = 'Automated Mailer', 
@mailserver_name = 'email-smtp.ap-south-1.amazonaws.com',
@port = 587,
@enable_ssl = 1,
@username = 'AKIASEPWAFS62Q3TNYWB',
@password = 'BPHOgKndJrX730hHJi8R3aukR0+p6Lz/9xLMdfnI+4vz' ;

It uses the following parameters:

  • @account_name: Specify the account name.
  • @email address: Specify the email address from which the mail needs to be sent.
  • @display_name: It is the display name for your email address.
  • @mailserver_name: Enter the SMTP address that you obtained earlier from Amazon SES.
  • @port: Give the port number of your SMTP. In my case, we get it from the Amazon SES portal.
  • @enable_SSL: Specify the value as 1 for SSL.
  • @username and @password: Enter the IAM user credentials for SMTP authentication.

Associate the Database Mail profile and account

In this step, we integrate the Database Mail profile with the account.

use msdb
go 
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'RDSMail', 
@account_name = 'MyAccount', 
@sequence_number = 1 ; 

Send a test email and validation

We can use another stored procedure msdb.dbo.sp_send_dbmail to send the email from database mail. In this query, you need to specify the profile name, recipient email address, email subject and its body.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'RDSMail',
@recipients = '[email protected]',
@body = 'This is a test email from DB mail feature of RDS SQL Server',
@subject = 'RDS SQL Server DB Mail Status Check';
GO

It triggers an email from the Database Mail account and returns a mail item id. In the below screenshot, it shows the mail item id 3.

You can query the msdb.dbo.sysmail_allitems table for the particular mail item id and check the status. Its status should be sent, as shown below.

SELECT sent_status,* FROM msdb.dbo.sysmail_allitems WHERE mailitem_id=3

Here, is the email triggered from the database mail and received in my Gmail account.

In the top, it shows Automated Mailer via amazonses.com.

  • Automated Mailer is the display name of the account.
  • amazonses.com is the SMTP email for Amazon SES service.
email triggered from the database mail

We can use HTML formatted emails as well for RDS DB mail similar to a traditional SQL Server. In the below script, we do the following tasks.

  • Create an RDS SQL Database [EmployeeData].
  • Create a SQL table [Employee1] and insert a few records into it.
  • Define an HTML format for DB mail.
  • Use DB mail to send the email to the specified email address.
CREATE DATABASE EmployeeData
Go
Use EmployeeData
Go
 
CREATE TABLE [dbo].[Employees1](
   [EmpID] [int] NULL,
   [Employee Name] [varchar](100) NULL,
   [Gender] [char](1) NULL,
   [Department] [varchar](100) NULL
) 
GO
 
INSERT INTO [Employees1]
SELECT 1,'Rajendra','M','IT'
UNION ALL
SELECT 2,'Kavita','F','HR'
UNION ALL
SELECT 3,'Mohan','M','Admin'
 
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
 
SET @xml = CAST(( SELECT [EmpID] AS 'td','',[Employee Name] AS 'td','',
[Gender] AS 'td','', Department AS 'td'
FROM [EmployeeData].[dbo].[Employees1]
ORDER BY [EmpID] 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
 
SET @body ='<html><body><H3>Employees Data</H3>
<table border = 1>
<tr>
<th> Emp ID </th> <th> Employee Name </th> <th> Gender </th> <th> Department</th></tr>'    
 
SET @body = @body + @xml +'</table></body></html>'
 
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'RDSMail',
@recipients = '[email protected]',
@body = @body,
@body_format ='HTML',
@subject = 'Employees Data';
GO

It sends the following HTML formatted email from the RDS SQL Server instance.

HTML formatted emails
Next Steps
  • If you have SQL Server RDS instance in AWS, make sure you configure Database Mail for useful notifications.
  • Check out the following tips:


Last Updated: 2020-11-10


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Comments For This Article




Wednesday, November 11, 2020 - 10:29:18 AM - Kusum Back To Top (87781)
Thanks for sharing the detailed information and steps. Excellent article!!


download





Recommended Reading

Limitations of SQL Server Native Backup and Restore in Amazon RDS

Serverless ETL using AWS Glue for RDS databases

Restore SQL Server database backup to an AWS RDS Instance of SQL Server

Troubleshoot Slow RDS SQL Servers with Performance Insights

How to Natively Import Data from Amazon S3 to an RDS SQL Server Database














get free sql tips
agree to terms