How do I use the AWSSupport-TroubleshootRDSIAMAuthentication runbook to diagnose Amazon RDS DB authentication issues?

Short description

The AWSSupport-TroubleshootRDSIAMAuthentication runbook helps you to troubleshoot IAM authentication issues for some Amazon RDS clusters and instance types. The runbook supports the following types:

• Amazon RDS for PostgreSQL
• Amazon RDS for MySQL 
• Amazon RDS for MariaDB instances
• Amazon Aurora PostgreSQL-Compatible Edition
• Aurora MySQL-Compatible Edition

Use the runbook to verify the configuration that's required for IAM authentication with these instance and cluster types. The document also provides steps to resolve the connectivity issues that it diagnoses.

Note: The runbook doesn't support Amazon RDS for Oracle or Amazon RDS for Microsoft SQL Server instances.

If you provide a source Amazon Elastic Compute Cloud (Amazon EC2) instance and a target RDS database, then the AWSSupport-TroubleshootConnectivityToRDS child automation is invoked to troubleshoot TCP connectivity. The output provides commands that you run on your EC2 instance or source machine to connect to the RDS DB instance with IAM authentication.

Resolution

Prerequisites

Make sure that your IAM user or role has the required permissions to allow the following IAM actions:

"rds:DescribeDBInstances"  
"rds:DescribeDBClusters"  
"ec2:DescribeInstances"  
"ec2:DescribeNetworkAcls"  
"ec2:DescribeSubnets"  
"ec2:DescribeSecurityGroups"  
"ec2:DescribeRouteTables"  
"ssm:StartAutomationExecution"  
"iam:GetRole"  
"iam:GetUser"  
"iam:GetPolicy"  
"iam:ListAttachedRolePolicies"  
"iam:ListAttachedUserPolicies"  
"iam:ListUserPolicies"  
"iam:ListRolePolicies"  
"iam:SimulatePrincipalPolicy"  
"ssm:DescribeAutomationStepExecutions"  
"ssm:GetAutomationExecution"  
              
              
"ssm:StartAutomationExecution" for Resources:  
"arn:aws:ssm:*:*:automation-definition/AWSSupport-TroubleshootConnectivityToRDS:*"  
"arn:aws:ssm:*:*:automation-definition/AWSSupport-TroubleshootRDSIAMAuthentication:*"

Run the AWSSupport-TroubleshootRDSIAMAuthentication runbook

1. Navigate to the AWSSupport-TroubleshootRDSIAMAuthentication runbook in the AWS Systems Manager console. 
2. Choose Execute automation.
3. For input parameters, enter the following information:
   AutomationAssumeRole (optional): Enter the ARN of the IAM role that allows Systems Manager Automation to perform actions for you. If you don't specify a role, then Systems Manager Automation uses the permissions of the user that starts the runbook.
   RDSType (Required): Choose the type of RDS DB instance that you want to connect to and authenticate. The allowed values are Amazon RDS or Amazon Aurora Cluster.
   DBInstanceIdentifier (Required): Enter the identifier of the target RDS DB instance or the Aurora DB cluster that you want to connect to, and use IAM credentials for authentication.
   SourceEc2InstanceIdentifier (Optional): To connect from an EC2 instance that's in the same account and AWS Region as the DB instance, enter the EC2 instance ID. If the source isn't an EC2 instance or the target RDS type is an Aurora DB cluster, then keep this field blank. 
   DBIAMRoleName (Optional): Enter the IAM role name that you use for IAM authentication. Provide this field only if DBIAMUserName isn't provided. Otherwise, keep this field blank. You must provide either the DBIAMRoleName or DBIAMUserName.
   DBIAMUserName (Optional): Enter the IAM user that you use for IAM authentication. Provide this field only if DBIAMRoleName isn't provided, otherwise leave this field blank. You must provide either the DBIAMRoleName or DBIAMUserName.
   DBUserName (Optional): Enter the database user that's mapped to an IAM role or user for IAM authentication in the database. The default option * is used to evaluate, so nothing is provided in this field.
4. Choose Execute.

Review the output

When the automation is complete, review the Outputs section for detailed results:

• Checking the IAM User/Role permission to connect to Database: Verifies that the required IAM permissions are in the IAM user or role. Sufficient permissions are required to use the IAM credentials to authenticate into the RDS DB instance for the specified database user.
• Checking IAM-Based Authentication Attribute for the database: Verifies that the IAM authentication feature is activated for the specified RDS or Aurora DB cluster or instance.
• Checking Connectivity from EC2 Instance to RDS Instance: Checks if the required network configurations are in place to allow you to connect from the EC2 instance to the RDS or Aurora DB instance. This checks the virtual private cloud (VPC), security group, network access control list (network ACL), and Amazon RDS availability.
• Next Steps: Lists the commands and the steps that you can follow to connect to your RDS or Aurora DB instance with the IAM credentials.

Related information

Running a simple automation (console)

Setting up Automation

How do I allow users to authenticate to an Amazon RDS for MySQL DB instance using their IAM credentials?

AWSSupport-TroubleshootConnectivityToRDS

Systems Manager Automation runbook reference