Cannot open database requested by the login. The login failed. how to solve this?

0

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

asked 8 months ago579 views
1 Answer
0

Apparently the problem is that the security group you attached to the database instance does not allow incoming traffic from your IP into MSSQL default port.

 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

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 }

You must add an ingress rule to allow your IP (preferably) to access port 1433 (DBPortNumber on your template).

Let me know if this does not solve your problem.

answered 8 months ago
  • Yes i agree on the answer , port 1433 should be default for MSSQL , allow the SG with that port inbound

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions