CODEWITHSUNDEEP
mysqlmysql-workbench
Matthew van Boheemen
Matthew van Boheemen

Reputation: 1335

MySQL changing authentication type from standard to caching_sha2_password

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?

Upvotes: 42

Views: 139972

Answers (6)

Dibyajyoti Nayak
Dibyajyoti Nayak

Reputation: 19

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.

Upvotes: 1

gem007bd
gem007bd

Reputation: 1165

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}';

Upvotes: 13

Elias
Elias

Reputation: 126

Run 

mysql> CREATE USER ‘username’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

Upvotes: 10

Marius Van Der Berg
Marius Van Der Berg

Reputation: 91

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.

Upvotes: 8

SalkinD
SalkinD

Reputation: 783

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.

Upvotes: 4

Meko Perez Estevez
Meko Perez Estevez

Reputation: 922

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.

Upvotes: 70

Related Questions