How to change default_authetication_plugin parameter in Azure MySQL DB

Question

Hello Team,

My database error log is getting flooded with below warning messages.
I am not able to change the default_authentication_plugin parameter to handle this problem like how we do in the MySQL standalone server.

[Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

please help.

Answer 1

Hi Srikar Gondesi - UAS Database Administrator •,

Welcome to Microsoft Q&A forum.

As I understand, you are trying to change default_authetication_plugin parameter in Azure MySQL DB.

On the MySQL documentation page https://dev.mysql.com/doc/refman/8.0/en/alter-user.html, we have:

For each affected account, `ALTER USER` modifies the corresponding row in the mysql.user system table to reflect the properties specified in the statement. Unspecified properties retain their current values.

Each account name uses the format described in Section 8.2.4, “Specifying Account Names”. The host name part of the account name, if omitted, defaults to '%'. It is also possible to specify CURRENT_USER or CURRENT_USER() to refer to the account associated with the current session.

In one case only, the account may be specified with the USER() function:

ALTER USER USER() IDENTIFIED BY 'auth_string';

This syntax enables changing your own password without naming your account literally. (The syntax also supports the REPLACE, RETAIN CURRENT PASSWORD, and DISCARD OLD PASSWORD clauses described at [ALTER USER Authentication Options](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-authentication"ALTER USER Authentication Options").)

For [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html"15.7.1.1 ALTER USER Statement") syntax that permits an auth_option value to follow a user value, auth_option indicates how the account authenticates by specifying an account authentication plugin, credentials (for example, a password), or both. Each auth_option value applies only to the account named immediately preceding it.

Following the user specifications, the statement may include options for SSL/TLS, resource-limit, password-management, and locking properties. All such options are global to the statement and apply to all accounts named in the statement.

Example: Change an account's password and expire it. As a result, the user must connect with the named password and choose a new one at the next connection:

ALTER USER 'jeffrey'@'localhost'

IDENTIFIED BY 'new_password' PASSWORD EXPIRE;

Example: Modify an account to use the caching_sha2_password authentication plugin and the given password. Require that a new password be chosen every 180 days, and enable failed-login tracking, such that three consecutive incorrect passwords cause temporary account locking for two days:

ALTER USER 'jeffrey'@'localhost'** **IDENTIFIED WITH caching_sha2_password BY 'new_password'** **PASSWORD EXPIRE INTERVAL 180 DAY** **FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2;

Let us know if this helps.

If not, we will further investigate options to get rid of the warning.

Thanks

Answer 2

Hi Srikar Gondesi - UAS Database Administrator •,

Thanks for the details.

I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks