Create RDS database with Cloud Formation

AWS Cloud Formation allows you to programmatically create a template that you can use to spin up multiple instances of an object (such as an RDS server) and repeat the process on multiple locations, accounts, etc. Today I am setting up an RDS instance that needs to be the same in three different AWS accounts. I have one for development (DEV), implementation (IMPL) and production (PROD).

This blog post assumes you have a basic understanding of how Cloud Formation works as well as basic familiarity with coding in YAML.

BEFORE STARTING MY Cloud Formation (CF) Template, I have the following already configured in my AWS Account.

• VPC
• IAM User Account
• MSAD (Microsoft Active Directory Services – Enterprise)
• SES (AWS Simple Email Service) This can be skipped or set up later if not needed.

I will deploy three CF Scripts to create the RESOURCES that I need to complete this. At first this may seem like a longer method than using the wizard to set up an RDS Server, but if you have more than two instances to create in multiple environments, I think this will save you time in the long run.

QUICK REVIEW OF CLOUD FORMATION

My templates have four sections:

• METADATA: This tells CF how to organize the initial input form.
• PARAMATERS: These are the values that will need to be supplied for each use.
• CONDITIONS: If not equal to this value, template will not run.
• RESOURCES: This is detail of each resource that CF will create, when run.

SHARED RESOURCES SCRIPT

My first CF Script will create these resources which may be shared among all RDS instances:

• KMS ENCRYPTION KEY (I advise against using the AWS default and will create my own.)
• RDS IAM ROLE
• S3 BUCKETS ( I will set up three buckets. Two for RDS and one for my CF scripts.)
• SECURITY GROUPS

AWSTemplateFormatVersion: 2010-09-09
Description: (June 2022 version) This template (database-shared-resources) sets up kms key, s3 buckets. All IPs are examples only and need updating.
Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
      - Label:
          default: Network configuration
        Parameters:
          - EnvironmentName
          - EnvironmentType
          - ThisVPC
          - CidrIp
          - CidrIp2
          - CidrIp3
    ParameterLabels:
      EnvironmentName:
        default: "Environment Name"
      EnviornmentType:
        default: "Enviornmetn Type"
      CidrIp:
        default: "IP range to grant to ports"
      ThisVPC:
        default: "VPC"
Parameters:
  EnvironmentName:
    Description: Environment Name.
    Default: mycompany
    Type: String
    AllowedValues: 
      - mycompany
  EnvironmentType:
    Description: Environment Type.
    Type: String
    AllowedValues:
      - prod
      - impl 
      - dev
    ConstraintDescription: must specify prod, impl or dev.
  ThisVPC:
    Description: VPC to operate in
    Type: AWS::EC2::VPC::Id
  CidrIp:
    Description: IP range to grant to ports (internal aws)
    Type: String
    Default: "10.2.7.0/23"
  CidrIp2:
    Description: IP range to grant to ports (workspaces public)
    Type: String
    Default: "18.2.7.0/23"
  CidrIp3:
    Description: IP range to grant to ports (workspaces adapter)
    Type: String
    Default: "198.2.7.0/23"
Conditions:
  isUsEastOne: !Equals
    - !Ref AWS::Region 
    - us-east-1
