AWS Database Blog

Using Database Mail on Amazon RDS for SQL Server

July 2023: This post was updated with steps to configure Database Mail using an on-premises SMTP Server.

We’re happy to announce that Amazon RDS for SQL Server now fully supports SQL Server Database Mail. Before this release, you needed to use a variety of work-arounds to enable Database Mail, such as using linked servers. With the release of Database Mail for SQL Server, you can enable Database Mail seamlessly by using database parameter groups.

Database Mail is one of the heavily used features in Microsoft SQL Server. Database Mail enables you to send messages from the SQL Server to users by using a Simple Mail Transfer Protocol (SMTP) server. In this post, you learn how to configure Database Mail and send emails from an RDS for SQL Server DB instance via Amazon Simple Email Service (Amazon SES) and also use an on-premises SMTP server to configure Database Mail on Amazon RDS for SQL Server.

Common use cases for Database Mail are:

  • Sending text messages
  • Sending query results or reports as a text or file attachment
  • Sending email notification programmatically inside a procedure or job

For this post, we work with the following AWS services:

  • Amazon RDS for SQL Server – Database Mail is supported on Web, Standard, and Enterprise SQL Server editions.
  • Amazon SES – We use Amazon SES as our SMTP server. This is just one option; you could alternatively use another SMTP server. If so, skip the following section on setting up Amazon SES.

Setting up Amazon SES

We use Amazon SES as our SMTP server to quickly send emails out. Amazon SES is a cost-effective, flexible, and scalable email service that enables you to send email from within any application. To begin, complete the following steps:

  1. On the Amazon SES console, choose SMTP Settings.
  2. Note the values for Server Name and Port.
  3. Choose Create My SMTP Credentials.


Note: The credentials that you use to send email through the Amazon SES SMTP interface are unique to each AWS Region. Refer to Obtaining Amazon SES SMTP credentials to learn more.

  1. Enter a name for your AWS Identity and Access Management
  2. (IAM) user or leave it as default.
  3. Choose Create.
  4. Save the SMTP credentials somewhere safe; this is the only time you can download it.

  1. On the Amazon SES console, choose Email Addresses.
  2. Choose Verify a New Email Address.
  3. Enter an email address that you own to receive a confirmation email.
  4. After you confirm your email, Verification Status should show as verified.


At this point, we have all the necessary information about our SMTP server and can start configuring Database Mail.

Setting up Database Mail

Before configuring Database Mail, you first enable it through a database parameter group.

Enabling Database Mail through a database parameter group

Database Mail is enabled on an RDS instance through a database parameter group. In Amazon RDS, parameter groups act as a container for engine configuration values that are applied to one or more DB instances. Each RDS instance comes with an associated default parameter group; however, we can’t modify it. You can either use a new parameter group or an existing created parameter group. If choosing an existing parameter group, it must support your SQL Server instance edition and version. For more information about creating a new parameter group, see Working with DB parameter groups. To enable Database Mail through a parameter group, complete the following steps:

  1. On the Amazon RDS console, choose Parameter groups.
  2. Choose the parameter group you want to use.
  3. In the search box, enter database mail xps.

  1. Choose Edit Parameters to modify the value.
  2. For Values, choose 1.
  3. Save your changes.
  4. On the Amazon RDS console, choose Databases.
  5. Choose the instance you want to use.
  6. Choose Modify.

  1. Under Database options, choose the parameter group that has database mail xps set to 1.
  2. Choose Continue.
  3. Under Scheduling of modifications, choose Immediately.
  4. Choose Modify DB Instance to apply the changes.

Configuring Database Mail for an instance in a public subnet

The following diagram illustrates an example Database Mail configuration. User1 has access to Account 1 and Account 2 via Profile 1. User2 has access to all accounts via both profiles. User3 has access to Account 2 and Account 3 via Profile 2.

Before we can use Database Mail, we need to set up a mail configuration.

  1. Launch SQL Server Management Studio.
  2. Connect to the SQL Server engine of the RDS instance that Database Mail is enabled for.
  3. Open a new query.

Use the following stored procedures to create a simple Database Mail configuration.

  1. Create a Database Mail profile (a profile is a container used to store email accounts). See the following code: 
    use msdb
go 

EXECUTE msdb.dbo.sysmail_add_profile_sp 
@profile_name = 'Notifications', 
@description = 'Profile used for sending outgoing notifications using SES.' ;
  2. Add principles to the profile; use public so any user can access the profile: 
    use msdb
go 

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
@profile_name = 'Notifications', 
@principal_name = 'public', 
@is_default = 1 ;

We can grant the permissions on Database Mail objects as needed, but public is fine for now.

  1. Create the Database Mail account (make sure to enter the correct SMTP credentials): 
    use msdb
go 

EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'Acc1', 
@description = 'Mail account for sending outgoing notifications.', 
@email_address = 'example@example.com', 
@display_name = 'Automated Mailer', 
@mailserver_name = 'email-smtp.us-west-2.amazonaws.com',
@port = 587,
@enable_ssl = 1,
@username = 'SMTP-username',
@password = 'SMTP-password';

This step is suitable for RDS instances that are hosted in a public subnet. However, if your RDS instance is located in a private subnet, please refer to the next section for further guidance.

  1. Add Database Mail account to the Database Mail profile: 
    use msdb
go 

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
@profile_name = 'Notifications', 
@account_name = 'Acc1', 
@sequence_number = 1;

Configuring Database Mail for an instance in a private subnet

It’s necessary to use a VPC endpoint for Amazon SES, if your RDS for SQL Server instance is private. This enables communication between the RDS for SQL Server instance and the SMTP server. Before proceeding further, it’s important to first complete steps 1-5 from the previous section. These initial steps provide the foundation for the subsequent steps.

Create a Security Group for SES VPC endpoint

  1. Open the Amazon VPC console.
  2. Create a security group for SES VPC endpoint.
  3. Create a new inbound rule.
  4. For Type, choose Custom TCP.
  5. For Port range, enter the port number that you want to use to send email. You can use 25, 465 and 587.
  6. For Source type, choose Custom. For Source, enter the Security Group attached to your RDS for SQL Server instance.

 Create a VPC endpoint

  1. Open the Amazon VPC console and select Endpoints.
  2. Select Create Endpoint.

  1. Provide a Name.
  2. Under Service Category select AWS Services.
  3. Under Services search for smtp and select the SMTP VPC endpoint specific for that region.

  1. Under the VPC section, select the VPC in which this endpoint will be created.
  2. Under additional settings keep the default Enable DNS name and Ipv4.
  3. Under Subnets select the subnets within the availability zone.
  4. Under Security Groups select the security group we created earlier.
  5. Click on Create endpoint.
  6. Once the VPC endpoint is in available state, choose the endpoint and copy the first entry found under the DNS field.
  7. Create the database mail account using the DNS name of VPC endpoint created earlier instead of the SMTP Server Name. (make sure to enter the correct SMTP credentials) 
    use msdb
go 

EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'Acc1', 
@description = 'Mail account for sending outgoing notifications.', 
@email_address = 'example@example.com', 
@display_name = 'Automated Mailer', 
@mailserver_name = 'vpce-XXXXX-XXXX.email-smtp.us-west-2.vpce.amazonaws.com', -- VPC endpoint created in previous step
@port = 587,
@enable_ssl = 1,
@username = 'SMTP-username', 
@password = 'SMTP-password';
  8. Add Database Mail account to the Database Mail profile: 
    use msdb
go 

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
@profile_name = 'Notifications', 
@account_name = 'Acc1', 
@sequence_number = 1;

Sending a test email

Run the stored procedure sp_send_dbmail to send an email (see the following code). For the recipient, we can use the Amazon SES simulator to quickly test if sending the email was successful or not. For more testing options, see Using the Mailbox Simulator. We have to repeat the process and verify additional email addresses if we want to send to real recipients.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'success@simulator.amazonses.com',
@body = 'The database mail configuration was completed successfully.',
@subject = 'Automated Success Message';
GO

Then run this stored procedure to see all email items:

SELECT * FROM msdb.dbo.sysmail_allitems

Under the sent_status column, verify that the status is sent.

Configuring Database Mail using an on-premises SMTP Server

