How can I send email using the Amazon SES feature when my RDS for SQL Server instance is in either a public or private subnet?

Short description

To send email with Amazon SES using an Amazon RDS for SQL Server DB instance, first create your simple mail protocol transfer (SMPT) credentials using Amazon SES. Then, create a parameter group to use with Database Mail. If your DB instance is hosted in a private subnet, then configure the VPC endpoint for use in your Database Mail profile.

Resolution

Set up Amazon SES

1.    Open the Amazon SES console, and then choose SMTP settings.

2.    Note the values that are listed for Server name and Port.

3.    Choose Create SMTP credentials. This opens the AWS Identity and Access Management (IAM) console.

4.    For IAM User Name, enter a user or proceed with the default user, and then choose Create.

5.    Choose Download credentials. This is the only time that you can download your SMTP credentials, so be sure to save them somewhere secure.

6.    Open the Amazon SES console, and then choose Email Addresses.

7.    Choose Verify a new email address, and then enter the email address that you want to receive a confirmation email.

8.    After you confirm your email address, confirm that your Verification status is verified.

Set up a Database Mail parameter group

If you already have a custom parameter group to use with your Database Mail profile, then skip this step.

1.    Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.

2.    Choose Create parameter group.

3.    For Parameter group family, choose the SQL Server version and edition that you are using. For example, SQL Server 2016 Enterprise Edition uses sqlserver-ee-13.0.

4.    Enter a Group name and Description, and then choose Create.

5.    On the Parameter groups page, choose the group that you created.

6.    Choose Edit parameters, and check the box beside database mail xps.

7.    For Values, choose 1.

8.    Save your changes.

Note: If the parameter group is already associated with your RDS for SQL Server instance, then skip these steps.

9.    In the Amazon RDS console, choose Database, and then choose Modify.

10.    Under Database options, choose the parameter that you previously configured, and then choose Continue.

11.    Under Scheduling of modifications, choose Immediately.

12.    Choose Modify DB Instance to apply the changes, and then reboot your instance.

Configure Database Mail

If your RDS for SQL Server instance is hosted in a public subnet, then follow the steps in Configuring Database Mail.

But, if your instance is in a private subnet, you might see this error when you configure Database Mail:

"The mail could not be sent to the recipients because of the mail server failure.(Sending Mail using Account 1 (2020-12-08T22:20:51). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 52.63.118.124:587)."

This error indicates that your DB instance is unable to reach the mail server. To resolve this, create a VPC endpoint for Amazon SES.

1.    Identify the private IP of your instance.

2.    Create a new security group with an inbound rule to the security group associated with your instance**.** For Type, choose Custom TCP. For Port range, enter the port number that you want to use to send email. You can use 25, 465, 587, 2465, or 2587. For Source type, choose Custom. For Source, enter the private IP of your RDS for SQL Server instance.

3.    Create a VPC endpoint. After the VPC endpoint is in the available state, choose the endpoint, and then copy the first entry found under the DNS field.

4.    Re-create the Database Mail account:

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-0a9cxxxxxxxxxxxxx-xxxxxxxx.email-smtp.ap-southeast-2.vpce.amazonaws.com',
<---- VPC endpoint created in previous step
@port = 587,
@enable_ssl = 1,
@username = 'SMTP-username', <---- For user name and
password, please use the SES credentials
@password = 'SMTP-password' ;

Note: Replace @mailserver_name with the DNS you obtained when you created the VPC endpoint.

5.    If you configured Database Mail correctly, then when you run this SQL command, the email sends successfully.

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';

6.    Next, run this stored procedure to list and verify all email items. In the sent_status column, verify that the status is sent.

SELECT * FROM msdb.dbo.sysmail_allitems

Related information

Managing an Amazon RDS Custom for SQL Server DB instance

Using Database Mail on Amazon RDS for SQL Server