Resources:
  ## Create KMS Encryption Key to be used by RDS
  RsaDatabaseKey:
    Type: 'AWS::KMS::Key'
    Properties:      
      Description: Multi-Region primary key for use by rds
      MultiRegion: true
      EnableKeyRotation: true
      PendingWindowInDays: 10
      KeyPolicy:
        Version: 2012-10-17
        Id: !Sub ${EnvironmentName}-${EnvironmentType}-database-key-01
        Statement:
          - Sid: Enable IAM User Permissions
            Effect: Allow
            Principal:
              AWS: !Sub 'arn:aws:iam::${AWS::AccountId}:root'
            Action: 'kms:*'
            Resource: '*'
          # TODO MAY NEED TO COMMENT OUT -- until resource is created...... This will allow Rds to backup and restore.
          # MANUALLY ADD This configuration if commented out.
          # - Sid: Allow use of the key
          #   Effect: Allow
          #   Principal:
          #     AWS: !Sub 'arn:aws:iam::${AWS::AccountId}:role/${EnvironmentName}-${EnvironmentType}-database-rds-role'
          #   Action: 'kms:*'
          #   Resource: '*'
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${EnvironmentType}-database-key-01
  RsaDatabaseKeyAlias:
    Type: 'AWS::KMS::Alias'
    Properties:
      AliasName: !Sub alias/${EnvironmentName}-${EnvironmentType}-database-key-01
      TargetKeyId: !Ref RsaDatabaseKey
  ## Create S3 Buckets for rds to use
  DatabaseS3bucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketName: !Sub ${EnvironmentName}-${EnvironmentType}-database
      BucketEncryption:
        ServerSideEncryptionConfiguration:
          - ServerSideEncryptionByDefault:
              SSEAlgorithm: 'aws:kms'
              KMSMasterKeyID: !Ref RsaDatabaseKey
    DeletionPolicy: Retain
    UpdateReplacePolicy: Retain
  DatabaseAuditS3bucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketName: !Sub ${EnvironmentName}-${EnvironmentType}-database-audit
      BucketEncryption:
        ServerSideEncryptionConfiguration:
          - ServerSideEncryptionByDefault:
              SSEAlgorithm: 'aws:kms'
              KMSMasterKeyID: !Ref RsaDatabaseKey
    DeletionPolicy: Retain
    UpdateReplacePolicy: Retain
  ## Deny access to S3 bucket if connection is not secure.
  DatabaseAuditS3bucketPolicy:
    Type: AWS::S3::BucketPolicy
    Properties:
      Bucket: !Ref DatabaseAuditS3bucket
      PolicyDocument:
        Version: 2012-10-17
        Statement:
          - Action: 's3:*'
            Effect: Deny
            Principal: '*'
            Resource:
              - !Sub "arn:aws:s3:::${EnvironmentName}-${EnvironmentType}-database-audit"
              - !Sub "arn:aws:s3:::${EnvironmentName}-${EnvironmentType}-database-audit/*"
            Condition:
              Bool:
                'aws:SecureTransport': false
  ## Create a bucket to store this CF Script and future ones as well.
  CloudFormScriptsS3bucket:
    Type: AWS::S3::Bucket
    Properties:
      BucketName: !Sub ${EnvironmentName}-${EnvironmentType}-cloud-formation
      BucketEncryption:
        ServerSideEncryptionConfiguration:
          - ServerSideEncryptionByDefault:
              SSEAlgorithm: 'aws:kms'
              KMSMasterKeyID: !Ref RsaDatabaseKey
    DeletionPolicy: Retain
    UpdateReplacePolicy: Retain
  ## Create IAM Role for rds
  DatabaseRdsIamRole:
    Type: AWS::IAM::Role
    Properties: 
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - ec2.amazonaws.com
                - ssm.amazonaws.com
                - rds.amazonaws.com
                - ds.amazonaws.com
            Action:
              - 'sts:AssumeRole'
      Description: Role to provide rds with s3 access
      #Path: /if/required/
      #PermissionsBoundary: !Sub arn:aws:iam::${AWS::AccountId}:policy/if/required/boundary-policy
      Policies: 
        - PolicyName: !Sub ${EnvironmentName}-${EnvironmentType}-database-rds-access
          PolicyDocument:            
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action: 
                  - s3:ListAllMyBuckets
                  - s3:GetAccessPoint
                  - s3:GetAccountPublicAccessBlock
                  - s3:GetBucketLocation
                  - s3:ListAccessPoints
                  - s3:ListBucket
                  - s3:ListJobs
                  - s3:PutObject
                  - s3:PutObjectAcl
                Resource: '*'
              - Effect: Allow
                Action:
                  - ds:DescribeDirectories
                  - ds:AuthorizeApplication
                  - ds:UnauthorizeApplication
                  - ds:GetAuthorizedApplicationDetails
                Resource: '*'
              - Effect: Allow
                Action: 
                  - rds:*
                Resource: '*'
              - Effect: Allow
                Action:  kms:*
                Resource: !Ref RsaDatabaseKey
                # TODO If the above should fail, replace it with the example below, after looking up KMS Key arn, when rerunning the script.
                # Resource: !Sub 'arn:aws:kms:us-east-1:${AWS::AccountId}:key/mrk-1234567890abcdef'
              - Effect: Allow
                Action: 
                  - iam:AttachRolePolicy
                  - iam:CreateRole
                  - iam:CreateServiceLinkedRole
                  - iam:DeleteRolePolicy
                  - iam:DeleteRole
                  - iam:DeleteServiceLinkedRole
                  - iam:GetPolicy
                  - iam:GetPolicyVersion
                  - iam:GetRole
                  - iam:GetRolePolicy
                  - iam:GetServiceLinkedRoleDeletionStatus
                  - iam:ListAttachedRolePolicies
                  - iam:ListPolicies
                  - iam:ListRolePolicies
                  - iam:ListRoles
                  - iam:ListRoleTags
                  - iam:PassRole
                  - iam:PutRolePolicy
                  - iam:UpdateRole
                  - iam:UpdateRoleDescription
                Resource: '*'
                  #- "arn:aws:iam::*:role/aws-service-role/*"
              - Effect: Allow
                Action:
                  - s3:*
                Resource: 
                  - '*'
                  - !Sub 'arn:aws:s3:::${EnvironmentName}-${EnvironmentType}-database/*'
                  - !Sub 'arn:aws:s3:::${EnvironmentName}-${EnvironmentType}-database-audit/*'  
      RoleName: !Sub ${EnvironmentName}-${EnvironmentType}-database-rds-role
 ## Security Groups for Rds
  DBServerRdsSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Enable 1433 access from VPC for sql rds
      GroupName: !Sub ${EnvironmentName}-${EnvironmentType}-database-rds
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '1433'
        ToPort: '1433'
        CidrIp: !Ref CidrIp
        Description: Entire VPC  
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${EnvironmentType}-database-rds
      VpcId: !Ref ThisVPC
  DBServerSsrsSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Enable 8443 access from VPC for sql rds ssrs
      GroupName: !Sub ${EnvironmentName}-${EnvironmentType}-database-rds-ssrs
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '8443'
        ToPort: '8443'
        CidrIp: !Ref CidrIp
        Description: Entire VPC 
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${EnvironmentType}-database-rds-ssrs
      VpcId: !Ref ThisVPC 
  # Create security groups that will allow access to the RDP instance.
  MSSQLExternalSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Enable 1433 access from VPC for sql rds
      GroupName: !Sub ${EnvironmentName}-${EnvironmentType}-database-mssql-external
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '1433'
        ToPort: '1433'
        CidrIp: !Ref CidrIp
        Description: Instance 1 Internal East
      - IpProtocol: tcp
        FromPort: '1433'
        ToPort: '1433'
        CidrIp: !Ref CidrIp2
        Description: Instance 1 Workspaces Public East
      - IpProtocol: tcp
        FromPort: '1433'
        ToPort: '1433'
        CidrIp: !Ref CidrIp3
        Description: Instance 1 Workspaces adapter East
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${EnvironmentType}-database-mssql-external
      VpcId: !Ref ThisVPC
  # Create security group that PowerShell will use. (This is not used by RDS and may be skipped here.)
  PowerShellSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Enable 5985 access for remote PowerShell management
      GroupName: !Sub ${EnvironmentName}-${EnvironmentType}-powershell
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '5985'
        ToPort: '5985'
        CidrIp: !Ref CidrIp
        Description: Entire VPC 
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${EnvironmentType}-powershell
      VpcId: !Ref ThisVPC
  # Create security group that will use AWS Simple Email Service (SES) to send database mail.
  SesVpcSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Security group for Amazon SES SMTP VPC endpoint
      GroupName: !Sub ${EnvironmentName}-${EnvironmentType}-ses-vpc-endpoint-security-group
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '587'
        ToPort: '587'
        CidrIp: !Ref CidrIp
        Description: Entire VPC 
      - IpProtocol: tcp
        FromPort: '465'
        ToPort: '465'
        CidrIp: !Ref CidrIp
        Description: Entire VPC 
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${EnvironmentType}-ses-vpc-endpoint-security-group
      VpcId: !Ref ThisVPC  

  # COMMENT OUT BELOW: The below is an additional group that would be created if you plan on using SSRS on an EC2 instance.  
  SsrsSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Enable port access from VPC for ssrs web server(s)
      GroupName: !Sub ${EnvironmentName}-${EnvironmentType}-ssrs-web
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '80'
        ToPort: '80'
        CidrIp: !Ref CidrIp
        Description: Entire VPC 
      - IpProtocol: tcp
        FromPort: '80'
        ToPort: '80'
        CidrIp: !Ref CidrIp2
        Description: Workspace Public IP address 
      - IpProtocol: tcp
        FromPort: '80'
        ToPort: '80'
        CidrIp: !Ref CidrIp3
        Description: Workspace Adapter IP address 
      - IpProtocol: tcp
        FromPort: '443'
        ToPort: '443'
        CidrIp: !Ref CidrIp
        Description: Entire VPC
      - IpProtocol: tcp
        FromPort: '443'
        ToPort: '443'
        CidrIp: !Ref CidrIp2
        Description: Workspace Public IP address 
      - IpProtocol: tcp
        FromPort: '443'
        ToPort: '443'
        CidrIp: !Ref CidrIp3
        Description: Workspace Adapter IP address       
      Tags:
        - Key: Name
          Value: !Sub ${EnvironmentName}-${EnvironmentType}-ssrs-web
      VpcId: !Ref ThisVPC

