0

I can't connect to my sql server, i tried some fixes from stackoverflow and google and it didn't help me. Thanks.

  connString = "SERVER ='''myserverip''';PORT=3306;DATABASE=mydatabase;UID=myuser;PASSWORD=mypassword";
        try
         {
             conn = new MySqlConnection();
             conn.ConnectionString = connString;
             conn.Open();
             MessageBox.Show("Connection success");

         }
         catch (MySql.Data.MySqlClient.MySqlException ex)
         {
             MessageBox.Show(ex.Message);
         }

To configure myuser I used this on my linux vps.

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword'; CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; GRANT ALL ON *.* TO 'myuser'@'localhost'; GRANT ALL ON *.* TO 'myuser'@'%';

i tried : Unable to connect to any of the specified mysql hosts. C# MySQL ( i tried to use MySqlConnectionStringBuilder, don't specify the port, instead of password in connection string i typed psw); Disable my pc firewall, disable linux server firewall

Mihai
  • 59
  • 2
  • 10

4 Answers4

0

MySqlConnectionStringBuilder does enable you to specify port. Just tested, this works just fine:

MySqlConnectionStringBuilder csb = new MySqlConnectionStringBuilder();
csb.Server = "192.168.1.105";
csb.Port = 3307;
csb.Database = "test";
csb.UserID = "me";
csb.Password = "mypassword";

cn = new MySqlConnection(csb.ToString());
cn.Open();

Are you quite sure your MySql server actually accepts incoming connections over TCP? By default that is disabled.

Avo Nappo
  • 620
  • 4
  • 9
  • same error, Unable to connect... maybe is something wrong with how i create the user? – Mihai Oct 07 '17 at 17:45
  • Well, first suspect then, that the MySql server doen't allow incoming TCP connections. I recall you had to explicitly enable that. See another SO post on the subject: https://stackoverflow.com/questions/14779104/how-to-allow-remote-connection-to-mysql – Avo Nappo Oct 07 '17 at 17:47
  • You are granting access to the user on localhost only. Hypothesis: that server is not on the same machine as the one you are trying to connect from? – Avo Nappo Oct 07 '17 at 17:51
  • i used CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword'; – Mihai Oct 07 '17 at 17:54
  • I suspect the problem is in the `GRANT ALL ON *.* TO 'myuser'@'localhost'; GRANT ALL ON *.* TO 'myuser'@'%';` part Specifically, you limit the network access to myuser to localhost. That user will not be able to connect from another computer. Try this instead: `GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.%.%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;` – Avo Nappo Oct 07 '17 at 17:57
  • tied that, dose not work same error, i tried create a new user with @'192.168.%.%' and same error, and when i paste that command i get " Query OK, 0 rows affected, 1 warning (0.00 sec) " – Mihai Oct 07 '17 at 18:03
  • Are you able to connect to that server at all, from another machine? With some sql server management tool a'la HeidiSQL or something? – Avo Nappo Oct 07 '17 at 18:05
  • tried a windows 10 data link " .udl ", and i get error https://i.gyazo.com/c06b01d42c33e51c7c3a1265b94f7b2c.png ,HeidiSQL also don't work – Mihai Oct 07 '17 at 18:09
  • The Port works fine in linux, i scanned with nmap, and 3306/tcp is open for mysql (no firewall) – Mihai Oct 07 '17 at 18:16
  • Well, then its most likely MySql server/user access configuration, somehow. In any case, using MySqlConnectionStringBuilder or its equivalent for other server flavours, is generally better than writing connection strings manually. Too easy to get something wrong :) – Avo Nappo Oct 07 '17 at 18:22
0

So, after searching on google how to setup sql connection in linux, and trying different setups hardly I fixed it so I want to share with stackoverflow how I fixed it. Thanks for help @Avo Nappo.

First you need to comment out the line #bind-address from your sql config. The accepted answer here :MySQL root access from all hosts .

Then I create a user using this command CREATE USER 'golden'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON * . * TO 'golden'@'%'; And in c# I used my default connection string that is in the question. I don't know why but MySqlConnectionStringBuilder dose not work on my pc. I hope this will help someone. Have a nice day and keep coding.

Mihai
  • 59
  • 2
  • 10
0

You must go in your Remote MySQL in your C-Pannel and Add Access Host first and change this code 

connString = "SERVER ='''myserverip''';PORT=3306;DATABASE=mydatabase;UID=myuser;PASSWORD=mypassword";

to

connString = "SERVER =*Put here your Hostname with Port*;DATABASE=mydatabase;UID=myuser;PASSWORD=mypassword;";

Or use this Method

MySqlConnectionStringBuilder ConStD = new MySqlConnectionStringBuilder();

ConStD.Server = "Hostname that get from Manage Access Hosts";
ConStD.Port = Port that get from Manage Access Hosts;
ConStD.Database = "YourDatabaseName";
ConStD.UserID = "yourUsername";
ConStD.Password = "yourpassword";

try
{
    MySqlConnection conn = new MySqlConnection(ConStD.ToString());
    conn.Open();
    MessageBox.Show("connection Success");
}
catch (MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

It's Must working For You

Yassine EL Bouji
  • 1
  • 1
0

1-Un check all Firewall and Network protection settings 2-Go in database management system running icon like wamp green icon 3-Go to Mysql CMD 3a-

CREATE USER 'muzamil'@'%' identified by 'muzamil';
grant all privileges on *.* to 'muzamil'@'%';
flush privileges;

These three commands run one by one 4-Windows Defender Firewall then Click on Advance settings ==> Inbound Rules ->Enable riles Remote Desktop TCP All Rules

5: C:\wamp64\alias open this File then change local to ==> Require all granted ==> Allow from all

Your remote server will work with c# application

Muzamil Ali
  • 1