0

I created a test user for my database. It is MySQL/MariaDB (running the MySQL server that comes with XAMPP).

Steps to create the test user:

CREATE USER 'user1'@'%' IDENTIFIED BY 'user1';
GRANT ALL PRIVILEGES ON mydb.* TO 'user1'@'%' IDENTIFIED BY 'user1';
FLUSH PRIVILEGES;

Next, I tried to log in with a C# WinForms application as that user.

Since I am on my local machine, I just used 127.0.0.1 as the host.

Connection string was: Server=127.0.0.1; Port=3306; Database=mydb; Uid=user1; Pwd=user1;

I got the following error message upon trying to open the connection:

Authentication to host '127.0.0.1' for user 'user1' using method 'mysql_native_password' failed with message: Access denied for user 'user1'@'localhost' (using password: YES)

Upon doing a SELECT host, user, password FROM mysql.user I get the following:

+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| 127.0.0.1 | root  |                                           |
| ::1       | root  |                                           |
| localhost |       |                                           |
| localhost | pma   |                                           |
| %         | user1 | *B9CFB5C1F2F7B45EF0F9A826B2250F6250D85E73 |
+-----------+-------+-------------------------------------------+

So, why am I seeing this error message? Since the domain for user1 is defined as any (%), I should be able to connect with this user from localhost or 127.0.0.1, no?

David Mordigal
  • 399
  • 3
  • 19
  • Related : http://stackoverflow.com/questions/6445917/connect-failed-access-denied-for-user-rootlocalhost-using-password-yes –  Jul 09 '16 at 03:40
  • Also answer "% doesnt include localhost" : http://stackoverflow.com/questions/10823854/using-for-host-when-creating-a-mysql-user –  Jul 09 '16 at 03:42
  • Wow, go figure it was the "localhost" part. Interesting. Of course changing it to my computer's IP address as seen from the Internet worked. – David Mordigal Jul 09 '16 at 03:51
  • so then `CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user1';` presumably fixes one aspect? – Drew Jul 09 '16 at 03:53
  • @Drew yes, that fixed it – David Mordigal Jul 10 '16 at 01:10

1 Answers1

0

The issue was that the % does not include localhost due to the differences in how the connection is setup from localhost compared to any other IP address. Changing the host name to my computer's public IP address worked, though I will probably add another user entry for the same username but for localhost as the domain.

David Mordigal
  • 399
  • 3
  • 19