We have created RDS MIcrosoft SQL Server. But when i try to connect to the database via VS Code's MSSQL extension it's throwing below error :
mssql: Error 4060: Cannot open database "database-1" requested by the login. The login failed.
Can anyone please give me the solution?
I am attaching my template below for reference :
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: ASIAVHYFZXFRUKOOHLQL
SecretAccessKey:
Type: String
# Default: 9bLaXe6CDWP6BmUzAtl99QFo1yVGHmebTbl47U/j
SessionToken:
Type: String
# Default: IQoJb3JpZ2luX2VjEPf//////////wEaCXVzLWVhc3QtMSJHMEUCIDNlIu9df787x+rMF6fNQTlOVCwBqJ5p9oPJv6r0D9pcAiEA4MgxjvRukMTt1Vb8YXZ9WL30KtEK3Zryy7g5pHvs6A8q+AEIwP//////////ARABGgwzNjAyNTI1NTM1NzEiDLeidc4xPFnjlDNy7irMAZoNTH5SR0hVp15dIjhP6B1MpEUncEscz0Bj3tXfQJ9ZR4Y3h4+WcU22KvhshzEhFOW1RSSAdwKTzefEvVr2+kEawQfDtpNoQ9RMeWOk5bICDZZ+HfkVh1aPZxs0YErmmuyctfodUGrfHDqlNz67F/bCo3gxbrMt8kwDIBav2yqVKow9F1wN3LPuJfY699NUNakQPiWUw53a/DlCv8bHHYpEvc/LVTqvszz9SbzhhhZ99k6RqDmU2Wq7DWwPTJbLPArM4IZrhLvK7619oTCBo72nBjqYAQ7i8iV0t8pmK+WzsBgcXRwvqqNM+qw2Jx+XnyO9q8bEQGYkq/Zo1CxtNPR1LqrXlblBhnkUS5zI0t7RMn1dzb92EliF49RjhkWh7DYdfCLfk3giiNdQI65zoQlFg6xBeqanhfIPSHr6R+1vscDeXDo6F3QT5mizDR8h1i37kbFh2guJeZNb9nveYKt4piVIepfLbZHp2+1q
DefaultRegion:
Type: String
Default: us-east-1
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
MapPublicIpOnLaunch: true
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:
VPCSecurityGroups: # Add this section to associate security groups
- !GetAtt MySecurityGroup.GroupId
DBSubnetGroupName: !Ref DBSubnetGroup
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
DBInstanceIdentifier: "cloudsql" # Hardcode your desired database name here
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_SESSION_TOKEN
Value: !Ref SessionToken
- 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: /nlq
Name: !Join ['', [!Ref ServiceName, TargetGroup]]
Port: !Ref ContainerPort
Protocol: HTTP
VpcId: !Ref MyVPC
HealthCheckProtocol: HTTP
TargetType: ip
LoadBalancerArns:
- !Ref LoadBalancer
Matcher:
HttpCode: '200,308'
Listener:
Type: AWS::ElasticLoadBalancingV2::Listener
Properties:
DefaultActions:
- Type: forward
TargetGroupArn: !Ref TargetGroup
LoadBalancerArn: !Ref LoadBalancer
Port: 7000
Protocol: HTTP
Outputs:
LoadBalancerURL:
Description: The URL of the load balancer
Value: !Sub http://${LoadBalancer.DNSName}:7000/nlq
Yes i agree on the answer , port 1433 should be default for MSSQL , allow the SG with that port inbound