Connection Java - MySQL : Public Key Retrieval is not allowed

Question

I try to connect MySQL database with Java using connector 8.0.11. Everything seems to be OK, but I get this exception:

Exception in thread "main" java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed at
     com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:108) at 
     com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) at
     com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at     
     com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:862) at 
     com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:444) at
     com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:230) at
     com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:226) at
     com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:438) at
     com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:146) at
     com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:119) at
     ConnectionManager.getConnection(ConnectionManager.java:28) at
     Main.main(Main.java:8)

Here is my Connection Manager class:

public class ConnectionManager {

    public static final String serverTimeZone = "UTC";
    public static final String serverName = "localhost";
    public static final String databaseName ="biblioteka";
    public static final int portNumber = 3306;
    public static final String user = "anyroot";
    public static final String password = "anyroot";
    
    public static Connection getConnection() throws SQLException {
    
        MysqlDataSource dataSource = new MysqlDataSource();
    
        dataSource.setUseSSL( false );
        dataSource.setServerTimezone( serverTimeZone );
        dataSource.setServerName( serverName );
        dataSource.setDatabaseName( databaseName );
        dataSource.setPortNumber( portNumber );
        dataSource.setUser( user );
        dataSource.setPassword( password );
        
        return dataSource.getConnection();
    }
}

Please, provide some code: how are you trying to connect. Stack trace of the exception would be helpful as well. — Sergei Sirik, May 16 '18 at 21:06
For connectrion need add to dburl : allowPublicKeyRetrieval=true — Ryabinin Sergey, Oct 20 '22 at 13:15

score 719 · Accepted Answer · edited Jan 26 '23 at 18:31

719

You should add client option to your mysql-connector allowPublicKeyRetrieval=true to allow the client to automatically request the public key from the server. Note that allowPublicKeyRetrieval=True could allow a malicious proxy to perform a MITM attack to get the plaintext password, so it is False by default and must be explicitly enabled.

See MySQL .NET Connection String Options

you could also try adding useSSL=false when you use it for testing/develop purposes

example:

jdbc:mysql://localhost:3306/db?allowPublicKeyRetrieval=true&useSSL=false

edited Jan 26 '23 at 18:31

Manuel Jordan

15,253
21
95
158

answered May 20 '18 at 19:57

jtomaszk

9,223
2
28
40

6

Well, this worked for me but I am not sure how legit these options are from security perspective. – Priyank Thakkar Jun 20 '18 at 07:10
59

`useSSL=false&allowPublicKeyRetrieval=true` is what I needed only when I tried connecting from `docker_container1` to `docker_container2_mysql(where mysql is installed)` within my local host. While from my host machine to `docker_container2_mysql`, `useSSL=false` is enough. – prayagupa Jun 26 '18 at 05:02
2

allowPublicKeyRetrieval=true&useSSL=false , tank you it works – Martin Klestil Sep 02 '18 at 09:34
5

Can you explain why this is the case? – Capn Sparrow Sep 07 '18 at 04:45
Adding 'allowPublicKeyRetrieval=true' to my JDBC connection string solved my development system's issue after a Windows 10 update yesterday. Guess Microsoft plugged another hole, and the option should be used "for development only" in my view, where SSL is not turned on. – Alz Sep 15 '18 at 13:31
18

My problem is, it was working fine till yesterday. What could have changed overnight? – Sandeep Kumar Dec 31 '19 at 08:14
13

try removing useSSl=false from the url it worked for me – Saroj Kumar Sahoo May 02 '20 at 11:14
2

I was struggling making `dbeaver` connect to *dockerized* `mysql-8` server. `allowPublicKeyRetrieval=true` did the trick for me :+1: – ira Nov 24 '20 at 07:10
2

@SandeepKumar Same thing happened to me. Did you figure out why? And yes, I know a lot of time passed after you commented this, so I don't have high expectations :) – Stefan May 16 '21 at 15:01
How to do this in commandline? For example I am dumping a file or querying via commandline? – Artanis Zeratul Oct 04 '22 at 21:14
with `allowPublicKeyRetrieval=true` it works for me, thnx – Dnyaneshwar Jadhav Mar 06 '23 at 17:50
Had the same issue using spring and it worked :) – G. Joe Mar 21 '23 at 14:41

score 529 · Answer 2 · edited Aug 10 '23 at 12:20

529

For DBeaver users:

Right-click your connection, choose "Edit Connection"
On the "Connection settings" screen (main screen), click on "Edit Driver Settings"
Click on "Driver properties"
Change two properties: "useSSL" and "allowPublicKeyRetrieval"
Set their values to "false" and "true" by double-clicking on the "value" column

