RDS SQL Server : Enable and Configure Database mail profile

In this article will discuss about enabling and configuring Database Mail on AWS RDS SQL Server.

Before configuring Database Mail, you've to first enable it 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.
To enable Database Mail through a parameter group, complete the following steps:

- On the Amazon RDS console, choose Parameter groups.
- Choose the parameter group you want to use.
- In the search box, enter database mail xps.
- Choose Edit Parameters to modify the value.
- For Values, choose 1.
- Save your changes.
- On the Amazon RDS console, choose Databases.
- Choose the instance you want to use.
- Choose Modify.
- Under Database options, choose the parameter group that has database mail xps set to 1.
- Choose Continue.
- Under Scheduling of modifications, choose Immediately.
- Choose Modify DB Instance to apply the changes.

Before we can use Database Mail, we need to set up a mail configuration.
- Launch SQL Server Management Studio.
- Connect to the SQL Server engine of the RDS instance that Database Mail is enabled for.
- Open a new query.
Use the following stored procedures to create a simple Database Mail configuration.

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

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

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

Please refer the below document for more details on the same : [+] Using Database Mail on Amazon RDS for SQL Server; https://aws.amazon.com/blogs/database/using-database-mail-on-amazon-rds-for-sql-server/