AWS RDS SQL Server SSRS

AWS RDS SSRS Email Subscription Set up

By David Speight / September 9, 2022

Today I am going to set up my AWS RDS SSRS Server for an Email Subscription. This new to AWS feature was just released end of August 2022.

To get started I will need information regarding my RDS Server, IAM Role, RDS Option group, and SMTP server. I will be using features and objects I’ve already set up elsewhere for RDS whenever possible. I will also be using Amazon Simple Email Service (SES) for my SMTP server.

I am making a list of these ARN paths so I will have them handy for use as I go along:

IAM USER for SES EMAIL: arn:aws:iam::112233445566:user/ses-smtp-user.20220000-99999
ROLE USED BY RDS: arn:aws:iam::112233445566:role/MYCOMPANYNAME-database-rds-role
ARN FOR RDS OPTION GROUP: arn:aws:rds:us-east-1:112233445566:og:MYCOMPANY-OPTIONGROUPsqlee15-9999
ARN FOR SECRET: (I will get this in STEP 2)

I also want to have my SMTP login info ready. As I mentioned, I will be using my AWS SES credentials for this.
SMTP SERVER Or SES SMTP PATH: vpce-0000099999-abcxyz.email-smtp.us-east-1.vpce.amazonaws.com
SMTP_USERNAME: SES ACCESS KEY ID
SMTP_PASSWORD: SES SECRET KEY

Step 1: CONFIGURE POLICY ON KMS ENCRYPTION KEY

I will need a KMS Encryption Key for SECRET MANAGER to use, which is where I will store the login credentials to my SMTP server. I MUST create a customer KMS key because the default AWS KMS keys should not be used. Since I already have one created for my RDS database instance, I will use the same encryption key and just add the required KEY POLICY permissions to it. If I was going to create a new key, I would still need to add the same KEY POLICY to it as well.

In summary, I am adding my RDS DATABASE ROLE and my SES SMTP USER to have access to my KMS KEY by attaching (updating) the KEY POLICY.

 {
            "Sid": "Allow RDS use of the key",
            "Effect": "Allow",
            "Principal": {
                "Service": "rds.amazonaws.com"
            },
            "Action": [
                "kms:Decrypt",
                "kms:DescribeKey"
              ],
            "Resource": "*"
        },
{
  "Effect": "Allow",
  "Principal": {
    "AWS": [
      "arn:aws:iam::112233445566:user/ses-smtp-user.20220413-999999",
      "arn:aws:iam::112233445566:role/MYCOMPANY-database-rds-role"
    ]
  },
  "Action": [
    "kms:Decrypt",
    "kms:DescribeKey"
  ],
  "Resource": "*"
}

Step 2: CONFIGURE POLICY ON THE SECRET

In SECRET MANAGER I am creating a (custom/other) entry to store my SMTP login info. Since I am using AWS Simple Email Service (SES) for SMTP I will store the User Access Key and Secret as shown here.

AFTER, I have created the SECRET, I will go back and add a RESOURCE PERMISSION that will allow my RDS OPTION GROUP to access the SECRET.

{
  "Version" : "2012-10-17",
  "Statement" : [ {
    "Effect" : "Allow",
    "Principal" : {
      "Service" : "rds.amazonaws.com"
    },
    "Action" : "secretsmanager:GetSecretValue",
    "Resource" : "*",
    "Condition" : {
      "StringEquals" : {
        "aws:sourceAccount" : "112233445566"
      },
      "ArnLike" : {
        "aws:sourceArn" : "arn:aws:rds:us-east-1:112233445566:og:MYCOMPANY-rds2019a-rdsoptiongroupsqlee15"
      }
    }
  } ]
}

Step 3: CONFIGURE POLICY ON IAM ROLE RDS USES

I also want my RDS ROLE to be able to access the SECRET, so I will find the POLICY attached to the RDS ROLE and add access to the SECRET there, as well. If you are using a separate ROLE for different options in RDS, you may need to configure this differently.

 {
            "Sid": "RdsSecretsGet",
            "Effect": "Allow",
            "Action": "secretsmanager:*",
            "Resource": "arn:aws:secretsmanager:us-east-1:112233445566:secret:MYCOMPANY-SesSecret-abc123"
        }

Step 4: CONFIGURE THE RDS OPTION GROUP

Since I already have the SSRS OPTION attached to my RDS OPTION GROUP, I will just need to update it. The steps would be almost the same if I was adding it for the first time.

IMPORTANT: It is important that the SAME SECURITY GROUP(s) that the RDS database server is running under, also be selected here.

Add a new OPTION GROUP or MODIFY an existing one as shown here.
The SECURITY GROUPS should be the same as for the RDS database.
Your configurations may be slightly different depending on your SMTP service.

The RDS DATABASE may go into MODIFY status until the changes are configured.

Step 5: Add SSRS Email Subscriptions

I’m assuming we all know how to add a subscription to an SSRS report. The steps are basically the same in AWS RDS SSRS as with any other instance of SSRS. The link below will demonstrate those steps as well as go into more detail regarding the steps I took to configure my AWS RDS SSRS server today.

https://aws.amazon.com/blogs/database/configuring-microsoft-sql-server-reporting-services-on-amazon-rds-for-sql-server/: AWS RDS SSRS Email Subscription Set up

You may also like