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:
# ...