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

質問済み 8ヶ月前596ビュー
1回答
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.

回答済み 8ヶ月前
  • Yes i agree on the answer , port 1433 should be default for MSSQL , allow the SG with that port inbound

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