Line 61: Creates a KMS Encruyption Key. It is important that I create my own and not use AWS default key since I plan on utilizing services such as SSRS and the new subscription email feature.
Line 88: Assigns an alias to the KMS Key.
Line 94: Creates three (3) S3 buckets for use by RDS, RDS Auditing, and to store my CF scripts.
Line 117: Defines the bucket policy to deny any connection that is not secure.
Line 146: Creates the RDS IAM ROLE that RDS will later require.
Line 164: Defines the Policies for the above role.
Line 230: Sets up security groups to allow connections to the RDS instance(s).
I’ve added a couple extra optional groups that I use for PowerShell and SQL Server on EC2.

I will upload this script to Cloud Formation as shown.

RDS SCRIPT PART A (Options)

TO create the RDS Server I will want to make sure I have my PARAMETER GROUP, OPTION GROUP and SUBNET GROUP all set up.
I will configure these PARAMETERS:

cost threshold for parallelism: 50
max degree of parallelism: 2
clr enabled: 1
max server memory (mb): 28672
database mail xps: 1
rds.force_ssl: 1

I will include these OPTIONS:

SQLSERVER_BACKUP_RESTORE: This will use teh IAM ROLE I created earlier.
SQLSERVER_AUDIT: This will use the S3 bucker I created previously.
SSIS
SSRS: To configure Subscription Email options, please see my post on that subject.