edited Aug 10 '23 at 12:20

Gianfranco P.

10,049
6
51
68

answered Jan 16 '20 at 21:31

Javier Aviles

7,952
2
22
28

43

For me, modifying just the "allowPublicKeyRetrieval" field under "Driver Properties" from False to True did the trick. May I ask why add the "useSSL" property and then set it to False? (I too am using DBeaver) – Sandun Dec 15 '20 at 07:07
On Ubuntu 16.04, I had a heck of a time figuring out how to set the value because visually it did not show anything that looked like input was expected or being recorded. I had to double click the value column and trust that it was taking input before hitting enter or clicking out of it and only then did the value I typed in show up. – Stack Underflow Feb 25 '21 at 19:34
7

Is enough set both properties in the `Driver properties` section/tab – Manuel Jordan Mar 29 '21 at 21:38
1

Trying to connect to an out-of-the-box mysql install on a docker container. Doing this made it work so I can connect to the mysql db using dbeaver. – Halfstop May 20 '21 at 04:32
3

This was the correct answer in my case when using DBeaver client – Ara Kokeba Aug 19 '21 at 01:48
In my case "allowPublicKeyRetrieval" properties only accept 'TRUE' (capital latter) – Istiaque Hossain Oct 28 '21 at 04:15
Works for `localhost` database with dbeaver. Thank you – zeg Jan 09 '23 at 09:39
As a DBeaver user, I tried all the options suggested here. But, I still getting the Deinal. DBeaver 23.1.0.202306041918. MySQL Server version: 8.0.33 MySQL Community Server - GPL. – Park JongBum Jul 06 '23 at 06:00

score 80 · Answer 3 · answered Dec 06 '20 at 12:36

80

When doing this from DBeaver I had to go to "Connection settings" -> "SSL" tab and then :

uncheck the "Verify server certificate"
check the "Allow public key retrival"

This is how it looks like.

Note that this is suitable for local development only.

answered Dec 06 '20 at 12:36

Michał Krzywański

15,659
4
36
63

2

this is what worked for me on the latest version of DB Beaver – jAC Mar 22 '21 at 16:51
1

Great. It worked with DBeaver version 21.0.4 – Chandima May 16 '21 at 08:33
this solved my problem – siddhusingh Sep 30 '21 at 09:20
This should be the answer for DBeaver users. – jmizv Dec 09 '21 at 13:44
The main interest is that it seems the SSL option is even required for a `127.0.0.1:3036` local plain SSH tunneled connection. – Artfaith Dec 18 '21 at 08:51
for DBeaver 21.3 this one worked for me without the need to change connection string ... – UnnameDSoS Jan 05 '22 at 14:49

susan097 · Answer 4 · 2022-01-30T07:05:46.017

51

Use jdbc url as :

jdbc:mysql://localhost:3306/Database_dbName?allowPublicKeyRetrieval=true&useSSL=False;

PortNo: 3306 can be different in your configuation

edited Jan 30 '22 at 07:05

answered Jul 02 '18 at 09:28

susan097

3,500
1
23
30

2

A small correction here. The connection property 'useSSL' acceptable values are: 'TRUE', 'FALSE', 'YES' or 'NO'. The value 'false;' is not acceptable. – bluelurker Mar 09 '19 at 18:26
@bluelurker I have updated it. – susan097 Jan 30 '22 at 07:06

score 28 · Answer 5 · answered Nov 14 '18 at 12:37

Alternatively to the suggested answers you could try and use mysql_native_password authentication plugin instead of caching_sha2_password authentication plugin.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';

score 20 · Answer 6 · answered Feb 16 '22 at 09:15

20

I updated this parameter when I faced the issue of "public-key-retrieval-is-not-allowed" with root account.

answered Feb 16 '22 at 09:15

Duc Toan Pham

474
6
6

score 18 · Answer 7 · answered Oct 29 '22 at 13:27

18

Open DBeaver->Edit connection->find driver properties->allowPublicKeyRetrieval=true and useSSl=true

answered Oct 29 '22 at 13:27

Reshnu chandran

338
2
6

score 9 · Answer 8 · answered Jun 02 '22 at 16:23

spring.datasource.url=jdbc:mysql://localhost:3306/database?createDatabaseIfNotExist=true&allowPublicKeyRetrieval=true&useSSL=false

You can insert this line to your applications.properties file and this means,

