AWS Glue Connection to RDS SQL Server Fails – "Failed status due to failed connection"

Question

Background
I am working on data migration from an on-premises SQL Server database to an RDS SQL Server instance using AWS Glue. To facilitate this, I created a JDBC connection in AWS Glue to connect to my Amazon RDS SQL Server instance, but the connection fails with the following error:
"Failed status due to failed connection"
Steps Taken
Created an RDS SQL Server Instance
Launched an Amazon RDS SQL Server instance inside a VPC.
Configured subnets, security groups, and network settings accordingly.
Set Up Security Group Rules
Inbound Rules: Allowed traffic for SQL Server (TCP 1433) from my VPC resources.
Outbound Rules: Allowed necessary outbound traffic.
Created a Glue Connection
Defined a JDBC connection in AWS Glue pointing to the RDS SQL Server instance.
Used the correct JDBC URL format:
Used the same VPC, security group, and subnets as the RDS instance.
Issue Faced
Despite following these steps, the Glue connection fails with the error "Failed status due to failed connection."
Possible Causes and Troubleshooting Steps Tried
VPC and Subnet Configuration:
The Glue job must run in the same VPC and subnet as the RDS instance.
Verified that the RDS SQL Server instance and Glue connection both use the same VPC and subnets.
Security Group Settings:
Ensured that the security group of RDS allows inbound connections on port 1433 from Glue.
Also checked outbound rules allowing traffic.
Network Access (Public vs. Private):
If the RDS instance is private, Glue needs a private subnet with a NAT gateway or VPC endpoints to reach it.
If the RDS instance is public, it should have a publicly accessible endpoint.
IAM Permissions:
The IAM role assigned to Glue has the necessary permissions to connect to RDS.
Database Authentication:
Verified that the credentials (username/password) used in the JDBC connection are correct.
Testing Connectivity:
Tried connecting from an EC2 instance in the same VPC using SQL Server Management Studio (SSMS) to ensure connectivity.
Used telnet  1433 to check if the RDS instance is reachable from the Glue environment.
Questions:
Are there additional network settings required for Glue to connect to RDS SQL Server?
Does Glue need a specific IAM permission to establish a JDBC connection with RDS SQL Server?
How can I debug the connection issue further?
Any insights or solutions would be greatly appreciated!

re:Post Agent · Answer

Based on the information provided, there are several potential reasons for the "Failed status due to failed connection" error when attempting to connect AWS Glue to your RDS SQL Server instance. Here are some additional troubleshooting steps and considerations:

1. Network Configuration:
   Ensure that your AWS Glue job is running in a private subnet with a NAT Gateway or VPC endpoints configured. This is necessary for Glue to reach your RDS instance if it's in a private subnet. Double-check that the route tables for your private subnets are correctly configured to route traffic through the NAT Gateway.

2. Security Group Configuration:
   Verify that the security group associated with your Glue connection includes a self-referencing inbound rule. This allows connectivity between Glue workers. Also, ensure that the security group attached to your RDS instance allows inbound traffic on port 1433 from the security group used by Glue.

3. JDBC URL Format:
   Confirm that your JDBC URL is correctly formatted for SQL Server. It should look something like this:
   jdbc:sqlserver://your-rds-endpoint:1433;databaseName=your-database-name

4. IAM Permissions:
   The IAM role associated with your Glue job should have permissions to access both the Glue service and the RDS instance. Ensure it has the necessary policies attached, including AWSGlueServiceRole and permissions to access RDS.

5. RDS Configuration:
   Check if your RDS instance allows connections using the SQL Server authentication method you're attempting to use (SQL Server authentication or Windows authentication).

6. Encryption and SSL:
   If your RDS instance requires SSL, make sure your JDBC connection string includes the appropriate SSL parameters.

7. Debugging:
   To further debug the connection issue, you can:
   - Review the CloudWatch logs for your Glue job for more detailed error messages.
   - Use the AWS Glue Data Catalog to create a connection and test it using the AWS Glue console.
   - Try creating a Glue notebook and attempt to connect to your RDS instance from there, which might provide more verbose error messages.

8. Alternative Testing:
   If possible, try creating a simple Glue ETL job that reads from your RDS instance. Sometimes, the connection test can fail while an actual job succeeds.

9. Version Compatibility:
   Ensure that the JDBC driver version used by Glue is compatible with your SQL Server version.

If you've verified all these points and are still experiencing issues, you may want to consider using the AWSSupport-TroubleshootGlueConnection runbook in AWS Systems Manager. This automated tool can help identify common connection issues between Glue and your data source.

Remember, when troubleshooting connection issues, it's often helpful to start with the simplest configuration and gradually add complexity, testing at each step to isolate the problem.  
**Sources**  
[Troublehsoot AWS Glue connection to a JDBC data source | AWS re:Post](https://repost.aws/knowledge-center/glue-troubleshoot-connection-jdbc-source)  
[Can't create Glue Connection to RDS MySQL - [Error] Failed to test connection due to FAILED status. | AWS re:Post](https://repost.aws/questions/QUII9-vGBPQ0OwbOuk00M68g/can-t-create-glue-connection-to-rds-mysql-error-failed-to-test-connection-due-to-failed-status)  
[Connection of glue with rds database isn't working | AWS re:Post](https://repost.aws/questions/QU-QCZqMb2T66On0Kg8XH5fg/connection-of-glue-with-rds-database-isn-t-working)  
[Communications link failure between Glue and AWS RDS | AWS re:Post](https://repost.aws/questions/QU6LbxeCEwTlGlg8FTm8Ln4Q/communications-link-failure-between-glue-and-aws-rds)

AWS Glue Connection to RDS SQL Server Fails – "Failed status due to failed connection"

Add your answer

Relevant content