Some customers wanted to use an on-premises SMTP server to configure mail on Amazon RDS for SQL Server. In this section, we provide details on how to configure Database Mail to send email from an RDS for SQL Server DB instance by using an on-premises SMTP server as a mail server.

Prerequisites

You should have the following prerequisites:

  • An RDS for SQL Server instance with the database parameter database mail xps enabled through the database parameter group in the previous section
  • An on-premises SMTP server with valid credentials for authentication on the SMTP server
  • Sufficient access privileges to make configuration changes on the on-premises SMTP server

Verify on-premises SMTP server connectivity

Make sure your on-premises SMTP server is configured correctly and able to send emails. Verify the following:

  • SMTP server address – Obtain the IP address or hostname of the SMTP server
  • Port number – Note the port number used by the SMTP server (usually port 25 for unencrypted connections or port 587 for TLS/SSL encrypted connections)
  • Authentication credentials – Ensure you have the necessary user name and password to authenticate with the SMTP server
  • Encryption Requirements – Determine if the SMTP server requires a secure connection (TLS/SSL)

Enable network connectivity

Complete the following steps to enable network connectivity:

  1. The SMTP server resides outside of the AWS network, requiring verification that the server permits connections from Amazon RDS for SQL Server within a specified IP range. You need to make sure the IP range or public IP of Amazon RDS for SQL Server is in the allow list on the SMTP server. It’s important to note that the specific procedure for achieving this may vary depending on your organization’s policies and setup.
  2. Modify the security group of the RDS for SQL Server instance and add a new inbound rule to allow connection from the SMTP server.
  3. In the AWS account where the RDS instance is running, port 25 is throttled by default. You need to request AWS to remove the SMTP (port 25) throttling to make sure port 25 is not blocked. You can achieve this by using the Request to remove email sending limitations form.
  4. Add an Amazon Route 53 rule to resolve DNS for the SMTP server. This will resolve the domain of the SMTP server while sending emails from the RDS instance.

Configure Database Mail

Configure Database Mail with the following steps:

  1. Follow the same steps in the section Configuring Database Mail for an instance in a public subnet to set up Database Mail.
  2. When creating your Database Mail account, for the mail server name, use your on-premises SMTP server name or IP (for example, mysmtpsvr.com).
  3. Create the Database Mail account (make sure to enter the correct SMTP credentials): 
    use msdb
go 

EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'Acc1', 
@description = 'Mail account for sending outgoing notifications.', 
@email_address = 'example@example.com', 
@display_name = 'Automated Mailer', 
@mailserver_name = 'mysmtpsvr.com',
@port = 587,
@enable_ssl = 1,
@username = 'On-Prem-SMTP-username',
@password = 'On-Prem-SMTP-password';
  4. Add the Database Mail account to the Database Mail profile: 
    use msdb
go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
@profile_name = 'Notifications', 
@account_name = 'Acc1', 
@sequence_number = 1;

Conclusion

This post showed you how to configure Database Mail with Amazon SES. With the launch of Database Mail for SQL Server, you no longer have to find a work-around to enable and use Database Mail. You can use this solution for any other third-party SMTP server. Try out Database Mail today on the AWS Management Console, and share your thoughts and experiences in the comments.

About the authors

Andrew Zhou is a Software Development Engineer at Amazon Web Services. He works with the AWS RDS team, focusing on commercial database engines and SQL Server. He enjoys working on technical challenges and is passionate about learning new technologies.

Chandra Shekar Ramavath is a Database Engineer at Amazon Web Services. He works with the AWS RDS team, focusing on commercial database engines, SQL Server and Oracle.

Sid Vantair is a Solutions Architect with AWS covering Strategic accounts. With over a decade of experience in working with relational databases, he thrives on resolving complex technical issues to overcome customer hurdles. Outside of work, he cherishes spending time with his family and fostering inquisitiveness in his children.

Bharath Kumar is a Lead Database Consultant with the AWS Professional Services Team. He has been supporting and enabling customers to migrate their database from OnPrem data center to AWS cloud and also in migration from commercial database engines to open source database in Amazon.

Nishad Mankar is a Lead Database Consultant with AWS Professional Services. He helps customers to migrate and modernize their databases on AWS Cloud.