AWSTemplateFormatVersion: 2010-09-09
Description: This template (example-database-rds2019-msad-PartA) sets up kms key, s3 buckets, role, param group, option group and rds instance(s)
Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
      - Label:
          default: Network configuration
        Parameters:
          - ThisVPC
          - EnvironmentName
          - EnvironmentType
      - Label:
          default: Amazon EC2 configuration
        Parameters:
          - InstanceType
          - SqlServerInstance1Name
          - InstanceDeleteProtection
          - DBAllocatedStorage
          - DBAllocatedStorageMax
          - DBbIops
          - InstanceMultiAZ
          - RdsRsaKey
      - Label:
          default: Active Directory configuration
        Parameters:
          - MSDirectoryService
          - SubnetList
          - VPCSecurityGroups
          - VpcSsrsOptionSecurityGroup
      - Label:
          default: SQL Server RDS configuration
        Parameters:
          - SqlVersion
          - DBAdminUser
          - DBAdminPassword
          - BackupRetentionDays  
          - DatabaseS3bucket
          - DatabaseAuditS3bucket    
    ParameterLabels:
      ThisVPC:
        default: "VPC"
      EnvironmentName:
        default: "Environment Name"
      EnviornmentType:
        default: "Enviornment Type"
      InstanceType:
        default: "Instance type size"
      SqlServerInstance1Name:
        default: "Enter a name for RDS server"
      InstanceDeleteProtection:
        default: "Protect the instance from being deleted"
      DBAllocatedStorage:
        default: "Initial storage (GiB)"
      DBAllocatedStorageMax:
        default: "Maximum allowed storage (GiB)"
      DBbIops:
        default: "The number of I/O operations per second (IOPS) that the database provisions"
      InstanceMultiAZ:
        default: "Deploy in multiple availability zones"
      RdsRsaKey:
        default: "Rsa encryption key"
      MSDirectoryService:
        default: "Directory Services"
      SubnetList:
        default: "Select at least two (2) subnets for Multi-AZ"
      VPCSecurityGroups:
        default: "Attach these security groups to allow access"
      VpcSsrsOptionSecurityGroup:
        default: "Security group configured for Rds Options in Ssrs"
      SqlVersion:
        default: "Versions: (14 = SQL 2017 | 15 = SQL 2019)"
      DBAdminUser:
        default: "The default user of --admin-- should be used unless a change  is required"
      DBAdminPassword:
        default: "Enter a password (8-40 characters, alpha numeric, at least one capital letter and one number)"
      BackupRetentionDays:  
        default: "Enter numberof days to retain snapshot"
      DatabaseS3bucket:
        default: "Bucket to access for storing backups"
      DatabaseAuditS3bucket:
        default: "Bucket to access for storing sql audit logs"
