Cannot connect .net core API in AWS Lambda to RDS

Question

I have a .net core web api that I am trying to deploy to AWS Lambda / API Gateway. I get a bland error "Unable to connect to any of the specified MySQL hosts".  
  
My API has 2 endpoints for testing. They are /TestConnect and /TestAWS. The /TestConnect endpoint works properly (returns a "success" string) so I know that the Web Api has deployed properly. The /TestAWS endpoint is just supposed to return the first row of a table. That code is below, but it's pretty straightforward. I know that this isn't best practice, but I used it to test in order to try and eliminate all of the other possibilities.

```
       [HttpGet("TestAWS")]
        public IActionResult TestAWS()
        {
            try
            {
                string dataString = "";

                MySqlConnectionStringBuilder cnString = new MySqlConnectionStringBuilder();

                cnString.Server = Environment.GetEnvironmentVariable("DB_ENDPOINT");
                cnString.UserID = Environment.GetEnvironmentVariable("USER");
                cnString.Password = Environment.GetEnvironmentVariable("PASSWORD");
                cnString.Database = Environment.GetEnvironmentVariable("DATABASE");
                cnString.Port = Environment.GetEnvironmentVariable("PORT");;

                using (MySqlConnection conn = new MySqlConnection(cnString.ToString()))
                {
                    conn.Open();

                    MySqlCommand cmd = new MySqlCommand("SELECT UserName FROM Users LIMIT 1", conn);

                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            dataString = dr["userName"].ToString();
                        }
                    }
                }

                return Ok("Returned: " + dataString);
            }
            catch (Exception ex)
            {
                return BadRequest("Returned Date { Environmnet: " + Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") + "-" + ex.Message + "}");
            }
        }
```

To debug the problem, I created a second Lambda function directly in AWS. It's a really simple Node.js function that does the same thing as my API.

```
var mysql = require('mysql');
var config = require('./config.json');
var pool  = mysql.createPool({
    connectionLimit : 10,
    host            : config.dbhost,
    user            : config.dbuser,
    password        : config.dbpassword,
    database        : config.dbname
  });

  exports.handler = (event, context, callback) => {
    context.callbackWaitsForEmptyEventLoop = false;
pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected

  connection.query('SELECT UserName FROM Users LIMIT 1', function (error, results, fields) {
    connection.release();

    if (error) callback(error);
    else callback(null, results[0].UserName)
  });
});
  };
```

That function works properly and returns the row from the table.  
  
Both functions have identical VPC settings. Same VPC, same subnets, same IAM role, same Security Group. That tells me that the AWS infrastructure is setup correctly and that there are no issues with the VPC, NACLs, etc.  
  
I think that just leaves the C# code.  
  
Do I have to make some changes to the Web Api in order for it to work? It almost seems like I have to add some Lambda-specific handling in the C# like I did in the working Lambda, but I can't find any references to that effect.  
  
Any help is greatly appreciated.

Answer

I am hooking the command to the connection here:

```
                    conn.Open();
                    MySqlCommand cmd = new MySqlCommand("SELECT UserName FROM Users LIMIT 1", conn);
 
```

Interestingly, I found the issue.  I was using the MySql.Data NuGet package and that wasn't working.  
From advice, I switched to the MySqlConnector package and it is now working properly.  
  
Thank You!

Answer

Your process looks a little off, I don't see where you are linking your command and connection.  Try:

```
using (MySqlConnection conn = new MySqlConnection(cnString.ToString()))
                { 
                    MySqlCommand cmd = new MySqlCommand("SELECT UserName FROM Users LIMIT 1", conn);
                     cmd.Connection = conn;
                    conn.Open();

                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            dataString = dr["userName"].ToString();
                        }
                    }
                }
```

Cannot connect .net core API in AWS Lambda to RDS

Relevant content