Broadcom API Gateway: MySQL JDBC Connection error "Public Key Retrieval is not allowed"

Products

• CA API Gateway

Issue/Introduction

While attempting to set up a MySQL JDBC connection from Policy Manager, the TEST button fails with the following error:

Environment

Release : 10.1

Cause

The reason for the error "Testing failed: invalid connection properties settings. Public Key Retrieval is not allowed" is likely caused by the fact that the DB's User designated to connect (via JDBC) to MySQL, was created in the database with caching_sha2_password authentication plugin rather than mysql_native_password.

For caching_sha2_password plugin, you must use either a secure connection or an unencrypted connection that supports password exchange using an RSA key pair.

If the connection is indeed unencrypted (skip-ssl parameter in my.cnf), the connection will try to retrieve an RSA Key to use for authentication. Hence the error.

In order to verify what authentication plugin the DB User is currently using, we can execute a MySQL query like 

# select user, host, plugin from mysql.user

Here is an example of output showing "dbuser" set with caching_sha2_password :

Resolution

There are several methods that can be used to fix this (please chose the one that suits you the most)

1. Enable and correctly implement SSL in MySQL

2. Create a DB User and configure it to use mysql_native_password authentication plugin. 

3. Add the following parameter to the JDBC URL: useSSL=false&allowPublicKeyRetrieval=true