Send notifications for an Amazon RDS for SQL Server database instance by using an on-premises SMTP server and Database Mail - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resources

Send notifications for an Amazon RDS for SQL Server database instance by using an on-premises SMTP server and Database Mail

Created by Nishad Mankar (AWS)

Environment: PoC or pilot

Technologies: Databases; Management & governance

Workload: Microsoft

AWS services: Amazon RDS

Summary

Database Mail (Microsoft documentation) sends email messages, such as notifications or alerts, from a Microsoft SQL Server database by using a Simple Mail Transfer Protocol (SMTP) server. The Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server documentation provides instructions for using Amazon Simple Email Service (Amazon SES) as the SMTP server for Database Mail. For more information, see Using Database Mail on Amazon RDS for SQL Server. As an alternative configuration, this pattern explains how to configure Database Mail to send email from an Amazon RDS for SQL Server database (DB) instance by using an on-premises SMTP server as the mail server.

Prerequisites and limitations

Prerequisites

  • An active AWS account

  • An Amazon RDS DB instance running a Standard or Enterprise edition of SQL Server

  • The IP address or hostname of the on-premises SMTP server

  • An inbound security group rule that allows connections to the Amazon RDS for SQL Server DB instance from the IP address of the SMTP server

  • A connection, such as an AWS Direct Connect connection, between your on-premises network and the virtual private cloud (VPC) that contains the Amazon RDS DB instance

Limitations

  • Express editions of SQL Server aren't supported.

  • For more information about limitations, see Limitations in Using Database Mail on Amazon RDS for SQL Server in the Amazon RDS documentation.

Product versions

Architecture

Target technology stack

  • Amazon RDS for SQL Server database instance

  • Amazon Route 53 forwarding rule

  • Database Mail

  • On-premises SMTP server

  • Microsoft SQL Server Management Studio (SSMS)

Target architecture

The following image shows the target architecture for this pattern. When an event or action occurs that initiates a notification or alert regarding the database instance, Amazon RDS for SQL Server uses Database Mail to send an email notification. Database Mail uses the on-premises SMTP server to send the email.

Amazon RDS for SQL server using an on-premises SMTP server to send email notifications to users.

Tools

AWS services

Other tools

  • Database Mail is a tool that sends e-mail messages, such as notifications and alerts, from the SQL Server Database Engine to users.

  • Microsoft SQL Server Management Studio (SSMS) is a tool for managing SQL Server, including accessing, configuring, and administering SQL Server components. In this pattern, you use SSMS to run the SQL commands to set up Database Mail on an Amazon RDS for SQL Server DB instance. 

Epics

TaskDescriptionSkills required

Remove Multi-AZ from the RDS DB instance.

If you're using a Multi-Zone RDS DB instance, convert the Multi-AZ instance to a Single-AZ instance. When you have finished configuring Database Mail, you will convert the DB instance back to a Multi-AZ deployment. The Database Mail configuration then works in both the primary and secondary nodes. For instructions, see Removing Multi-AZ from a Microsoft SQL Server DB instance.

DBA

Create an allow list for the Amazon RDS endpoint or IP address on the on-premises SMTP server.

The SMTP server is outside of the AWS network. On the on-premises SMTP server, create an allow list that permits the server to communicate with the outbound endpoint or IP address for the Amazon RDS instance or the Amazon Elastic Compute Cloud (Amazon EC2) instance hosted on Amazon RDS. This procedure varies from organization to organization. For more information about the DB instance endpoint, see Finding the DB instance endpoint and port number.

DBA

Remove port 25 restrictions.

By default, AWS restricts port 25 on EC2 instances. To remove the port 25 restriction, do the following:

  1. Sign in with your AWS account, and then open the Request to remove email sending limitations form.

  2. Enter your email address so that AWS Support can contact you with updates about your request.

  3. Provide the required information in the Use case description field.

  4. Choose Submit.

Note:

  • If you have instances in more than one AWS Region, then submit a separate request for each Region.

  • It can take up to 48 hours to process your request.

General AWS

Add a Route 53 rule to resolve DNS queries for the SMTP server.

Use Route 53 to resolve DNS queries between your AWS resources and the on-premises SMTP server. You must create a rule that forwards the DNS queries to the SMTP server domain, such as example.com. For instructions, see Creating forwarding rules in the Route 53 documentation.

Network administrator
TaskDescriptionSkills required

Enable Database Mail.

Create a parameter group for Database Mail, set the database mail xps parameter to 1, and then associate the Database Mail parameter group with the target RDS DB instance. For instructions, see Enabling Database Mail in the Amazon RDS documentation. Do not proceed to the Configuring Database Mail section in these instructions. The configuration for the on-premises SMTP server differs from Amazon SES.

DBA

Connect to the DB instance.

From a bastion host, use Microsoft SQL Server Management Studio (SSMS) to connect to the Amazon RDS for SQL Server database instance. For instructions, see Connecting to a DB instance running the Microsoft SQL Server database engine. If you encounter any errors, see the connection troubleshooting references in the Related resources section.

DBA

Create the profile.

In SSMS, enter the following SQL statement to create the Database Mail profile. Replace the following values:

  • For profile_name, enter a name for the new profile.

  • For description, enter a brief description of the new profile.

For more information about this stored procedure and its arguments, see sysmail_add_profile_sp in the Microsoft documentation.

EXECUTE msdb.dbo.sysmail_add_profile_sp
 @profile_name = 'SQL Alerts profile',
 @description = 'Profile used for sending outgoing notifications using OM SMTP Server.';
DBA

Add principals to the profile.

Enter the following SQL statement to add public or private principals to the Database Mail profile. A principal is an entity that can request SQL Server resources. Replace the following values:

  • For profile_name, enter the name of the profile you created previously.

  • For principal_name, enter the name of the database user or role. This value must map to an SQL Server authentication user, a Windows Authentication user, or a Windows Authentication group.

For more information about this stored procedure and its arguments, see sysmail_add_principalprofile_sp in the Microsoft documentation.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
 @profile_name = 'SQL Alerts profile',
 @principal_name = 'public',
 @is_default = 1 ;
DBA

Create the account.

Enter the following SQL statement to create the Database Mail account. Replace the following values:

  • For account_name, enter a name for the new account.

  • For description, enter a brief description of the new account.

  • For email_address, enter the e-mail address to send the Database Mail messages from.

  • For display_address, enter a display name to use for outgoing messages for this account, such as SQL Server Automated Notification. You can also use the value you entered for email_address.

  • For mailserver_name, enter the name or IP address of the SMTP mail server.

  • For port, leave the value of 25.

  • For enable_ssl, leave the value at 1 or enter 0 if you don't want Database Mail to encrypt communication by using SSL.

  • For username, enter the username for logging on to the SMTP mail server. If the server doesn't require authentication, enter NULL.

  • For password, enter the password for logging on to the SMTP mail server. If the server doesn't require authentication, enter NULL.

For more information about this stored procedure and its arguments, see sysmail_add_account_sp in the Microsoft documentation.

EXECUTE msdb.dbo.sysmail_add_account_sp
 @account_name = 'SQL Alerts account',
 @description = 'Database Mail account for sending outgoing notifications.',
 @email_address = 'xyz@example.com',
 @display_name = 'xyz@example.com',
 @mailserver_name = 'test_smtp.example.com',
 @port = 25,
 @enable_ssl = 1,
 @username = 'SMTP-username',
 @password = 'SMTP-password';
DBA

Add the account to the profile.

Enter the following SQL statement to add the Database Mail account to the Database Mail profile. Replace the following values:

  • For profile_name, enter the name of the profile you created previously.

  • For account_name, enter the name of the account you created previously.

For more information about this stored procedure and its arguments, see sysmail_add_profileaccount_sp in the Microsoft documentation.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = 'SQL Alerts profile',
 @account_name = 'SQL Alerts account',
 @sequence_number = 1;
DBA

(Optional) Add Multi-AZ to the RDS DB instance.

If you want to add Multi-AZ with Database Mirroring (DBM) or Always On Availability Groups (AGs), see the instructions in Adding Multi-AZ to a Microsoft SQL Server DB instance.

DBA

Related resources