How to create RDS SQL server CloudFormation template?

Question

I am trying to create the RDS SQL server via CloudFormation template but i am facing the error. It's saying "No subnets found for the default VPC". 
Can anyone please help on this?

```
AWSTemplateFormatVersion: 2010-09-09
Description: CloudFormation template for deploying an app using Fargate with EBS storage.

Parameters:
  Image:
    Type: String
  ServiceName:
    Type: String
  ContainerPort:
    Type: Number
    Default: 7000
  AccessKeyId:
    Type: String
    Default: AKIAVHYFZXFR3GW6DVTK
  SecretAccessKey:
    Type: String
    Default: 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
  DefaultRegion:
    Type: String
    Default: us-east-1
  ServerName:
    Type: String
    Default: munlq-server
  DatabaseName:
    Type: String
    Default: munlqdatabase  
  AdminUsername:
    Type: String
    Default: musigma
  AdminPassword:
    Type: String
    Default: Crunchdata!
  lambdaFunctionName:
    Type: String
    AllowedPattern: '[a-zA-Z0-9]+[a-zA-Z0-9-]+[a-zA-Z0-9]+'
    Default: corenlpfunction123
  
  DBInputCIDR:
    Description: CIDR  to allow access to DB instances
    Type: String
    AllowedPattern: "(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})/(\d{1,2})"
    ConstraintDescription: must be a valid IP CIDR range of the form x.x.x.x/x.

DBPortNumber:
    Description: The port number on which the database accepts connections.
    Type: Number
    Default: '1433'
    MinValue: '1433'
    MaxValue: '1433'
    ConstraintDescription: 1150-65535 except for 1434, 3389, 47001, 49152, and 49152
    
  AllocatedStorage:
    Description: The allocated storage size, specified in gigabytes (GB).
    Type: String
    AllowedPattern: "^([2-9]?[0-9]{1}|[0-9]{3,4}|1[0-5]?[0-9]{3}|16[0-2]?[0-9]{2}|163[0-7]?[0-9]{1}|1638[0-4]?)$"
    ConstraintDescription: "Value must be a valid number between 20-16384."
  
  DBInstanceClass:
    Description: The name of the compute and memory capacity classes of the DB instance.
    Type: String
    Default: db.t3.small
    
  Engine:
    Description: The name of the database engine to be used for this instance.
    Type: String
    AllowedValues: [sqlserver-ee, sqlserver-se, sqlserver-ex, sqlserver-web]
    ConstraintDescription: "Please specify either a sqlserver-ee, sqlserver-se, sqlserver-ex, or sqlserver-web engine for the RDS instance."

MasterUsername:
    Description: The master user name for the DB instance.
    Type: String

MasterUserPassword:
    Description: The master password for the DB instance.
    Type: String
    NoEcho: true

Resources:
  MyVPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: 10.0.0.0/16
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: MyVPC

SubnetA:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref MyVPC
      CidrBlock: 10.0.0.0/24
      AvailabilityZone: us-east-1a
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Name
          Value: DefaultSubnetA

SubnetB:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref MyVPC
      CidrBlock: 10.0.1.0/24
      AvailabilityZone: us-east-1b
      Tags:
        - Key: Name
          Value: DefaultSubnetB

MyInternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: MyInternetGateway

MyInternetGatewayAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref MyVPC
      InternetGatewayId: !Ref MyInternetGateway

PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref MyVPC
      Tags:
        - Key: Name
          Value: PublicRouteTable

DefaultRoute:
    Type: AWS::EC2::Route
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref MyInternetGateway

SubnetARouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref SubnetA
      RouteTableId: !Ref PublicRouteTable

SubnetBRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      SubnetId: !Ref SubnetB
      RouteTableId: !Ref PublicRouteTable

MySecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: My Security Group
      VpcId: !Ref MyVPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 7000
          ToPort: 7000
          CidrIp: 0.0.0.0/0
      SecurityGroupEgress:
        - IpProtocol: '-1'
          FromPort: 0
          ToPort: 65535
          CidrIp: 0.0.0.0/0

DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnets available for the RDS DB Instance
      SubnetIds:
        - !Ref SubnetA
        - !Ref SubnetB

SGBaseIngress:
    Type: AWS::EC2::SecurityGroupIngress
    Properties:
      GroupId: !Ref MySecurityGroup
      IpProtocol: tcp
      FromPort: !Ref DBPortNumber
      ToPort: !Ref DBPortNumber
      CidrIp: !Ref DBInputCIDR
  MyDB:
    Type: "AWS::RDS::DBInstance"
    Properties:
      AllocatedStorage: !Ref AllocatedStorage
      AllowMajorVersionUpgrade: false
      AutoMinorVersionUpgrade: false
      BackupRetentionPeriod:  7
      CopyTagsToSnapshot: true
      DBInstanceClass: !Ref DBInstanceClass
      Engine: !Ref Engine
      #EngineVersion: "14.00.3192.2.v1"
      LicenseModel: license-included
      MasterUsername: !Ref MasterUsername
      MasterUserPassword: !Ref MasterUserPassword
      MultiAZ: false
      MonitoringInterval: 0 
      PubliclyAccessible: true 
      StorageType: gp2
      Tags:
        - Key: Name
          Value: !Sub
          - ${AWS::StackName}-${Name}
          - { Name: !Ref Engine }

lambdaFunction:
    Type: AWS::Lambda::Function
    Properties:
      Code:
        ImageUri: 360252553571.dkr.ecr.us-east-1.amazonaws.com/cloudops:lambda
      Description: Example Lambda function using Docker image
      FunctionName: !Ref lambdaFunctionName
      Role: !GetAtt lambdaIAMRole.Arn
      PackageType: Image
      Timeout: 300 # 5 minutes (timeout is in seconds)
      MemorySize: 1024 # 1024 MB

FunctionURL:
    Type: AWS::Lambda::Url
    Properties:
      TargetFunctionArn: !GetAtt lambdaFunction.Arn
      AuthType: AWS_IAM

lambdaIAMRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Action:
              - sts:AssumeRole
            Effect: Allow
            Principal:
              Service:
                - lambda.amazonaws.com
      Policies:
        - PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Action:
                  - logs:CreateLogGroup
                  - logs:CreateLogStream
                  - logs:PutLogEvents
                  - ecr:GetDownloadUrlForLayer
                  - ecr:BatchGetImage
                  - lambda:InvokeFunction
                  - sts:GetCallerIdentity
                Effect: Allow
                Resource:
                  - !Sub arn:aws:logs:${AWS::Region}:${AWS::AccountId}:log-group:/aws/lambda/${lambdaFunctionName}:*
          PolicyName: lambda

Cluster:
    Type: AWS::ECS::Cluster
    Properties:
      ClusterName: !Join ['', [!Ref ServiceName, Cluster]]

TaskDefinition:
    Type: AWS::ECS::TaskDefinition
    Properties:
      NetworkMode: awsvpc
      RequiresCompatibilities:
        - FARGATE
      Cpu: '4096'
      Memory: '16384'

ExecutionRoleArn: !GetAtt ExecutionRole.Arn
      TaskRoleArn: !GetAtt TaskRole.Arn
      ContainerDefinitions:
        - Name: !Ref ServiceName
          Image: !Ref Image
          PortMappings:
            - ContainerPort: !Ref ContainerPort
          Essential: true
          Environment:
            - Name: AWS_ACCESS_KEY_ID
              Value: !Ref AccessKeyId
            - Name: AWS_SECRET_ACCESS_KEY
              Value: !Ref SecretAccessKey
            - Name: AWS_DEFAULT_REGION
              Value: !Ref DefaultRegion
            - Name: SERVER_NAME
              Value: !Ref ServerName
            - Name: DATABASE_NAME
              Value: !Ref DatabaseName
            - Name: ADMIN_USERNAME
              Value: !Ref AdminUsername
            - Name: ADMIN_PASSWORD
              Value: !Ref AdminPassword
            - Name: LAMBDA_FUNCTION_NAME
              Value: !Ref lambdaFunctionName 
      
      EphemeralStorage:
        SizeInGiB: 150

ExecutionRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: muNLQExecutionRole
      AssumeRolePolicyDocument:
        Statement:
          - Effect: Allow
            Principal:
              Service: ecs-tasks.amazonaws.com
            Action: 'sts:AssumeRole'
      ManagedPolicyArns:
        - 'arn:aws:iam::aws:policy/service-role/AmazonECSTaskExecutionRolePolicy'

TaskRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: muNLQTaskRole
      AssumeRolePolicyDocument:
        Statement:
          - Effect: Allow
            Principal:
              Service: ecs-tasks.amazonaws.com
            Action: 'sts:AssumeRole'
  
  FargateService:
    Type: AWS::ECS::Service
    Properties:
      Cluster: !Ref Cluster
      LaunchType: FARGATE
      ServiceName: munlq-service
      DesiredCount: 1
      TaskDefinition: !Ref TaskDefinition
      LoadBalancers:
        - ContainerName: !Ref ServiceName
          ContainerPort: !Ref ContainerPort
          TargetGroupArn: !Ref TargetGroup
      NetworkConfiguration:
        AwsvpcConfiguration:
          AssignPublicIp: ENABLED
          SecurityGroups:
            - !Ref MySecurityGroup  # Corrected reference to MySecurityGroup

Subnets:
            - !Ref SubnetA
            - !Ref SubnetB
      DeploymentConfiguration:
        MaximumPercent: 200
        MinimumHealthyPercent: 100
        DeploymentCircuitBreaker:
          Enable: true
          Rollback: true

LoadBalancer:
    Type: AWS::ElasticLoadBalancingV2::LoadBalancer
    Properties:
      Name: !Join ['', [!Ref ServiceName, LoadBalancer]]
      Type: application
      Subnets:
        - !Ref SubnetA
        - !Ref SubnetB
      SecurityGroups:
        - !Ref MySecurityGroup

TargetGroup:
    Type: AWS::ElasticLoadBalancingV2::TargetGroup
    Properties:
      HealthCheckPath: /
      Name: !Join ['', [!Ref ServiceName, TargetGroup]]
      Port: !Ref ContainerPort
      Protocol: HTTP
      VpcId: !Ref MyVPC
      HealthCheckProtocol: HTTP
      TargetType: ip
      LoadBalancerArns:
        - !Ref LoadBalancer
      Matcher:
        HttpCode: '200,308,404'

Listener:
    Type: AWS::ElasticLoadBalancingV2::Listener
    Properties:
      DefaultActions:
        - Type: forward
          TargetGroupArn: !Ref TargetGroup
      LoadBalancerArn: !Ref LoadBalancer
      Port: 7000
      Protocol: HTTP

```

Accepted Answer

Hi,

You need to link your DBInstance with your VPC by adding something like the following to your `AWS::RDS::DBInstance `object:
(excerpt from my own working CFNs - adapt with your own resource names)

```
VPCSecurityGroups:
        - !GetAtt CdcRdsSecGroup.GroupId
DBSubnetGroupName: !Ref CdcRdsSubnetGroup
```

All details at https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-rds-dbinstance.html

If you don't do that your db is not attached to any VPC, hence your error message where it tries to link to default vpc, which is not good. Your config with own VPC (MyVPC) is much better

Best,

Didier

Answer

1. Go to VPC Console
2. Click on Subnets in left pane
3. Now see if you have subnets created in each availability zone.

You should see subnets in each availability zone, if not then you can create one for each.

Please comment here what you find, accordingly I can assist you.

Abhishek

How to create RDS SQL server CloudFormation template?

Relevant content