Parameters:
  ThisVPC:
    Description: VPC to operate in
    Type: AWS::EC2::VPC::Id
  EnvironmentName:
    Description: Environment Name.
    Default: mycompany
    Type: String
    AllowedValues: 
      - mycompany
  EnvironmentType:
    Description: Environment Type.
    Type: String
    AllowedValues:
      - prod
      - impl 
      - dev
    ConstraintDescription: must specify prod, impl or dev.
  DBAdminUser:
    NoEcho: 'false'
    Default: 'admin'
    Description: Database admin account username
    Type: String
    MinLength: '1'
    MaxLength: '16'
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
    ConstraintDescription: must begin with a letter and contain only alphanumeric characters.
  DBAdminPassword:
    NoEcho: 'true'
    Description: Admin password
    Type: String
    MinLength: '8'
    MaxLength: '41'
    AllowedPattern: "^(?=.*[0-9])(?=.*[a-zA-Z])([a-zA-Z0-9]+)"
    ConstraintDescription: Must contain only alphanumeric characters with at least one capital letter and one number.
  SqlServerInstance1Name:
    Description: RDS SQL Server Instance 1 Name (alpha,numberic,-,_)
    Type: String
    MinLength: '1'
    MaxLength: '63'
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
    ConstraintDescription: Must contain only alphanumeric characters.
  InstanceMultiAZ:
    Description: Deploy into multiple availability zones.
    Type: String
    AllowedValues:
      - true
      - false
    ConstraintDescription: Select true for Multi-AZ, else false.
  InstanceDeleteProtection:
    Description: Do NOT allow deletion of the instance.
    Type: String
    Default: false
    AllowedValues:
      - true
      - false
  InstanceType:
    Description: Instance type (cpu:2-4-8| ram:16-32-64)
    Type: String
    AllowedValues:
      - db.r5.xlarge
      - db.r5.2xlarge
      - db.r5.4xlarge
      - db.r5.8xlarge
  DBAllocatedStorage:
    Default: '500'
    Description: The size of the database (GiB)
    Type: Number
    MinValue: '20'
    MaxValue: '65536'
    ConstraintDescription: must be between 20 and 65536 GiB.
  DBAllocatedStorageMax:
    Default: '1000'
    Description: The max size of the database (GiB) may grow.
    Type: Number
    MinValue: '20'
    MaxValue: '65536'
    ConstraintDescription: must be between 20 and 65536 GiB.
  DBbIops:
    Default: '1000'
    Description: The Iops
    Type: Number
  SqlVersion:
    Description: Intall version 14 (SQL 2017) or 15 (SQL 2019)
    Type: String
    Default: 15.00.4073.23.v1
    AllowedValues:
      - 14.00.3381.3.v1
      - 15.00.4073.23.v1
  SubnetList:
    Description: Subnet IDs for Rds to Use
    Type: "List<AWS::EC2::Subnet::Id>"
  VPCSecurityGroups:
    Description: Security Groups to allow Rds
    Type: "List<AWS::EC2::SecurityGroup::Id>"
  VpcSsrsOptionSecurityGroup:
    Description: Security Group for SSRS
    Type: "List<AWS::EC2::SecurityGroup::Id>"
  MSDirectoryService:
    Description: "MS AD"
    Type: String
    AllowedValues:
    # TODO Update with current SAD accounts
      - "d-9009009a1"
      - "d-9009009b2"
      - "d-9009009c3"
    ConstraintDescription: Select MS AD for this enviornment.
  BackupRetentionDays:
    Default: '7'
    Description: Number of daily snapshots to retain.
    Type: Number
    MinValue: '1'
    MaxValue: '35'
    ConstraintDescription: must be between 1 and 35.
  RdsRsaKey:
    Description: RSA Encryption Key
    Type:  String
    AllowedValues:
    # TODO Update these values with current  KMS ARN from environemnts
      - "arn:aws:kms:us-east-1:111111111111:key/mrk-1234567890abcdef1234567890abcdef"
      - "arn:aws:kms:us-east-1:222222222222:key/mrk-98765432109876543210987654321098"
      - "arn:aws:kms:us-east-1:333333333333:key/mrk-abcdef12345678987654321fedcba1"
    ConstraintDescription: Select RSA Key.
  DatabaseS3bucket:
    Description: S3 bucket access for rds backup files
    Type:  String
    AllowedValues:
      - "mycompany-dev-database"
      - "mycompany-impl-database"
      - "mycompany-prod-database"
    ConstraintDescription: Select S3 bucket for backups.
  DatabaseAuditS3bucket:
    Description: S3 bucket to store sql audit logs
    Type:  String
    AllowedValues:
      - "mycompany-dev-database-audit"
      - "mycompany-impl-database-audit"
      - "mycompany-prod-database-audit"
    ConstraintDescription: Select S3 bucket for audit.
Conditions:
  isUsEastOne: !Equals
    - !Ref AWS::Region 
    - us-east-1
Resources:
  ## Rds Patamater Group for Sql EE | Naming for this is automated ie:  StackName+RandomGUID
  rdsdbparametergroup15:
    Type: 'AWS::RDS::DBParameterGroup'
    Properties:
      Description: Sql Server 2019 ee custom paramaters
      Family: sqlserver-ee-15.0
      Parameters:        
        cost threshold for parallelism: 50
        max degree of parallelism: 2
        clr enabled: 1
        max server memory (mb): 28672
        database mail xps: 1
        rds.force_ssl: 1
## Option Group Customization for Rds SSIS and SSRS
  RdsOptionGroupSqlEe15:
    Type: AWS::RDS::OptionGroup
    Properties:
      EngineName: sqlserver-ee
      MajorEngineVersion: '15.00'
      OptionGroupDescription: OptionGroup for sql server 2019 ee with ssrs, ssis, backup, audit
      OptionConfigurations:
        - OptionName: SQLSERVER_BACKUP_RESTORE
          OptionSettings:
            - Name: IAM_ROLE_ARN
              Value: !Sub arn:aws:iam::${AWS::AccountId}:role/${EnvironmentName}-${EnvironmentType}-database-rds-role
              #Value: !Sub arn:aws:iam::${AWS::AccountId}:role/PermissionsBoundary/IfNeeded/${EnvironmentName}-${EnvironmentType}-database-rds-role
        - OptionName: SSIS
        - OptionName: SSRS
          OptionSettings:
            - Name: MAX_MEMORY
              Value: 25
          Port: 8443
          VpcSecurityGroupMemberships: !Ref VpcSsrsOptionSecurityGroup
        - OptionName: SQLSERVER_AUDIT
          OptionSettings:
            - Name: S3_BUCKET_ARN
              Value: !Sub arn:aws:s3:::${EnvironmentName}-${EnvironmentType}-database-audit
            - Name: IAM_ROLE_ARN
              Value: !Sub arn:aws:iam::${AWS::AccountId}:role/${EnvironmentName}-${EnvironmentType}-database-rds-role
              #Value: !Sub arn:aws:iam::${AWS::AccountId}:role/PermissionsBoundary/IfNeeded/${EnvironmentName}-${EnvironmentType}-database-rds-role
      Tags:
        - Key: Name
          Value: RdsOptionGroupSqlEe15ssrs
  # Subnet Group is used to create the MULTI-AZ instance. Two or more subnets must be selected in the SubnetList this references from PARAMETERS.
  DBRdsSubnetGroup: 
    Properties: 
      DBSubnetGroupDescription: Rds data tier subnet group
      SubnetIds: !Ref SubnetList
      Tags: 
        - 
          Key: Name
          Value: !Sub ${EnvironmentName}-rds-private-subnets
    Type: "AWS::RDS::DBSubnetGroup"

After I’ve successfully run the script I will check to make sure my groups were created and write down the ARN numbers to use in the next template.

RDS SCRIPT PART B (SQL Server)

My script will create the RDS database instance. If you are thinking that PART A and PART B could be combined, you are correct. I have split it up here, because this gives flexibility so that I can delete, modify, and add other RDS instances without affecting the other option resources.

