Access AWS RDS MySQL database from a Lambda Function without a proxy-CodePudding

I'm trying to connect to an AWS RDS instance containing a MySQL database from a lambda function (LF) but I can't get it to work.

I don't want to use an RDS proxy yet because for now, there'll only be 1 connection to the RDS database at a time.

The LF and RDS instance are both:

in eu-west-2
in the default VPC
in all 3 subnets
assigned the same security group that allows inbound outbound access to port 3306

The LF has a role assigned that contains the AmazonRDSFullAccess & AWSLambdaVPCAccessExecutionRole IAM policies.

All three subnets have a common NACL that contains an inbound and outbound rule that allows all traffic to/from 0.0.0.0/0.

All three subnets have a common route table containing a route with the default VPC's CIDR as the destination and 'local' as the target.

The RDS instance is publicly available.

DNS hostnames and DNS resolution are enabled on the VPC.

The Lambda application code is as below:

using System;
using System.Data.SqlClient;

namespace TestRDSAccess
{
    public class Function
    {
        public void FunctionHandler()
        {
            string server = Environment.GetEnvironmentVariable("DB_ENDPOINT");
            string database = Environment.GetEnvironmentVariable("DATABASE");
            string username = Environment.GetEnvironmentVariable("USER");
            string password = Environment.GetEnvironmentVariable("PASSWORD");

            string connectionString = String.Format("server={0};database={1};user={2};password={3}", server, database, username, password);

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
            }
        }
    }
}

Regarding the connection string, when this is incorrect in any way, it times out rather than returning an error immediately.

The environment variable strings return the correct values if I write them to the console. The 'server' string contains both the endpoint and ",3306". (Note that this is contrary to AWS's own documentation, which says to specify 'port=' in the connection string, which causes a 'parameter not recognised' error.)

My error upon invoking the Lambda function:

  "errorType": "InvalidOperationException",
  "errorMessage": "Internal connection fatal error.",
  "stackTrace": [
    "at System.Data.SqlClient.TdsParserStateObject.TryProcessHeader()",
    "at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)",
    "at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)",
    "at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)",
    "at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)",
    "at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)",
    "at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)",
    "at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)",
    "at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)",
    "at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)",
    "at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)",
    "at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)",
    "at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)",
    "at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)",
    "at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)",
    "at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)",
    "at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)",
    "at System.Data.SqlClient.SqlConnection.Open()",
    "at TestRDSAccess.Function.FunctionHandler() in [local path to cs file]:line 62"
  ]

The last line is a reference to the conn.Open(); line even though it isn't at line 62.

Also, in the RDS instance's logs and events section, in 'error/mysql-error-running.log', I see the following error:

2022-05-01T15:05:08.928116Z 609 [Warning] [MY-010055] [Server] IP address '172.31.34.94' could not be resolved: Name or service not known

This sounded suspiciously like a DNS issue. On the basis that because the RDS instance is set to public so that it's resolving the endpoint to a public IP address, which potentially makes no sense when I'm trying to access it from within the same VPC (from the LF), I tried setting 'publicly available' on the RDS instance to false but it made no difference.

172.31.34.94 is within the VPC's CIDR (172.31.0.0/16).

Does anyone have any idea what I'm missing? Sorry this post is so long; I wanted to explain everything!

CodePudding user response：

You can't use SqlConnection to connect to a MySQL database.

Install MySqlConnector and use the MySqlConnection type instead.

The 'server' string contains both the endpoint and ",3306". (Note that this is contrary to AWS's own documentation, which says to specify 'port=' in the connection string, which causes a 'parameter not recognised' error.)

Follow the AWS documentation and put Server and Port separately in the connection string. MySqlConnection does not support adding the port after a comma. (Note that you can actually omit Port=3306; because that is the default.)