AWS add option group

Question

I asked this on the AWS forum but not getting much traction. My root problem is that I'm trying to restore a MS SQL Server RDS database and getting the error message "Database backup/restore option is not enabled yet or is in the process of being enabled. Please try again later." I found this thread about this problem:

https://forums.aws.amazon.com/message.jspa?messageID=736361

Which suggests to add an option to the database's option group. However, the "Add Option" button on the option group is disabled, and the drop-down to select an option group for the database is also disabled. So I can't add an option to the option group, and I can't make a new option group and choose that. I learned that you cannot add options to a default option group, so I definitely need to create an option group. I still don't know why I can't change the option group of an existing RDS instance.

So I made a new option group, no problem. I made a brand new RDS instance so I could pick a different option group. On the new option group I created I can click Add Option, but can't successfully complete the process. I created an IAM role with the RDS full access permission, and when attempting to add the backup/restore option with that role to the option group, I get this:

"IAM role ARN value is invalid or does not include the required permissions for: SQLSERVER_BACKUP_RESTORE (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: 3824d081-648a-11e6-9c94-87e171d2e1f8)"

I don't know a lot about IAM so I'm not sure what to do with this. Ideas?

UPDATE

I got another response on the AWS forum. Here's the suggestion and my results.

Please try the following steps -

1. Select Option Groups from the left hand side panel in RDS Console
2. Select the Option group you created and click on the Add Option button
3. Select SQL_SERVER_BACKUP_RESTORE in the Option drop-down.
4. Click the text Create a New Role under the IAM Role drop-down. This will show you options for IAM Role creation.
5. Add a name for the IAM Role, select the S3 bucket you want to use and check Yes for the Apply Immediately radio button.
6. Click the Add Option button.

Reply

That sounded good! But after I click Add Option at the bottom of the form, the button disabled for a few seconds and then the IAM Role Name field was surrounded by red. I've attached a screenshot. Clicking on the exclamation mark or anywhere in the red doesn't bring up any additional information. I've tried a couple of different names with the same result. Are there rules about the name that the page doesn't specify? Thanks again for your help.

Solution 1

I signed up for paid support and here's the answer, in case it helps anyone else. I had to edit the trust policy on the IAM role like so:

{
    "Version": "2012-10-17",
    "Statement":
    [{
        "Effect": "Allow",
        "Principal": {"Service":  "rds.amazonaws.com"},
        "Action": "sts:AssumeRole"
    }]
}

Then I was able to add the option to the option group and the restore worked. Note that the trust policy is called "trust relationship" on the web console.

Solution 2

Below is the policy for a role with the permissions that should work:

 {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1472672338000",
      "Effect": "Allow",
      "Action": [
        "rds:*"
      ],
      "Resource": [
        "arn:aws:rds:us-west-2:123456789012:snapshot:ms-sql-rds-final-snapshot"
      ]
    }
  ]
}

Comments

[EEAA]

What did AWS support say about this? If you don't have a support plan, just pay for a month and then cancel after you get your question answered. It's well worth the cost.

[nasch]

I will suggest that to my boss if I don't get an answer another way.

[Jonah Benton]

Don't enter a name in that field. Try the IAM Role select box instead,

[nasch]

When I do that I get the same error as before: IAM role ARN value is invalid or does not include the required permissions for: SQLSERVER_BACKUP_RESTORE (Service: AmazonRDS; Status Code: 400; Error Code: InvalidParameterValue; Request ID: ab04cad7-6df2-11e6-bae8-737d9f5a0a02)

[Jonah Benton]

It looks like the IAM role has to have permissions for the s3 bucket from which the restore will occur, not just permissions on the database.

[nasch]

On S3 I have granted list and upload/delete to "Any Authenticated AWS User". Do I need more than that? Do I need a bucket policy?

[Jonah Benton]

It looks like it is associated with the role, not with the bucket. The role has to have bucket access enabled. Try these steps: ramblingsofraju.com/sql-server/…

[nasch]

The instructions were a little terse but assuming "Created Custom Options Group and enabled to use the NativeBackupRestore Role" means adding an option to the option group (I don't see what else it would be) this results in the same error. I added the policy "AmazonS3FullAccess" and "AmazonRDSFullAccess" to the role. I didn't see anything else that looked like "S3 Bucket Access". The full access policy is described as "Provides full access to all buckets via the AWS Management Console" so that should do it.

[nasch]

I don't understand. By "my IAM user" do you mean the credentials I'm using to log in to the dashboard? If so, I'm using the root credentials and I can create roles just fine. Do you mean the IAM role that I'm trying to put on the option? If so, why does that role need to create other roles? Can you point me to where I would need to save this policy to get this working? Do I need to create a custom policy and then set it on the role I created?

[Anton Zorin]

Maybe this IAM role (highlighted with red) already exist?

[nasch]

No, I've tried it at least three times with names that don't already exist.