Hero Image

Automatically initialize a new database when using AWS RDS for Microsoft SQL Server and ECS


Problem

When using AWS RDS for Microsoft SQL Server, you have to create manually a database and database user in your RDS instance either by using the command line or a tool like Microsoft SQL Management Studio. In a locked down AWS account without any allowed remote access, it is not possible to add a custom database and/or database user in this way. Instead, you have to rely upon the default master database and the master user account. For security reasons, using the default database credentials and master database should be avoided.

Solution

For this exact problem, you can use the Docker image dreitier/mssql-init-db-env (GitHub source). It can be used as a sidecar container to create a new database, login, user and assign the required SQL Server roles in a Microsoft SQL Server instance. This solution is easier to use and debug as having any AWS Lambda-backed custom resource.

With AWS Elastic Container Service (ECS), you can use the sidecar container as a dependency. With every deployment it is ensured, that the database, database login and database user exist and the required roles for the database user are assigned:

  MyTaskDefinition
    Type: AWS::ECS::TaskDefinition
    Properties:
      ContainerDefinitions:
      - Name: is-mssql-env-initialized
        Environment:
        - Name: DATABASE_HOST
          Value: !Sub '${MssqlServerInstance.Endpoint.Address}'
        # you probably want to pull the credentials through SSM and rotate the master username and password
        - Name: MASTER_USERNAME
          Value: !Ref RdsMasterUsername
        - Name: MASTER_PASSWORD
          Value: !Ref RdsMasterPassword
        - Name: DATABASE_USER
          Value: !Ref RdsApplicationUsername
        - Name: DATABASE_LOGIN
          Value: !Ref RdsApplicationUsername
        - Name: DATABASE_PASSWORD
          Value: !Ref RdsApplicationPassword
        - Name: DATABASE_NAME
          Value: !Ref RdsApplicationDatabaseName
        - Name: DATABASE_ROLES
          Value: db_owner
        # in a locked down environment, you might want to push the image in your account's ECR
        Image: docker.io/dreitier/mssql-init-db-env:latest
      - Name: application
        DependsOn:
        - ContainerName: is-mssql-env-initialized
          # only start the application container if the MSSQL environment has been successfully initialized
          Condition: COMPLETE
        Environment:
        # ...