AWS RDS backup to S3 fails

Question

We have an option group attached to one of our SQL Server instances to backup to an S3 bucket. When we attempt to run a backup using the rds_backup_database stored procedure, we get the following errors:

[2021-03-18 20:20:22.260] Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup.
[2021-03-18 20:20:22.270] Task has been aborted
[2021-03-18 20:20:22.270] Access Denied

Everything I've read says that this means the IAM role used for the backup and restore option group doesn't have the proper permissions to the S3 bucket.

Yet, everything appears to be configured correctly. Here is the permissions configuration for that role.

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

As for the backup window, it's configured for a half hour just after midnight, so we can't be conflicting with that.

Another peculiarity is that our RDS instance resides in the region us-east-2a. I can't find any information on what us-east-2a is. You can't select it when you have the option to select regions. Our S3 bucket is in us-east-2, as it should be. Are the two regions the same, or is this mismatch the cause of our failure to perform backups?

Answer 1

us-east-2a looks like it is the AZ your database is located:

the region still is us-east-2

based on the DB Snapshot Export IAM Role example:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html#USER_ExportSnapshot.SetupIAMRole

you should declare arn:aws:s3:::your-s3-bucket in your last statement,

from:

{
  "Effect": "Allow",
  "Action": [
    "s3:GetObjectMetaData",
    "s3:GetObject",
    "s3:PutObject",
    "s3:ListMultipartUploadParts",
    "s3:AbortMultipartUpload"
  ],
  "Resource": [
    "arn:aws:s3:::sabmssqldevbackups/sabmssqldev/*"
  ]
}

to:

{
  "Effect": "Allow",
  "Action": [
    "s3:GetObjectMetaData",
    "s3:GetObject",
    "s3:PutObject",
    "s3:ListMultipartUploadParts",
    "s3:AbortMultipartUpload"
  ],
  "Resource": [
    "arn:aws:s3:::sabmssqldevbackups/sabmssqldev"
    "arn:aws:s3:::sabmssqldevbackups/sabmssqldev/*"
  ]
}

to verify that the IAM Role is working, you can expand AWS CLI section in:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html#USER_ExportSnapshot.Exporting

and start a aws rds start-export-task command to verify that everything is working:

aws rds start-export-task \
    --export-task-identifier my_snapshot_export \
    --source-arn arn:aws:rds:AWS_Region:123456789012:snapshot:snapshot_name \
    --s3-bucket-name my_export_bucket \
    --iam-role-arn iam_role \
    --kms-key-id master_key

Answer 2

This turned out to be a problem with the encryption key. Someone set up our db instance to use the default AWS-managed key. This creates a sticky permissions problem that can only be fixed by encrypting with a customer-managed key. Since you can't change encryption keys on an existing db instance, the only solution is to do the following:

• Create a new customer-managed key in the KMS console
• Take a snapshot of the existing db instance
• Restore that snapshot to a new instance
• Apply the customer-managed key as the encryption key
• Change all existing references to point to the new db instance
• Delete the original instance

Much more work than I hoped it would be.

Answer 3

I think you are trying to execute backup to s3 into the automated RDS backup window, so RDS will kill your native SQL backup/restore. The way to work around this issue is to never run native SQL backup/restore commands around your scheduled RDS backup window, or changing it to different time: