SQL Server Restoring DB .bak file from AWS S3 to AWS RDS, error 'The ciphertext refers to a customer master key that does not exist'

I'm trying to restoring a DB .bak file from AWS S3 to AWS SQL RDS, get an error message 'The ciphertext refers to a customer master key that does not exist, does not exist in this region, or you are not allowed to access.'

There are 2 .bak files, which is I get from others. One of them succeeds to be restored but the other one is failed.

I've tried to search on AWS support but there is no similar answers for the situation I'm facing.

For the failed one, I tried the following steps to restore it:

Using my local SQL server 2019 to restore the .bak file,and it succeed.
Backup the restored DB, to create a new .bak file, without changing any parameters, just keep it as system recomended default(no cipher, no other additional changes)
Upload the generated .bak file to S3 using 'aws s3 cp' cmd.
Using SQL management studio to connect to the AWS RDS, and execute the restore query:

use master
go

exec msdb.dbo.rds_restore_database
 @restore_db_name = 'myDBName',
 @s3_arn_to_restore_from = 'arn:aws:s3:::s3-my-temp-folder/myDBName.bak'
go

exec msdb.dbo.rds_task_status;

After executing the msdb.dbo.rds_task_status, the error is reported and with message 'The ciphertext refers to a customer master key that does not exist, does not exist in this region, or you are not allowed to access.'

Some testing results I have:

The other .bak file(we call it B), this B is able to be restore successfully onto RDS directly.
The both .bak file given by others are able to be restored locally on SQL server.
Both .bak files are under the same S3 path.
I can confirm that I didn't add any encryption options to .bak file.

just yesterday I faced this problem but I was able to solve it, but it is possibly due to problems with the encrypted key in the KMS service, if what you are trying to do is in two different accounts, it is necessary to carry out more configurations.

Verify that the IAM policy includes the following attributes:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": "arn:aws:s3:::bucket_name"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObjectAttributes",
        "s3:GetObject",
        "s3:PutObject",
        "s3:ListMultipartUploadParts",
        "s3:AbortMultipartUpload"
      ],
      "Resource": "arn:aws:s3:::bucket_name/*"
    }
  ]
}

Note: Replace arn:aws:s3:::bucket_name with the ARN of your S3 bucket.

Verify that the policy is correctly associated with the role given in the SQLSERVER_BACKUP_RESTORE option.

Verify that the SQLSERVER_BACKUP_RESTORE option is the option group associated with the DB instance:

S3 Bucket ARN S3 folder prefix (Optional)

For more information, see https://repost.aws/knowledge-center/native-backup-rds-sql-server

This error is commonly associated with cross account database restore.

Example:

Account A has an S3 bucket where the backup is stored. Account B has an RDS DB instance where the restore needs to be done. The error occurs when you have permission-related issues in an IAM role or policy associated with the option. Or, there is a permissions issue with the bucket policy associated with the S3 bucket in the cross account.

[2022-02-03 15:57:22.180] Aborted the task because of a task failure or a concurrent RESTORE_DB request. [2022-02-03 15:57:22.260] Task has been aborted

Verify that the IAM policy in Account B (the account where the DB instance that you will be restoring to is located) includes the following attributes:

{
  "Version": "2012-10-17",
  "Statement":
    [
      {
        "Effect": "Allow",
        "Action":
          [
            "s3:ListBucket",
            "s3:GetBucketLocation"
          ],
        "Resource": "arn:aws:s3:::name_of_bucket_present_in_Account_A"
      },
      {
        "Effect": "Allow",
        "Action":
          [
            "s3:GetObject",
            "s3:PutObject",
            "s3:ListMultipartUploadParts",
            "s3:AbortMultipartUpload"
          ],
        "Resource": "arn:aws:s3::: name_of_bucket_present_in_Account_A /*"
      },
      {
        "Action": [
          "kms:DescribeKey",
          "kms:GenerateDataKey",
          "kms:Decrypt",
          "kms:Encrypt"
          "kms:ReEncryptTo",
          "kms:ReEncryptFrom"
        ],
        "Effect": "Allow",
        "Resource": [
          "arn:aws: PUT THE NAME OF THE KEY HERE",
          "arn:aws:s3::: name_of_bucket_present_in_Account_A /*"
        ]
      }
    ]
}

Verify that the bucket policy associated with the S3 bucket in Account A includes the following attributes:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Permission to cross account",
      "Effect": "Allow",
      "Principal": {
        "AWS": [
          "arn:aws:iam::AWS-ACCOUNT-ID-OF-RDS:role/service-role/PUT-ROLE-NAME"   /*---- Change Details here
        ]
      },
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation"
             ],
      "Resource": [
        "arn:aws:s3:::PUT-BUCKET-NAME"   /*---- Change Details here
      ]
    },
    {
      "Sid": "Permission to cross account on object level",
      "Effect": "Allow",
      "Principal": {
        "AWS": [
          "arn:aws:iam::AWS-ACCOUNT-ID-OF-RDS:role/service-role/PUT-ROLE-NAME"   /*---- Change Details here
        ]
      },
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:ListMultipartUploadParts",
        "s3:AbortMultipartUpload"
      ],
      "Resource": [
        "arn:aws:s3::: PUT-BUCKET-NAME/*"  /*---- Change Details here
      ]
    }
  ]
}

All the above information, I took it from:

https://repost.aws/knowledge-center/rds-sql-server-fix-native-backup-restore

SQL Server Restoring DB .bak file from AWS S3 to AWS RDS, error 'The ciphertext refers to a customer master key that does not exist'

1条答案

相关问题

热门标签

最新问答