I recently need to work on a project to create a new web API project with an existing MySQL database.

This article helped me to set up the project. However, I was only able to connect to my localhost database, not the remote database.

The error I originally had was:

MySql.Data.MySqlClient.MySqlException: Authentication with old password no longer supported, use 4.1 style passwords.

My first attempt to solve the problem is granting privileges to my PC to be able to run queries on the remote database.

Mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'myIpAddress';

After this, I can check the remote database users and host which is allowed to connect.

Mysql> SELECT user,host FROM mysql.user;
+-----------------------------+
|user           | host        |
+---------------+-------------+
|database_user  | %           |
|database_user  | myIpAddress |
+---------------+-------------+

Everything looks good, so I tried to call my API again. And this time a new error come up.

MySql.Data.MySqlClient.MySqlException: Authentication to host 'remoteDBhost' for user 'database_user' using method 'mysql_native_password' failed with message: Access denied for user 'database_user'@'myIpAddress' (using password: YES)

I tried below solutions.

Mysql> SET SESSION old_passwords=0;
Mysql> SET PASSWORD FOR 'database_user'@'myIpAddress'=PASSWORD('password here');

With all above settings, I finally get my 200 response.