In DPA, automatic user creation fails with MySQL 8.0

If you configure the DPA repository on a MySQL 8.0 instance and you choose to allow DPA to create the repository user, the user creation process fails. 

If you are registering a MySQL 8.0 instance for monitoring with DPA and you choose to allow DPA to create the monitoring user, the user creation process fails.

For MySQL 8.0, the default authentication plug-in has changed from mysql_native_password to caching_sha2_password. If you allow a DPA wizard to create the repository user or the monitoring user, the process fails because DPA cannot authenticate a user created with the default option.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

To configure the DPA repository on a MySQL 8.0 instance

1. Create the repository user manually and specify that the mysql_native_password plugin be used to authenticate that user. For example:

   CREATE USER 'userName'@'localhost' IDENTIFIED WITH mysql_native_password BY 'userPassword';

   Note: You can verify user creation by querying the mysql.user table:

   select user,host,plugin from mysql.user;

2. Grant the required privileges to the user. See the GRANT statements in the script available through the Repository Creation Wizard.
3. On step 2 of the Repository Creation Wizard, select Provide the repository user. Then enter the user name and password of the user you created.

To register a MySQL 8.0 instance for monitoring with DPA

1. Create the monitoring user manually and specify that the mysql_native_password plugin be used to authenticate that user. For example:

   CREATE USER 'userName'@'localhost' IDENTIFIED WITH mysql_native_password BY 'userPassword';

   Note: You can verify user creation by querying the mysql.user table:

   select user,host,plugin from mysql.user;

2. Grant the required privileges to the user. See the GRANT statements in the script available through the Register Instance Wizard.
3. On step 2 of the Register Instance Wizard, select I'll create the database user. Then enter the user name and password of the user you created.