spring.datasource.url=jdbc:mysql://localhost:3306/ This one uses mysql as the database service. I think this can changed by using relavent name and the port of your database name.
database?createDatabaseIfNotExist=true = use the database named database if you haven't already make a database like that, make a new one.
allowPublicKeyRetrieval=true = to allow the client to automatically request the public key from the server. (This part might be additional)
useSSL=false = This will disable SSL and also suppress the SSL errors

Furthermore, be alert about the spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect property in the same file.

Finally check whether you've added following dependency in dependencies in your pom.xml file.

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version>
</dependency>

score 8 · Answer 9 · answered Jun 12 '20 at 10:32

First of all, please make sure your Database server is up and running. I was getting the same error, after trying all the answers listed here I found out that my Database server was not running.

You can check the same from MySQL Workbench, or Command line using

mysql -u USERNAME -p

This sounds obvious, but many times we assume that Database server is up and running all the time, especially when we are working on our local machine, when we restart/shutdown the machine, Database server will be shutdown automatically.

I logged into mysql via terminal and tried again and it worked. — Martin Karari, Dec 04 '21 at 19:06

score 7 · Answer 10 · answered Aug 26 '18 at 15:39

7

I solve this issue using below configuration on spring boot framework

spring.datasource.url=jdbc:mysql://localhost:3306/db-name?useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

answered Aug 26 '18 at 15:39

duyuanchao

3,863
1
25
16

right, simplest could be your local mysql password is something different that what is configured in application.properties .. go check and change that. This was the case with mw – Rajni Gangwar Mar 09 '21 at 12:28

score 6 · Answer 11 · answered Sep 10 '20 at 17:36

6

This also can be happened due to wrong user name or password. As solutions I've added allowPublicKeyRetrieval=true&useSSL=false part but still I got error then I checked the password and it was wrong.

answered Sep 10 '20 at 17:36

HashanR

194
3
18

score 6 · Answer 12 · answered Sep 09 '21 at 14:53

6

Another way, on DBeaver.

You can edit the connection of a database, go to SSL tab in connection settings. There's a checkbox "allow public key retrieval" mark it as true. That'll sove the issue.

answered Sep 09 '21 at 14:53

Ravi

211
3
11

score 4 · Answer 13 · answered Nov 09 '21 at 01:27

In MySQL 8.0 the default authentication plugin was changed from mysql_native_password to caching_sha2_password. See https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password for more information about this change.

What that means is that in order to use caching_sha2_password the connection must do one of the following:

use a secure connection (useSSL=true)
use an unencrypted connection that supports password exchange using an RSA key pair (useSSL=false and additional configuration parameters - see https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html)

You have a few options:

ALTER the users to use the mysql_native_password plugin (like how it was doing historically and will also work with older clients / connections which don't support caching_sha2_password)
useSSL=true
useSSL=false and configure public key retrieval (this doesn't mean using allowPublicKeyRetrieval=true which would avoid the error - but defeats the objective of this extra security and is slow - it does mean using something like server-public-key-path to point to the client side copy of the public key)

score 3 · Answer 14 · answered Dec 16 '18 at 18:49

The above error in my case was actually due to the wrong username and password. Solving the issue: 1. Go to the line DriverManager.getConnection("jdbc:mysql://localhost:3306/?useSSL=false", "username", "password"); The fields username and password might be wrong. Enter the username and password which you use to start your mysql client. The username is generally root and password is the string which you enter when a screen similar to this appears Startup screen of mysql

Note: The portname 3306 might be different in your case.

score 2 · Answer 15 · answered Feb 03 '19 at 21:20

I found this issue frustrating because I was able to interact with the database yesterday, but after coming back this morning, I started getting this error.

I tried adding the allowPublicKeyRetrieval=true flag, but I kept getting the error.

What fixed it for me was doing Project->Clean in Eclipse and Clean on my Tomcat server. One (or both) of those fixed it.

I don't understand why, because I build my project using Maven, and have been restarting my server after each code change. Very irritating...

score 2 · Answer 16 · answered Nov 10 '19 at 08:20

2

Update the useSSL=true in spring boot application connection with mysql;

jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&useSSL=true&useLegacyDatetimeCode=false&serverTimezone=UTC

answered Nov 10 '19 at 08:20

Bheem Singh

607
7
13

Perfect - i had this error after server was upgraded to mysql 8 from mysql 5. useSSL=true fixed the error – Sudhir N Apr 19 '23 at 10:45

score 1 · Answer 17 · edited Nov 25 '18 at 02:41

1

This solution worked for MacOS Sierra, and running MySQL version 8.0.11. Please make sure driver you have added in your build path - "add external jar" should match up with SQL version.

String url = "jdbc:mysql://localhost:3306/syscharacterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";

edited Nov 25 '18 at 02:41

Stephen Rauch

47,830
31
106
135

answered Nov 25 '18 at 02:15

surendrapanday

530
3
13

score 1 · Answer 18 · answered Dec 01 '18 at 01:17

1

In my case it was user error. I was using the root user with an invalid password. I am not sure why I didn't get an auth error but instead received this cryptic message.

answered Dec 01 '18 at 01:17

juice

1,651
15
11

score 1 · Answer 19 · answered Jul 15 '19 at 23:26

If you are getting the following error while connecting the mysql (either local or mysql container running the mysql):

java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

Solution: Add the following line in your database service:

command: --default-authentication-plugin=mysql_native_password

score 1 · Answer 20 · answered Apr 24 '23 at 15:52

For folks that are using a .xml file to store their database connection details, modifying your jdbcUrl to below should make your connection from your test tomcat server to your database valid again:

jdbcUrl="jdbc:mysql://localhost:3306/DB_NAME?autoReconnect=true&amp;allowPublicKeyRetrieval=true&amp;useSSL=false"

score 1 · Answer 21 · edited May 29 '23 at 16:49

I managed to connect to MySQL using SSL through DBeaver with allowPublicKeyRetrieval=false and useSSL=true driver settings, which is preferred solution from security perspective, so I wanted to share my solution here.

To establish a successful SSL connection, you need to provide the correct paths to the certificates in your client (e.g., DBeaver).

MySQL generates the certificates by default, so you don't need to generate them manually.

They are located in the /var/lib/mysql directory.

This is from official MySQL documentation:

Automatic SSL and RSA File Generation

For MySQL distributions compiled using OpenSSL, the MySQL server has the capability of automatically generating missing SSL and RSA files at startup. The auto_generate_certs, sha256_password_auto_generate_rsa_keys, and caching_sha2_password_auto_generate_rsa_keys system variables control automatic generation of these files. These variables are enabled by default. They can be enabled at startup and inspected but not set at runtime.

At startup, the server automatically generates server-side and client-side SSL certificate and key files in the data directory if the auto_generate_certs system variable is enabled, no SSL options other than --ssl are specified, and the server-side SSL files are missing from the data directory. These files enable encrypted client connections using SSL; see Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”.

If you can't find the certificates there, it's likely that you have modified the default behavior of MySQL using system variables. In that case, please refer to the documentation to learn how to resolve this issue.

The required certificate files for SSL connection are as follows:

CA Certificate: /var/lib/mysql/ca.pem
Client Certificate: /var/lib/mysql/client-cert.pem
Client Private Key: /var/lib/mysql/client-key.pem

By providing these paths in the SSL configuration of your client application, you should be able to connect to MySQL using SSL successfully.

I hope this helps others who might encounter similar SSL connection issues with MySQL and DBeaver.

P.S If you use Docker Image, you need to copy certificates and key from docker container to your local computer, so MySQL client (e.g DBeaver) could find them there.

score 0 · Answer 22 · answered May 15 '19 at 05:13

0

Give connection URL as jdbc:mysql://localhost:3306/hb_student_tracker?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC

answered May 15 '19 at 05:13

samit tiwary

99
1
3

how about in command line or connecting through commandline? – Artanis Zeratul Oct 04 '22 at 22:08

score 0 · Answer 23 · answered Feb 20 '20 at 09:26

I was also facing such an issue while dockerizing our existing application. The solution si to add allowPublicKeyRetrieval connection option of MySQL with a value of true to the JDBC connection string. If that is not working , try adding useSSL option to false as well .

The resultant string would look like this :

jdbc:mysql://<database server ip>:3306/databaseName?allowPublicKeyRetrieval=true&useSSL=false

score 0 · Answer 24 · answered May 03 '21 at 10:59

0

Setting Server Time Zone to my local place, fixed the issue.

answered May 03 '21 at 10:59

Manohar Reddy Poreddy

25,399
9
157
140

score 0 · Answer 25 · answered Jul 16 '21 at 18:22

0

My problem was in pom.xml (spring boot). My pom.xml had two dependencies entries for different databases. Make sure to keep only the MySQL dependency and remove any other database dependency entry.

answered Jul 16 '21 at 18:22

Savrige

3,352
3
32
38

Connection Java - MySQL : Public Key Retrieval is not allowed

25 Answers25

Linked