0

After seeing that default passwords generated by phpMyAdmin used mysql_native_password and therefore hashes didn't changed when equal passwords where used, I updated one user's password and set the hashing to SHA256.

When I tried to login again using the setted password it says:

mysqli_real_connect(): (HY000/1045): Access denied for user 'xxxx'@'xxxx' (using password: YES).

The server uses https and phpMyAdmin forces to uses SSL as well.

Once the password is changed with SHA256, the only way to gain access again is manually setting the password and reverting password plugin to mysql_native_password.

How can I login to phpMyAdmin with users using SHA256 passwords?

Note: if I try to use the same user and the SHA256 password to login to mysql console it works fine.

Jorge C.M
  • 375
  • 3
  • 11
  • @samuel-liew you asked me to flag this as a duplicate of another question, but I don't think I have enough reputation to mark a question as a duplicate of one without an accepted answer. Please help? – Isaac Bennetch Dec 18 '19 at 05:08

3 Answers3

3

MySQL has recently changed the default authentication type and, between MySQL and PHP, this change took quite a while to be supported by PHP. The old method was mysql_native_password and the new one is caching_sha2_password. PHP versions starting with 7.4 support the new method. Since this is related to PHP itself, phpMyAdmin supporting this method requires you to run an updated PHP installation (phpMyAdmin itself is ready for this change, but your ability to use it depends on your PHP version).

You basically have two options:

  1. Upgrade your PHP to 7.4 or newer.
  2. Change the authentication type on your user to the older mysql_native_password. You can do that by editing the user (from the User accounts tab in phpMyAdmin, edit the user, then change the authentication method from the dropdown).
Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
  • That's what I did as I wrote on my answer. But yours gives another option for those who doesn't want to upgrade all services (MySQL, as mine was 5.7, PHP and phpMyAdmin), so thank you so much for your detailed answer! – Jorge C.M Dec 17 '19 at 20:53
  • 1
    Thank you. Yep, I saw that you were able to resolve your problem but figured you (and future posters) might like to know detail about what exactly caused the problem. Thank you for marking this as accepted! – Isaac Bennetch Dec 17 '19 at 21:01
0

Problem was that ubuntu's 18.04.3 install had outdated MySQL, PHP and phpMyAdmin.

To solve the problem I needed to update MySQL from 5.7 to 8, PHP from 7.3 to 7.4 and phpMyAdmin to 4.9.2.

After updating I can log in with users that has a caching_sha2_password password so, for those with same problem as mine, check your current versions of PHP, MySQL and phpMyAdmin and upgrade them if needed. (Perform a backup first as you may encounter data loss)

Jorge C.M
  • 375
  • 3
  • 11
0

I know the answer has been up there, but I wanna just put some instruction that help me to solve this problem. First: was hard to me change the authentication type of mysql, I couldn't to that and I couldn't update PHP from MAC system, I don't know how to to that and I just didn't wanna mess with everything, so what I did? I installed php from BREW and I've changed the httpd.conf.

  1. install BREW.

  2. install php from brew.

  3. open the file [httpd.conf] using terminal:

    sudo nano /etc/apache2/httpd.conf

  4. go to line:

    LoadModule php7_module libexec/apache2/libphp7.so

    and comment it.

  5. insert this line:

    LoadModule php7_module /usr/local/opt/php/lib/httpd/modules/libphp7.so

So, you just change the PHP MAC system to PHP BREW - that is updated, new version.

Paul Roub
  • 36,322
  • 27
  • 84
  • 93
Moises B. Almeida
  • 361
  • 1
  • 4
  • This is definitly an answer. But you could improve its perception as such by rephrasing away from the impression of being a "Me too." or being another question instead of an answer. – Yunnosch May 12 '20 at 22:08