How do I use Amazon SES to send email when my Amazon RDS for Microsoft SQL Server instance is in either a public or private subnet?

Resolution

Set up Amazon SES

Use the Amazon SES console to get the Simple Mail Transfer Protocol (SMTP) credentials. 

To receive a confirmation email, complete the following steps:

1. Open the Amazon SES console, and then choose Email Addresses.
2. Choose Verify a new email address, and then enter the email address where you want to receive a confirmation email.
3. Confirm that the Verification status is verified.

Set up a Database Mail parameter group

Note: If you already have a custom parameter group to use with Database Mail, then proceed to the Configure Database Mail section. If your parameter group is already associated with your RDS for SQL Server instance, then proceed to the next set of steps in this section.

To create a custom parameter group, complete the following steps:

1. Open the Amazon RDS console.
2. Choose Parameter groups, and then choose Create parameter group.
3. For Parameter group family, choose the SQL Server version and edition that you use. 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.
6. Choose Edit parameters, and then select database mail xps.
7. For Values, choose 1.
8. Choose Save.

To associate the parameter group with your RDS for SQL Server instance, complete the following steps:

1. Open the Amazon RDS console.
2. Choose Database, and then choose Modify.
3. Under Database options, choose the parameter group, and then choose Continue.
4. Under Scheduling of modifications, choose Immediately.
5. 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 you can configure the Database Mail.

If your instance is in a private subnet, then you might get the following error message when you configure Database Mail:

"The mail could not be sent to the recipients because of the mail server failure. Exception Message: Could not connect to mail server."

If you receive the preceding error message, then your DB instance can't reach the mail server. To resolve this issue, create an Amazon Virtual Private Cloud (Amazon VPC) endpoint for Amazon SES.

Complete the following steps:

1. Identify your instance's private IP address.

2. Create a new security group with an inbound rule to the security group that's 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 address of your RDS for SQL Server instance.

3. Create a VPC endpoint.

4. Choose the endpoint, and then note the first name under the DNS field.

5. Recreate the Database Mail account:

   use msdbgo 
    
   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 name that you noted from the DNS field.

6. Run the following command to confirm that the Database Mail is correctly configured:

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

   Note: If the Database Mail is correctly configured, then the email sends.

7. Run the following stored procedure to verify all email items:

   SELECT * FROM msdb.dbo.sysmail_allitems
   

8. In the sent_status column, verify that the status is Sent.

Related information

Managing an Amazon RDS Custom for SQL Server DB instance

Using Database Mail on Amazon RDS for SQL Server