Troubleshoot network connectivity to Amazon RDS databases using VPC Reachability Analyzer

July 2023: This post was reviewed for accuracy.

Amazon Virtual Private Cloud (Amazon VPC) enables you to provision a logically isolated section of theAWS Cloud where AWS resources such as Amazon Relational Database Service (Amazon RDS) DB instances can be launched in a virtual network you define. When creating an Amazon RDS DB instance, you have the option to create a new VPC or select the default VPC for provisioning the DB instance.

One common scenario is for you to set up a VPC that contains an Amazon Elastic Compute Cloud (Amazon EC2) instance hosting a public facing web application and an Amazon RDS/Amazon Aurora database that should not be publicly accessible. You can set up the connection between the EC2 instance and RDS / Aurora database manually or automatically by following the instructions in the configure automatic network connectivity with an EC2 instance guide. After setting up the connection, you may want to validate that your EC2 instance has connectivity to your RDS database instance.

In this post, we show you how to validate, trace, troubleshoot network connection and reachability from your compute resource (such as EC2 instance, AWS Lambda, or Amazon Elastic Kubernetes Service (Amazon EKS)) to your RDS DB instance using VPC Reachability Analyzer.

VPC Reachability Analyzer is a feature in Amazon VPC that can help you check network reachability between a source and destination resource on AWS. When the destination is reachable, Reachability Analyzer produces hop-by-hop details of the virtual network path between the source and the destination. When the destination isn’t reachable, Reachability Analyzer identifies the blocking or missing configurations.

Solution overview

The following are the high-level steps to troubleshoot connectivity issues for your RDS instances:

Find the IP address associated with the RDS database endpoint (instance, cluster, or read-only endpoint).
Find the ENI associated with the IP address for the RDS database endpoint (instance, cluster, or read-only endpoint).
Use Reachability Analyzer to create a “network path” between a source resource (for example, ENI associated with RDS database endpoint) and a destination resource (for example, EC2 instance).
Run the network path analysis, and review the results to understand or troubleshoot network reachability.

Prerequisites

For this exercise, we use the nslookup utility to identify the IP address for your RDS database, so you must have the nslookup utility installed in your workstation or in a bastion host such as EC2. Also ensure you have the required Identity and access management (IAM) permissions for VPC Reachability Analyzer for the IAM user or IAM role that you are using.

Step 1: Find the IP address associated with the RDS database

Find the IP address associated with the RDS database endpoint (instance, cluster, or read-only endpoint) using the Linux nslookup utility by providing the RDS database endpoint. You may run nslookup command from any Linux based workstation.

To locate your RDS database endpoint, refer to Finding the connection information for an Amazon RDS DB instance. You can see the endpoint name listed in database details page as shown in the following screenshot.

You can use the following command to find the IP address:

nslookup <RDS endpoint>

The following is the sample output:

nslookup db-instance-1.xxxxuuuu.us-zzzz-1.rds.amazonaws.com

Server: aaa.bb.ccc.dd
Address: aaa.bb.ccc.dd#nn

Non-authoritative answer:
db-instance-1.xxxxuuuu.us-zzzz-1.rds.amazonaws.comcanonical name = ec2-xx-yy-zz-nnn.
compute-1.amazonaws.com.
Name: ec2-xx-yy-zz-nnn.compute-1.amazonaws.com.
Address: xx.yy.zz.nnn

Step 2: Find the ENI associated with the RDS DB instance.

You can find the ENI using the AWS Management Console. On the Amazon Elastic Compute Cloud (Amazon EC2) console, choose Network interfaces in the navigation pane and search the IP address you got from the previous step. Note the ENI ID associated with the IP address for your RDS database.

Step 3: Specify the source and destination in Reachability Analyzer

Reachability Analyzer allows you to specify various source and destination resources between which you can analyze network reachability. To specify you source and destination resources, navigate to the Amazon VPC console, and choose Reachability Analyzer in the navigation pane. Then choose Create and analyze path in the Reachability Analyzer console.

Provide the source and destination details and choose Create and analyze path. In this example, I’m checking reachability between an RDS instance in a private subnet and an Amazon EC2 instance in public subnet within the same VPC. To specify the RDS database as the source or destination, I select Source Type as “Network interfaces” and enter the ENI ID determined from step 2 above. Also, make sure “Destination port” is your database port.

Step 4: Review the result of the analysis task

To view your successful network connection on the Amazon VPC console, choose Reachability Analyzer in the navigation pane. Choose the path ID and choose the correct analysis ID.

The following screenshot shows the Analysis explorer page.

In case of failure, Reachability Analyzer identifies the blocking component. You have a detailed visibility into the causes of the connectivity failure, which allows for quicker troubleshooting and resolving the issue. The following screenshot shows the connection failure in the flow.

You can also run this analysis in an automated manner using the following script. You must need to have the AWS Command Line Interface (AWS CLI) v2 installed and configured on the host from where you invoke this script from.

#!/bin/bash

#-----------------------------------------------------------------------------------------