AWSTemplateFormatVersion: 2010-09-09
Description: This template (example-database-rds2019-msad-partB) sets up kms key, s3 buckets, role, param group, option group and rds instance(s)
Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
      - Label:
          default: Network configuration
        Parameters:
          - ThisVPC
          - EnvironmentName
          - EnvironmentType
      - Label:
          default: Amazon EC2 configuration
        Parameters:
          - InstanceType
          - SqlServerInstance1Name
          - InstanceDeleteProtection
          - DBAllocatedStorage
          - DBAllocatedStorageMax
          - DBbIops
          - InstanceMultiAZ
          - RdsRsaKey
      - Label:
          default: Active Directory configuration
        Parameters:
          - MSDirectoryService
          - VPCSecurityGroups
          - VpcSsrsOptionSecurityGroup
      - Label:
          default: SQL Server RDS configuration
        Parameters:
          - SqlVersion
          - DBAdminUser
          - DBAdminPassword
          - BackupRetentionDays  
          - DatabaseS3bucket
          - DatabaseAuditS3bucket    
    ParameterLabels:
      ThisVPC:
        default: "VPC"
      EnvironmentName:
        default: "Environment Name"
      EnviornmentType:
        default: "Enviornment Type"
      InstanceType:
        default: "Instance type size"
      SqlServerInstance1Name:
        default: "Enter a name for RDS server"
      InstanceDeleteProtection:
        default: "Protect the instance from being deleted"
      DBAllocatedStorage:
        default: "Initial storage (GiB)"
      DBAllocatedStorageMax:
        default: "Maximum allowed storage (GiB)"
      DBbIops:
        default: "The number of I/O operations per second (IOPS) that the database provisions"
      InstanceMultiAZ:
        default: "Deploy in multiple availability zones"
      RdsRsaKey:
        default: "Rsa encryption key"
      MSDirectoryService:
        default: "Directory Services"
      VPCSecurityGroups:
        default: "Attach these security groups to allow access"
      VpcSsrsOptionSecurityGroup:
        default: "Security group configured for Rds Options in Ssrs"
      SqlVersion:
        default: "Versions: (14 = SQL 2017 | 15 = SQL 2019)"
      DBAdminUser:
        default: "The default user of --admin-- should be used unless a change  is required"
      DBAdminPassword:
        default: "Enter a password (8-40 characters, alpha numeric, at least one capital letter and one number)"
      BackupRetentionDays:  
        default: "Enter numberof days to retain snapshot"
      DatabaseS3bucket:
        default: "Bucket to access for storing backups"
      DatabaseAuditS3bucket:
        default: "Bucket to access for storing sql audit logs"
Parameters:
  ThisVPC:
    Description: VPC to operate in
    Type: AWS::EC2::VPC::Id
  EnvironmentName:
    Description: Environment Name.
    Default: mycompany
    Type: String
    AllowedValues: 
      - mycompany
  EnvironmentType:
    Description: Environment Type.
    Type: String
    AllowedValues:
      - prod
      - impl 
      - dev
    ConstraintDescription: must specify prod, impl or dev.
  DBAdminUser:
    NoEcho: 'false'
    Default: 'admin'
    Description: Database admin account username
    Type: String
    MinLength: '1'
    MaxLength: '16'
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
    ConstraintDescription: must begin with a letter and contain only alphanumeric characters.
  DBAdminPassword:
    NoEcho: 'true'
    Description: Admin password
    Type: String
    MinLength: '8'
    MaxLength: '41'
    AllowedPattern: "^(?=.*[0-9])(?=.*[a-zA-Z])([a-zA-Z0-9]+)"
    ConstraintDescription: Must contain only alphanumeric characters with at least one capital letter and one number.
  SqlServerInstance1Name:
    Description: RDS SQL Server Instance 1 Name (alpha,numberic,-,_)
    Type: String
    MinLength: '1'
    MaxLength: '63'
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
    ConstraintDescription: Must contain only alphanumeric characters.
  InstanceMultiAZ:
    Description: Deploy into multiple availability zones.
    Type: String
    AllowedValues:
      - true
      - false
    ConstraintDescription: Select true for Multi-AZ, else false.
  InstanceDeleteProtection:
    Description: Do NOT allow deletion of the instance.
    Type: String
    Default: false
    AllowedValues:
      - true
      - false
  InstanceType:
    Description: Instance type (cpu:2-4-8| ram:16-32-64-128)
    Type: String
    AllowedValues:
      - db.r5.xlarge
      - db.r5.2xlarge
      - db.r5.4xlarge
      - db.r5.8xlarge
  DBAllocatedStorage:
    Default: '500'
    Description: The size of the database (GiB)
    Type: Number
    MinValue: '20'
    MaxValue: '65536'
    ConstraintDescription: must be between 20 and 65536 GiB.
  DBAllocatedStorageMax:
    Default: '1000'
    Description: The max size of the database (GiB) may grow.
    Type: Number
    MinValue: '20'
    MaxValue: '65536'
    ConstraintDescription: must be between 20 and 65536 GiB.
  DBbIops:
    Default: '1000'
    Description: The Iops
    Type: Number
  SqlVersion:
    Description: Intall version 14 (SQL 2017) or 15 (SQL 2019)
    Type: String
    Default: 15.00.4073.23.v1
    AllowedValues:
      - 14.00.3381.3.v1
      - 15.00.4073.23.v1
  VPCSecurityGroups:
    Description: Security Groups to allow Rds
    Type: "List<AWS::EC2::SecurityGroup::Id>"
  VpcSsrsOptionSecurityGroup:
    Description: Security Group for SSRS
    Type: "List<AWS::EC2::SecurityGroup::Id>"
  MSDirectoryService:
    Description: "MS AD"
    Type: String
    AllowedValues:
      # TODO Update with current SAD accounts
      - "d-9009009a1"
      - "d-9009009b2"
      - "d-9009009c3"
    ConstraintDescription: Select MS AD for this enviornment.
  BackupRetentionDays:
    Default: '7'
    Description: Number of daily snapshots to retain.
    Type: Number
    MinValue: '1'
    MaxValue: '35'
    ConstraintDescription: must be between 1 and 35.
  RdsRsaKey:
    Description: RSA Encryption Key
    Type:  String
    AllowedValues:
       # TODO Update these values with current  KMS ARN from environemnts
      - "arn:aws:kms:us-east-1:111111111111:key/mrk-1234567890abcdef1234567890abcdef"
      - "arn:aws:kms:us-east-1:222222222222:key/mrk-98765432109876543210987654321098"
      - "arn:aws:kms:us-east-1:333333333333:key/mrk-abcdef12345678987654321fedcba1"
    ConstraintDescription: Select RSA Key.
  DatabaseS3bucket:
    Description: S3 bucket access for rds backup files
    Type:  String
    AllowedValues:
      - "mycompany-dev-database"
      - "mycompany-impl-database"
      - "mycompany-prod-database"
    ConstraintDescription: Select S3 bucket for backups.
  DatabaseAuditS3bucket:
    Description: S3 bucket to store sql audit logs
    Type:  String
    AllowedValues:
      - "mycompany-dev-database-audit"
      - "mycompany-impl-database-audit"
      - "mycompany-prod-database-audit"
    ConstraintDescription: Select S3 bucket for audit.
