C# cannot connect to remote MySQL

Asked By 0 points N/A Posted on -
qa-featured

How do you allow the remote MySQL server to be opened via a simple C# application?

I'm having a connection problem.
 
I'm using MySQL.net version 5 connector and Visual Studio 2005 express edition for this, but it is really strange working on the localhost but not on the remote computer.
 
Below is my code:
 
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Welcome …!");
String conString = "SERVER = 192.168.2.1; DATABASE = l2emu; User ID = root; PASSWORD = password;";
MySqlConnection connection = new MySqlConnection(conString);
String command = "SELECT * FROM people";
MySqlCommand cmd = new MySqlCommand(command,connection);
MySqlDataReader reader;
try
{
connection.Open();
cmd.ExecuteNonQuery();
reader = cmd.ExecuteReader();
cmd.CommandType = System.Data.CommandType.Text;
while (reader.Read() != false)
{
Console.WriteLine(reader["name"]);
Console.WriteLine(reader["department"]);
}
Console.ReadLine();
}
catch (MySqlException MySqlError)
{
Console.WriteLine(MySqlError.Message);
}
}
}
}
SHARE
Answered By 30 points N/A #84745

C# cannot connect to remote MySQL

qa-featured

 

Hello there,

There is no problem with your application; the problem exists in your database connection. Your database connection won't work since root username is only allowed for localhost. It will not allow any remote access. To gain access from remote computers to your MySQL server, you need to create another username which would handle the connection. First thing you need to do is add MySQL to your firewall exception. Then, in your MySQL administrator you need to create a new user account that will serve remote access in your database. You can also do this by typing the following code in your MySQL console:
 
CREATE USER 'client'@'%' IDENTIFIED BY 'password';
 
In this line, client is the username and password is the password, % means wild card for any host. In this case it would accept connection from any host. Once your connection instance is set, you need to grant privilege to that account. This means that you would choose what database is allowed to use and what transaction it can perform. To do this you need to write the following code:
 
GRANT ALL PRIVILEGE ON database_name.* TO 'client'@'%' IDENTIFIED BY 'password';
 
FLUSH PRIVILEGES;
 
The first line allows the username client to access the database database_name and all its tables. The second line flush the privileges granted to that user or simply means for the privilege to take effect.
 
Hope it helps you. Please give me a feedback on how it goes.
 
-Pabz-

 

 

Login/Register to Answer

Related Questions