C# Winforms connection to MySQL AWS Database

Question

I'm trying to connect a winforms .net application with an AWS RDS MySQL database but I am having difficulty making the connection. I have read a lot of material about connecting through Microsoft SQL database and through Elastic Beanstalk but I haven't come across the answer I'm looking for... possibly because I'm a noob.

I've looked through a few of these questions: How to connect to MySQL Database? https://dev.mysql.com/doc/dev/connector-net/8.0/html/T_MySql_Data_MySqlClient_MySqlConnection.htm

using MySql.Data.MySqlClient;

string connection = "server=localhost; Database=database_URL; User Id=admin; 
Password=myPassword";
myConn.Open();
MessageBox.Show("Success");

I'm getting the following error message:

MySql.Data.MySqlClient.MySqlException: 'Unable to connect to any of the specified MySQL hosts.'

Is there something simple that I'm missing? I have copied the database endpoint into the database_URL location. My user id and password are correct. My database is setup on AWS as a MySQL database.

Can you able to login into MySQL using terminal/command line? — James, Aug 01 '19 at 04:29
Often you need to create firewall rules to allow access to the cloud database server (for security reasons). "When you first create a DB instance, its firewall prevents any database access except through rules specified by an associated security group." https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.html — Bradley Grainger, Aug 01 '19 at 04:40
You should consider the negative implications of opening your MySQL instance up to the internet. Anyone trawling your application for readable strings will find your MySQL server's IP address, admin username and password giving them full access. — AlwaysLearning, Aug 01 '19 at 05:30
Unless you're running /developing this winforms application directly on an EC2 instance that also houses a MySQL server, putting localhost as the server name isn't going to work out. Ensure the port your MySQL db is using is open to the world (bad idea) then put the public internet IP address of your db server, into your connection string in place of `localhost`. - then read up on why it's a bad idea and change things around so your app uses a web service or similar, and the web service has a private trusted connection to mysql — Caius Jard, Aug 01 '19 at 05:52

score 1 · Answer 1 · answered Aug 01 '19 at 07:26

Checking back with ConnectionStrings makes it appear as if your parameter-names are wrong. 'username' should be 'uid' and 'password' should be 'pw'.

In any case I'd suggest using the MySqlConnectionStringBuilder-class to construct your connection string.

var connectionStringBuilder = new MySqlConnectionStringBuilder
{
    Server = "<Instance_Ip>",
    UserID = "root",
    Password = "<Password>",
    Database = "<Database_Name>"
};

using (var conn = new MySqlConnection(connectionStringBuilder.ToString()))

score 0 · Answer 2 · answered Aug 01 '19 at 04:55

The error message is given because can't connect to the host.

In your connection string is given the localhost as the server but your database is on cloud (AWS), so it means that you must specify the database's IP or the domain name pointing to that database, not the local (local means that is in your computer). e.g.

string conn = "server=192.168.0.7; Database=database_name; User Id=admin; Password=myPassword";

Note that the server IP is provided by AWS, and you'd make sure that ports are enable. The most common port for MySQL is 3306.

Best regards.

I'm not sure it's too helpful, providing an example that uses a 192.168.x.x local lan address to someone who has failed to grasp the concept that localhost is not the public facing IP of their cloud based database server.. — Caius Jard, Aug 01 '19 at 05:43

score 0 · Answer 3 · answered Aug 01 '19 at 07:12

Try this,

   //This is my connection string i have assigned the database file address path  
   string MyConnection2 = 
   "host='localhost';database='databasename';username='myusername';password='mypassword'";
   //This is my insert query in which i am taking input from the user through windows forms  
   string Query = "Your query";
   //This is  MySqlConnection here i have created the object and pass my connection string.  
   MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
   //This is command class which will handle the query and connection object.  
   MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
   MySqlDataReader MyReader2;
   MyConn2.Open();
   MyReader2 = MyCommand2.ExecuteReader();     
   // Here our query will be executed and data saved into the database.  
   MessageBox.Show("Save Data");
   while (MyReader2.Read())
   {
   }
   MyConn2.Close();

C# Winforms connection to MySQL AWS Database

3 Answers3