How to connect to a remote MySQL server using C#?

Question

I have a C# Application which would access a MySQL server on another computer. I am trying to do it via IP. Here is my Connection String :

server = "192.168.10.221";
database = "restaurantdb";
uid = "root";
password = "";
string connectionString;
connectionString = "SERVER=" + server + "; PORT = 3306 ;" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
mycon = new MySqlConnection(connectionString);

Now the idea is, I would connect it through the Internet or Wi-Fi connection on which both of the computers are connected to. I would then access the database through SQL Strings coded on C#. Now I could also connect them through LAN networking but I don't know how.

I am getting this exception in my code

{"Access denied for user 'root'@'Crave-PC.lan' (using password: NO)"}   System.Exception {MySql.Data.MySqlClient.MySqlException}

Any ideas how I can access the server through networking?

Answer 1

The problem is at MySQL Server side, root user doesn't have permissions to connect remotelly. To give root user permisions to connect remotelly just type this on a mysql command line:

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'rootPass';

Just change 'rootPass' for your current root password, if root doesnt have password MySQL wont let you connect so you will have to define one for it.

One side note: As a security best practice you should define a different user other than root to access your mysql databases from client applications.

Hope it helps,