show_usage() {

  echo ""

  echo "Script Usage : $0 RDS_END_POINT DEST_RES_ID [DEST_PORT]"

  echo ""

  echo "     RDS_END_POINT : Fully qualified endpoint of the RDS Database (Cluster/Instance)"

  echo "     DEST_RES_ID   : AWS Resource ID of the Destination Resource (NAT, IGW, TGW, VPCE etc.) "

  echo "     DEST_PORT     : [Optional] Port of the Detination resource"

  echo ""

  echo "script execution failed argument(S) missing"

  echo ""

  exit 1

}

#------------------------------- Send Mail  ----------------------------------------------

# Input validation

if [ $# -eq 2 ]; then

   RDS_EP=$1

   DEST_ID=$2

elif [ $# -eq 3 ]; then

   RDS_EP=$1

   DEST_ID=$2

   DEST_PORT=$3

else

   show_usage

fi

# Get IP of RDS DB Instance using nslookup

DB_HOST_IP=$(nslookup ${RDS_EP} | tail -2 | head -1 | cut -d':' -f 2 | sed 's/ //g')

echo -e "Source IP adderss : ${DB_HOST_IP}"

# Get RDS DB Instance ENI

DB_HOST_ENI=`aws ec2 describe-network-interfaces --filters Name=private-ip-address,Values=${DB_HOST_IP} --query 'NetworkInterfaces[*].[NetworkInterfaceId]' --no-cli-pager --output text`

if [ $? -ne 0 ]; then

  echo -e "Error in fetching ENI details"

  exit 1

else

  echo -e "Source ENI : ${DB_HOST_ENI}"

fi

# Create network insights analysis path

if [ $# -eq 3 ]; then

   NI_PATH_ID=`aws ec2 create-network-insights-path --source ${DB_HOST_ENI} --destination ${DEST_ID} --destination-port ${DEST_PORT} --protocol TCP --query 'NetworkInsightsPath.NetworkInsightsPathId' --no-cli-pager --output text`

else

   NI_PATH_ID=`aws ec2 create-network-insights-path --source ${DB_HOST_ENI} --destination ${DEST_ID} --protocol TCP --query 'NetworkInsightsPath.NetworkInsightsPathId' --no-cli-pager --output text`

fi

echo -e "Network Insights Path ID : ${NI_PATH_ID}"

# Start network insights analysis

NIA_ID=`aws ec2 start-network-insights-analysis --network-insights-path-id ${NI_PATH_ID} --query 'NetworkInsightsAnalysis.NetworkInsightsAnalysisId' --no-cli-pager --output text`

echo -e "Network Insights Analysis ID : ${NIA_ID}"

# Validate Result

STATUS=""

while [ "${STATUS}" == "" ]; do

  echo -e "Analysis in progress, wiating for 10 sec... "

  sleep 10

  STATUS=`aws ec2 describe-network-insights-analyses --network-insights-analysis-ids ${NIA_ID} --query 'NetworkInsightsAnalyses[].NetworkPathFound' --no-cli-pager --output text`

done

echo -e "Network Insights Analysis Status : ${STATUS}"

if [ "${STATUS}" == "True" ]; then

# Success

   echo -e "\nFound path to the destination"

   echo -e "\nForward Path :\n"

   aws ec2 describe-network-insights-analyses --network-insights-analysis-ids ${NIA_ID} \

   --query '[ NetworkInsightsAnalyses[*].ForwardPathComponents[*].SequenceNumber, NetworkInsightsAnalyses[*].ForwardPathComponents[*].Component[].Id ]' \

   --no-cli-pager --output table

   echo -e "\nReturn Path :\n"

   aws ec2 describe-network-insights-analyses --network-insights-analysis-ids ${NIA_ID} \

   --query '[ NetworkInsightsAnalyses[*].ReturnPathComponents[*].SequenceNumber, NetworkInsightsAnalyses[*].ReturnPathComponents[*].Component[].Id ]' \

   --no-cli-pager --output table

else

# Failure

   echo -e "\nNo Path Found to the destination"

   echo -e "\nDetails :\n"

   aws ec2 describe-network-insights-analyses --network-insights-analysis-ids ${NIA_ID} \

   --query 'NetworkInsightsAnalyses[*].Explanations[*]' --no-cli-pager --output table

fi<

Conclusion

The connectivity of an RDS database depends on multiple network resources, such as VPC security groups, route tables, and gateways. In the case of connectivity issues, manually checking all these resources can be difficult and time-consuming. VPC Reachability Analyzer helps you:

Understand network reachability and the network path between your source and destination resources in your VPCs
Troubleshoot network reachability issues caused by network misconfiguration between resources such as RDS/Aurora database and a compute resource such as an EC2 instance in your VPC

If you have any questions or suggestions about this post, leave a comment.

About the authors

Baji Shaik is a Sr. Database Consultant with AWS ProServe, GCC AMER. His background spans a wide depth and breadth of expertise and experience in SQL/NoSQL database technologies. He is a Database Migration Expert and has developed many successful database solutions addressing challenging business requirements for moving databases from on-premises to Amazon RDS and Aurora PostgreSQL/MySQL. He is an eminent author, having written several books on PostgreSQL. A few of his recent works include PostgreSQL Configuration, Beginning PostgreSQL on the Cloud and PostgreSQL Development Essentials. Furthermore, he has delivered several conference and workshop sessions.

Sudip Acharya is a Sr. Consultant with the AWS ProServe team in India. He works with internal and external Amazon customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

AWS Database Blog