AWSTemplateFormatVersion: '2010-09-09'
Description: Creates an empty SQL Server Express RDS database as an example for automated deployments.
Parameters: 
  SqlServerInstanceName:
    NoEcho: 'false'
    Description: RDS SQL Server Instance Name
    Type: String
    Default: SqlRdsDB
    MinLength: '1'
    MaxLength: '63'
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*"
Metadata:
  AWS::CloudFormation::Interface:
    ParameterGroups:
    - Label:
        default: SQL Instance name, master username and password
      Parameters:
        - SqlServerInstanceName
        - DatabaseUsername
        - DatabasePassword
      ParameterLabels:
        SqlServerInstanceName:
            default: Instance name
Resources:
  ECSSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    DeletionPolicy: Delete
    Properties:
      GroupDescription: ECS Security Group
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '80'
        ToPort: '80'
        CidrIp: 0.0.0.0/0
  ECSAccessRDSSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    DeletionPolicy: Delete
    Properties:
      GroupDescription: ECS Security Group
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '1433'
        ToPort: '1433'
        SourceSecurityGroupId: !GetAtt ECSSecurityGroup.GroupId
  ECSFullAccessRole:
    Type: 'AWS::IAM::Role'
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
              - ecs-tasks.amazonaws.com
            Action:
              - 'sts:AssumeRole'
      Path: /
      RoleName: ECSTask-FullAccess
      Policies:
        - PolicyName: AdministratorAccess 
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action: '*'
                Resource: '*'  
  CycleStoreCreds:
    Type: 'AWS::SecretsManager::Secret'
    Properties:
      Name:  CycleStoreCredentials
      Description: "This secret has a static user id and password for cycle store db."
      SecretString: '{"username":"DBUser","password":"DBU$er2020"}'
      Tags:
        -
          Key: AppName
          Value: CycleStore
  RdsS3FullAccessRole:
    Type: 'AWS::IAM::Role'
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
              - rds.amazonaws.com
            Action:
              - 'sts:AssumeRole'
      Path: /
      RoleName: RDS-Sqlex--S3-FullAccess
      Policies:
        - PolicyName: AmazonS3FullAccess
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action: 's3:*'
                Resource: '*'
  SQLServerSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    DeletionPolicy: Delete
    Properties:
      GroupDescription: SQL Server Security Group
      SecurityGroupIngress:
      - IpProtocol: tcp
        FromPort: '1433'
        ToPort: '1433'        
        CidrIp: 0.0.0.0/0
  OptionGroup: 
    Type: AWS::RDS::OptionGroup
    Properties: 
      EngineName: sqlserver-ex
      MajorEngineVersion: "14.00"
      OptionConfigurations: 
        - 
          OptionName: SQLSERVER_BACKUP_RESTORE
          OptionSettings: 
            - 
              Name: IAM_ROLE_ARN
              Value: !GetAtt RdsS3FullAccessRole.Arn
      OptionGroupDescription: "rds-sql-ee-OptionGroup"  
  SQLDatabase:
    Type: AWS::RDS::DBInstance
    DeletionPolicy: Delete
    Properties:
      VPCSecurityGroups:
      - Fn::GetAtt:
        - SQLServerSecurityGroup
        - GroupId
      - Fn::GetAtt:
        - ECSAccessRDSSecurityGroup
        - GroupId
      DBInstanceIdentifier:
        Ref: SqlServerInstanceName
      LicenseModel: license-included
      Engine: sqlserver-ex
      EngineVersion: 14.00.3281.6.v1
      MultiAZ: false
      DBInstanceClass: db.t2.micro
      AllocatedStorage: '20'
      MasterUsername: DBUser
      MasterUserPassword: <change-here> #  Please use the password mentioned in the blog      
      PubliclyAccessible: 'true'
      OptionGroupName: !Ref OptionGroup
      Tags:
        -
          Key: "Name"
          Value: "SQlExpress Demo"
        -
          Key: "project"
          Value: "Legacy Migration"
      BackupRetentionPeriod: '1'
    DependsOn: SQLServerSecurityGroup
Outputs:
   SQLDatabaseEndpoint:
     Description: Database endpoint
     Value: !Sub "${SQLDatabase.Endpoint.Address}:${SQLDatabase.Endpoint.Port}"