C# cannot connect to remote MySQL

Asked By roqz_2011 0 points N/A Posted on - 10/01/2011

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);

}

}

}

}

• Status: Open
• Question Views: 2286
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: C#

Answered By pabz07 30 points N/A #84745

C# cannot connect to remote MySQL

 

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-

 

 

About pabz07

Questions
3

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 10/04/2011
• Question Category: C#