Conditions:
  isUsEastOne: !Equals
    - !Ref AWS::Region 
    - us-east-1
Resources:
# Create the RDS Instance 
  DBInstanceWithAD:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: !Ref DBAllocatedStorage
      AllowMajorVersionUpgrade: 'false'
      AutoMinorVersionUpgrade: 'true'
      BackupRetentionPeriod: !Ref BackupRetentionDays
      DBInstanceClass: !Ref InstanceType
      DBInstanceIdentifier: !Ref SqlServerInstance1Name
      # Change this (below) accordingly .... 
      DBSubnetGroupName: mycompany-database-rds2019a-dbrdssubnetgroup-987654
      # Change this (below) accordingly .... 
      DBParameterGroupName: mycompany-database-rds2019a-rdsdbparametergroup15-1123abcd
      DeletionProtection: !Ref InstanceDeleteProtection   
      Domain: !Ref MSDirectoryService
      DomainIAMRoleName: !Sub ${EnvironmentName}-${EnvironmentType}-database-rds-role
      #DomainIAMRoleName: !Sub PermissionsBoundary/IfNeeded/${EnvironmentName}-${EnvironmentType}-database-rds-role
      EnableCloudwatchLogsExports: 
        - error
        - agent
      Engine: sqlserver-ee
      EngineVersion: !Ref SqlVersion
      Iops: !Ref DBbIops
      KmsKeyId: !Ref RdsRsaKey
      LicenseModel: license-included      
      MasterUsername: !Ref DBAdminUser
      MasterUserPassword: !Ref DBAdminPassword
      MaxAllocatedStorage: !Ref DBAllocatedStorageMax
      MultiAZ: !Ref InstanceMultiAZ
      # Change this (below) accordingly .... 
      OptionGroupName: mycompany-database-rds2019a-rdsoptiongroupsqlee15-zxyasd456
      PubliclyAccessible: 'false'
      PreferredBackupWindow: 01:00-02:00
      PreferredMaintenanceWindow: sun:04:00-sun:05:00
      StorageEncrypted: 'true'
      StorageType: io1      
      VPCSecurityGroups: !Ref VPCSecurityGroups      
    DeletionPolicy: Snapshot

My CF Form should look similar to this:

I proceeded through the steps until the RDS Server is set up.

Depending on my configuration, it may take hours before the status updates to AVAILABLE.

Once I get that green indicator, I open up SSMS on a workspace and verify that I can log in.