ProxySQL ssl by command connection works but through Java Client doesn't

Question

I can connect through ProxySQL (using ssl) to a MariaDB instance:

E:\>mysql -h 192.168.33.180 -P 6033 -u user --password=password --ssl 
Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MySQL connection id is 38364 Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

But when I try to connect using Java Client:

    private static final String MARIADB_URL = "jdbc:mariadb://192.168.33.180:6033/offenderconnect";
    private static final String MARIADB_USER = "user";
    private static final String MARIADB_PASSWORD = "password";

    private static final Properties properties = new Properties();

    static {

        properties.put("useSSL", "true");
        properties.put("user", MARIADB_USER);
        properties.put`enter code here`("password", MARIADB_PASSWORD);

    }
    ...

    try (Connection connectionMariaDB = DriverManager.getConnection(MARIADB_URL, properties)) {

The Exception Output is:

java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=192.168.33.180)(port=6033)(type=master) : Could not connect to 192.168.33.180:6033 : PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73) at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:194) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1394) at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635) at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:150) at org.mariadb.jdbc.Driver.connect(Driver.java:89) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189) at com.gtl.datamigration.App.checkTable(App.java:211) at com.gtl.datamigration.App.lambda$5(App.java:447) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to 192.168.33.180:6033 : PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73) at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:185) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:575) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1389) ... 10 more Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:131) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:349) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:292) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:287) at java.base/sun.security.ssl.CertificateMessage$T13CertificateConsumer.checkServerCerts(CertificateMessage.java:1356) at java.base/sun.security.ssl.CertificateMessage$T13CertificateConsumer.onConsumeCertificate(CertificateMessage.java:1231) at java.base/sun.security.ssl.CertificateMessage$T13CertificateConsumer.consume(CertificateMessage.java:1174) at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:392) at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:443) at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:421) at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:182) at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:171) at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1418) at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1324) at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:440) at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:411) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.sslWrapper(AbstractConnectProtocol.java:658) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:541) ... 11 more Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:439) at java.base/sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:306) at java.base/sun.security.validator.Validator.validate(Validator.java:264) at java.base/sun.security.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:313) at java.base/sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:222) at java.base/sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:129) at java.base/sun.security.ssl.CertificateMessage$T13CertificateConsumer.checkServerCerts(CertificateMessage.java:1340) ... 24 more Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target at java.base/sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141) at java.base/sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126) at java.base/java.security.cert.CertPathBuilder.build(CertPathBuilder.java:297) at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:434) ... 30 more

I try this without luck.

Using

-Djavax.net.debug=all

The following additional info appears:

javax.net.ssl|DEBUG|0D|pool-1-thread-1|2021-06-23 15:05:34.869 COT|CertificateMessage.java:1171|Consuming server Certificate handshake message ( "Certificate": {   "certificate_request_context": "",   "certificate_list": [   {
    "certificate" : {
      "version"            : "v3",
      "serial number"      : "60 D2 2A 99",
      "signature algorithm": "SHA256withRSA",
      "issuer"             : "CN=ProxySQL_Auto_Generated_CA_Certificate",
      "not before"         : "2021-06-22 13:23:21.000 COT",
      "not  after"         : "2031-06-20 13:23:21.000 COT",
      "subject"            : "CN=ProxySQL_Auto_Generated_Server_Certificate",
      "subject public key" : "RSA"}
    "extensions": {
      <no extension>
    }   },   {
    "certificate" : {
      "version"            : "v3",
      "serial number"      : "60 D2 2A 99",
      "signature algorithm": "SHA256withRSA",
      "issuer"             : "CN=ProxySQL_Auto_Generated_CA_Certificate",
      "not before"         : "2021-06-22 13:23:21.000 COT",
      "not  after"         : "2031-06-20 13:23:21.000 COT",
      "subject"            : "CN=ProxySQL_Auto_Generated_CA_Certificate",
      "subject public key" : "RSA",
      "extensions"         : [
        {
          ObjectId: 2.5.29.19 Criticality=true
          BasicConstraints:[
            CA:false
            PathLen: undefined
          ]
        }
      ]}
    "extensions": {
      <no extension>
    }   }, ] } )

Please give me some hints to try.

The stack trace hints that the JVM does not trust the Remote certificates — Thorbjørn Ravn Andersen, Jun 23 '21 at 20:34
@ThorbjørnRavnAndersen, thanks for your note. Do you know how can I tell JVM to trust this remote certificate? — davigre, Jun 24 '21 at 13:50

score 1 · Answer 1 · answered Jun 25 '21 at 16:25

Finally it works:

We need to put

trustServerCertificate = true and usePipelineAuth = false

static {
        
    properties.put("user", MARIADB_USER);
    properties.put("password", MARIADB_PASSWORD);
    properties.put("useSSL", "true");
    properties.put("trustServerCertificate", "true");
    properties.put("usePipelineAuth", "false");

}

ProxySQL ssl by command connection works but through Java Client doesn't

1 Answers1