Mail Configuration for AWS RDS (PASS) SQL server.

Prerequisite :

1. EC2 (Stand alone sql server).
2. SQL RDS (AWS) server.
3. SMTP credentials.
4. Both server Same VPC as your Amazon RDS for SQL Server.

Configuration

1. Create SQL server user with same credentials (Ec2 Instance and RDS Instance same )
   
   2.Create linked server RDS to EC2 Machine.

SQL Server (EC2 or on-premises) to RDS SQL Server linked server configuration

EXEC master.dbo.sp_addlinkedserver @server = N’SQLRDS’, @srvproduct=N’’, @provider=N’SQLNCLI’, @datasrc=N’Test.CB2XKFSFFMY7.US-WEST-2.RDS.AMAZONAWS.COM’;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’SQLRDS’,@useself=N’False’,@locallogin=NULL,@rmtuser=N’ajay’,@rmtpassword=’ajay@123';

SQLRDS to Sql server(EC2) Linked server configuration

EXEC master.dbo.sp_addlinkedserver @server = N’REPLTest2′, @srvproduct=N”, @provider=N’SQLNCLI’, @datasrc=N’10.0.0.135′;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’REPLTest2′,@useself=N’False‘,@locallogin=NULL,@rmtuser=N’<username>’,@rmtpassword=‘<password>’;
GO

Link: https://aws.amazon.com/blogs/database/powering-up-database-mail-on-amazon-rds-for-sql-server-how-under-armour-runs-database-mail-on-amazon-rds-for-sql-server/

2.1 once created the linked server changed the below settings

Link:

https://aws.amazon.com/blogs/database/powering-up-database-mail-on-amazon-rds-for-sql-server-how-under-armour-runs-database-mail-on-amazon-rds-for-sql-server/

Link : http://www.bradleyschacht.com/server-servername-is-not-configured-for-rpc/

3.Configure the mail profile respective ec2 machine

sp_configure ‘Database Mail XPs’, 1;
GO
RECONFIGURE
GO

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Database Mail XPs’, 1;
GO
RECONFIGURE
GO

→Allow global Profile Changed the below settings

Provide Account Name ,SMTP credentials and port number also.

4.tiggred the mail alerts using below script from RDS SQL server

EXEC [Mailserver].[msdb].[dbo].sp_send_dbmail
@recipients = ‘ajay.tangella@gmail.com’,
@body = ‘This email is from RDS’,
@subject = ‘This email is from RDS’;

Refer the below link:

https://aws.amazon.com/blogs/database/powering-up-database-mail-on-amazon-rds-for-sql-server-how-under-armour-runs-database-mail-on-amazon-rds-for-sql-server/