Author Topic: Getting SQLSTATE[HY000] [2054] The server requested authentication method unknow  (Read 5978 times)

Offline Siv

  • Newbie
  • *
  • Posts: 7
Hi,
I am just setting up Roundcube on an Ubuntu 19.10 Server and have downloaded the roundcube files and set up everything and am running through the webmail installer and everything is coming up OK except the    Check DB config section which says:

Code: [Select]
SN (write):  NOT OK(SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client)

Make sure that the configured database exists and that the user has write privileges
DSN: mysql://roundcubeuser:Lynn%3F1958@localhost/roundcube

I have checked that the user has write privileges and they do:
Code: [Select]
mysql> SELECT * FROM mysql.user WHERE user = 'roundcubeuser'\G
*************************** 1. row ***************************
                    Host: localhost
                    User: roundcubeuser
             Select_priv: N
             Insert_priv: N
             Update_priv: N
             Delete_priv: N
             Create_priv: N
               Drop_priv: N
             Reload_priv: N
           Shutdown_priv: N
            Process_priv: N
               File_priv: N
              Grant_priv: N
         References_priv: N
              Index_priv: N
              Alter_priv: N
            Show_db_priv: N
              Super_priv: N
   Create_tmp_table_priv: N
        Lock_tables_priv: N
            Execute_priv: N
         Repl_slave_priv: N
        Repl_client_priv: N
        Create_view_priv: N
          Show_view_priv: N
     Create_routine_priv: N
      Alter_routine_priv: N
        Create_user_priv: N
              Event_priv: N
            Trigger_priv: N
  Create_tablespace_priv: N
                ssl_type:
              ssl_cipher:
             x509_issuer:
            x509_subject:
           max_questions: 0
             max_updates: 0
         max_connections: 0
    max_user_connections: 0
                  plugin: caching_sha2_password
   authentication_string: $A$005$1/s<J:zIf+nl68imY8xlIvvylJD/3yaT9ydelWOcgP.FAT3QE0MLj1
        password_expired: N
   password_last_changed: 2019-11-17 01:19:45
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: N
          Drop_role_priv: N
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: NULL
1 row in set (0.01 sec)

mysql> SELECT * FROM mysql.db WHERE user = 'roundcubeuser'\G
*************************** 1. row ***************************
                 Host: localhost
                   Db: roundcube
                 User: roundcubeuser
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)

Reading up various things in these forums and elsewhere it seems to be something to do with the password hashing system using the wrong number of digits, I have tried everything I can find and nothing seems to work. Could someone point me to what I need to change so that I can get Roundcube running.

Siv

Offline JohnDoh

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 2,845
What versions of PHP and MySQL are you running? Did you see this in the PHP manual?
Quote
MySQL 8

When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

Roundcube Plugins: Contextmenu, SpamAssassin Prefs, and moreā€¦

Offline Siv

  • Newbie
  • *
  • Posts: 7
JohnDoh,

SQL Version is: 8.0.17-0ubuntu2
PHP Version returned by php -v is:
Code: [Select]
php -v
PHP 7.3.11-0ubuntu0.19.10.1 (cli) (built: Oct 24 2019 11:38:49) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.11, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.3.11-0ubuntu0.19.10.1, Copyright (c) 1999-2018, by Zend Technologies

I didn't look at the PHP side of things as I am just following a procedure to set up Roundcube webmail on a server.  I must admit to thinking the issue was more to do with MySQL than PHP.

Siv

Offline Siv

  • Newbie
  • *
  • Posts: 7
I finally fixed the issue, I had to do the following:

1.    Edit the /etc/mysql/my.cnf file and add this section:

   
Code: [Select]
[mysqld]
default-authentication-plugin=mysql_native_password
bind-address            = 127.0.0.1

2.    Remove the user and re-create it as follows:
       Log into the MySQL shell with:
       
Code: [Select]
sudo mysql -u root
       Drop the existing user:
       
Code: [Select]
DROP USER 'roundcubeuser'@'localhost';
       Recreate the user with slightly different parameters:
       
Code: [Select]
CREATE USER 'roundcubeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EnterYourPasswordHere';
       Grant that newly created user permissions on the database in my case that's called "roundcube":
       
Code: [Select]
GRANT ALL PRIVILEGES ON roundcube.* TO roundcubeuser@localhost;
       Then flush the privileges table to make the changes stick:
       
Code: [Select]
flush privileges;
       Finally Exit the MySQL Shell:
       
Code: [Select]
exit;
       Then restart MySQL:
       
Code: [Select]
sudo systemctl restart mysql
       Then Restart Apache:
       
Code: [Select]
sudo systemctl restart apache2
       After that I was able to open Roundcube! Hurrah!