36

I've setup a new MySQL instance on a computer and every time I add a user it sets the Authentication Type to caching_sha2_password.

This happens even if I set the Authentication Type to "Standard", it then changes it when I save the user. I've also changed the default authentication plug in to "mysql_native_password", but it still keeps doing it.

With it using the caching_sha2_password I can't connect to the database from .net core as I get an error stating:

MySqlException: Authentication method 'caching_sha2_password' not supported by any of the available plugins

How do I get it to save users with the Standard authentication type?

akshat
  • 1,219
  • 1
  • 8
  • 24
Matthew van Boheemen
  • 1,087
  • 3
  • 13
  • 21
  • If you use the newer authentication method, it also prevents Loopback (a node.js library) from accessing MySQL databases. – Paul Chernoch Jun 28 '18 at 14:12

6 Answers6

67

I had the same problem today. The only way I found to fix it was:

  1. executing the install file
  2. select "Reconfigure" over the mysql server
  3. In Authentication Method tab, select "Use Legacy Authentication Method"

It should fix your problem.

Meko Perez Estevez
  • 892
  • 8
  • 6
  • 1
    Thanks, @Meko for the tip. I just installed MySQL 8.0.11 and my Toad for MySQL 8.0 refused to connect with the error. Now after the reconfiguration, it works. – Prabhat May 16 '18 at 11:56
  • 2
    If you're on OSX the option should be in the Mysql Pane under system preferences. Press the button to stop the server, then press the "Initialize Database" button. Then choose: "Use Legacy Password Encryption". Then start the server again. – Jesse Greathouse Oct 16 '18 at 04:56
  • @Meko Perez, I am using node js with MySQL. Even I had the issues with authentication so I changed to legacy auth method and it worked. But, after that if I restarted my system and now again the auth is failing. I think once the restart of MySQL server is happening it is probably switching to the recommended approach to use latest auth. Anyone who got this issue resolved? – Chaitanya Sep 12 '19 at 11:49
9

Run 

mysql> CREATE USER ‘username’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
Butiri Dan
  • 1,759
  • 5
  • 12
  • 18
Elias
  • 116
  • 1
  • 2
9

Reset the user password with mysql_native_password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{NewPassword}';

gem007bd
  • 1,085
  • 13
  • 11
7

For those of you that are using MySQL Workbench 8.0 CE and still struggling, the following worked for me:

  1. Under Navigator, click on Administration Administration under Navigator preview, then select Options File.
  2. Administration - Options File will open. Under the security tab, scroll down until you see "default authentication plugin". Mine was on caching_sha2_password. Changing it to mysql_native_password, clicking apply and restarting MySQL Workbench worked for me.

Administration - Options File preview

I had to delete and re-add the user. It did not automatically change the user's authentication type back to caching_sha2_password, it kept it on standard.

Marius Van Der Berg
  • 71
  • 1
  • 2
  • 4
  • This was useful for me, as workbench worked, but sequel pro didn't based on the plugin. I prefer Sequel Pro for everyday use, as it has the basics in a cleaner layout. – atomicDroid Jun 29 '20 at 14:52
4

You can also do it with MySQL Workbench:

"Users and Privilegues" -> "Add Account" -> Authentication Type: "Standard"

This user now can login with default login, i.e. for phpMyAdmin.

SalkinD
  • 753
  • 9
  • 23
  • Thanks, I did the above steps, and it didn't work, because the account I used in my connectionstring was configured to 'caching_sha2_password' in workbench, only with your help I noticed it. – Anderson Paiva Sep 04 '18 at 13:40
1
import mysql.connector

def connect():
    conn = mysql.connector.connect(host='localhost',
    database='mydb',
    user='root_new',
    password='root_new')
    if conn.is_connected():
       print('Connected to MySQL database')

if name == 'main':
   connect()

Output : Connected to MySQL database



1.Stop database server in preferences for MacOS.
2.initialise DB with legacy authentication.
3.Open mysqlWorkBench and Create a new user with standard authentication.
4.Create a new schema(DB) in sqlWorkbench.
5.Execute python Code in Eclipse.
Dibyajyoti Nayak
  • 19
  • 3