Joomla! Documentation™

MySQL default authentication plugin issue

It's not possible to connect to a MySQL 8 Database using Joomla 3.8, 3.9 or 4.0. The reason is that MySQL 8 has a lot of changes under the hood. One change that affects Joomla is the default authentication plugin which is sha256_password instead of mysql_native_password. The native PHP MySQL-Driver don't support MySQL 8 with this plugin yet other programming languages like GO or PERL are struggling too. PHP 7.3 (alpha) is supporting MySQL 8 though.

How MySQL default authentication plugin works

The advantage of mysql_native_password is that it support challenge-response mechanism which is very quick and does not require encrypted connection. However, mysql_native_password relies on SHA1 algorithm and NIST has suggested to stop using it.

Further, if two user accounts use the same password, mysql_native_password transformation is the same in the mysql.user table. Although the hash does not expose information about the actual password, it still tells which two users use the same password. To avoid that, salt should be used. Salt is basically a random number that is used as one of the inputs to cryptographic hash functions used to transform user passwords. Since salt is random and different for each execution, even if two users use the same passwords, the end result of transformation would look very different.

Since MySQL 5.6, sha256_password authentication plugin is supported. It uses multiple rounds of SHA256 hash on a salted password to make sure that the hash transformation is more secure. However, it requires either encrypted connections or support for an RSA key pair. So, while password security is stronger, secure connections and multiple rounds of hash transformations require more time in the authentication process.

caching_sha2_password tries to combine the best of both worlds.