Connect MariaDB through Spring and Hibernate using SSL (Self-Signed) certificates

Question

I have developed the J2EE application with Spring and Hibernate. Now I wanted to connect my application with MariaDB with SSL certificate (without using password) I can able to connect to my database by using following command

mysql -h myhost.com -P3306 -umyuser --ssl-ca ca.crt --ssl-cert app.crt --ssl-key my-key.pem

but I can not able to connect My Application with the database as I am getting following error

Error Log

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2270)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:723)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
    at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 71 ms ago.
    at sun.reflect.GeneratedConstructorAccessor37.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
    at com.mysql.jdbc.ExportControlled.transformSocketToSSLSocket(ExportControlled.java:104)
    at com.mysql.jdbc.MysqlIO.negotiateSSLConnection(MysqlIO.java:4502)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1322)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2198)
    ... 18 more
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
    at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1884)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:276)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:270)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1341)
    at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:153)
    at sun.security.ssl.Handshaker.processLoop(Handshaker.java:868)
    at sun.security.ssl.Handshaker.process_record(Handshaker.java:804)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1016)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1312)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1339)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1323)
    at com.mysql.jdbc.ExportControlled.transformSocketToSSLSocket(ExportControlled.java:89)
    ... 21 more

After reading this question I have added the certificate into keystore with the following command:

keytool -import -noprompt -trustcacerts -alias "My App Certificate" -file ca.crt -keystore /usr/lib/jvm/java-1.6.0-openjdk/jre/lib/security/cacerts -storepass mypassword

still getting following error following is my configuration files

JDBC.properties

jdbc.initialPoolSize=10
jdbc.maxPoolSize=30
jdbc.minPoolSize=5
jdbc.acquireIncrement=3
jdbc.acquireRetryAttempts=0
jdbc.preferredTestQuery=SELECT 1
jdbc.idleConnectionTestPeriod=600
jdbc.numHelperThreads=10
jdbc.debugUnreturnedConnectionStackTraces=true
jdbc.mydbUrl=jdbc:mysql://myhost.com:3306/mydb?verifyServerCertificate=true&useSSL=true&requireSSL=true&characterEncoding=utf8
jdbc.mydbUsername=myuser

datasource-context.xml

<bean id="mydbCommon" class="com.mchange.v2.c3p0.ComboPooledDataSource" abstract="true" destroy-method="close">
    <property name="driverClass" value="${jdbc.rptdbDriverClassName}"/>
    <!-- Common properties for all DS -->
        <property name="initialPoolSize" value="${jdbc.initialPoolSize}"/>
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"/>
        <property name="minPoolSize" value="${jdbc.minPoolSize}"/>
        <property name="acquireIncrement" value="${jdbc.acquireIncrement}"/>
        <property name="acquireRetryAttempts" value="${jdbc.acquireRetryAttempts}"/>
        <property name="preferredTestQuery" value="${jdbc.preferredTestQuery}"/>
        <property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}"/>
        <property name="numHelperThreads" value="${jdbc.numHelperThreads}"/>
        <property name="debugUnreturnedConnectionStackTraces" value="${jdbc.debugUnreturnedConnectionStackTraces}"/>
</bean>
<bean id="mydb" parent="mydbCommon">
        <property name="jdbcUrl" value="${jdbc.mydbUrl}"/>
        <property name="user" value="${jdbc.mydbUsername}"/>
</bean>

Can anyone help to solve this problem. Thanks in Advance...

score 3 · Accepted Answer · answered Jul 01 '14 at 05:17

Following is the steps that I have follow to solve my problem this is not my creativity just gather some steps mentioned in following articles.

References

Connecting Securely Using SSL

The Most Common OpenSSL Commands

In order to make use of SSL encryption to connect to your database, you will need:

The assistance of your database administrator to configure the MySQL server to accept SSL connections.
The SSL PEM public-key file that corresponds to your server's encryption key.

Steps to check your MySQL server has SSL support

Use following command to check your server has support for SSL or not

show variables like 'have_ssl'

If the answer from your server is

NO then the server has no SSL support compiled into it. DISABLED, your system administrator does not start the MySQL server with SSL enabled. YES, you may be able to use SSL.

IF you want ot Configuring MySQL for SSL then you following MySql documentation

6.3.6.2 Configuring MySQL for SSL

Follow below steps to configure your Java environment to use the server's SSL certificate with JDBC.

The system works through two Java truststore files,

One file contains the certificate information for the server (truststore in the examples below)
The other file contains the certificate for the client (keystore in the examples below)

NOTE: All Java truststore files are password protected by supplying a suitable password to the keytool when you create the files. You need the file names and associated passwords to create an SSL connection.

Create truststore File.

You will first need to import the MySQL server CA Certificate into a Java truststore. A sample MySQL server CA Certificate is located in the SSL subdirectory of the MySQL source distribution. This is what SSL will use to determine if you are communicating with a secure MySQL server. Alternatively, use the CA Certificate that you have generated or been provided with by your SSL provider.

NOTE: Assuming that keytool is in your path. The keytool is typically located in the bin subdirectory of your JDK or JRE

shell> keytool -import -alias dbServerCACert -file ca.crt -keystore truststore
    Enter keystore password: truststorepwd 
    Re-enter new password: truststorepwd
    ....    
    Trust this certificate? [no]:  yes
    Certificate was added to keystore

Create Keystore File.

Firts we have to generate an empty Java keystore file using following command

shell>keytool -genkey -alias dbkeystore -keystore my-app.jks
    Enter keystore password:  
    Re-enter new password: 
    What is your first and last name?
      [Unknown]:  mycomp
    What is the name of your organizational unit?
      [Unknown]:  mycomp
    What is the name of your organization?
      [Unknown]:  mycomp
    What is the name of your City or Locality?
      [Unknown]:  Singapore
    What is the name of your State or Province?
      [Unknown]:  Singapore
    What is the two-letter country code for this unit?
      [Unknown]:  SG
    Is CN=mycomp , OU=mycomp, O=mycomp, L=Singapore, ST=Singapore, C=SG correct?
      [no]:  yes

    Enter key password for <keystore>
        (RETURN if same as keystore password):

    shell>keytool -delete -alias dbkeystore -keystore my-app.jks
    Enter keystore password: keystorepwd

Convert your client certificate (my-app.crt) and Private Key(my-app.key) to pkcs12 file using following command

shell>openssl pkcs12 -export -name dbkeystore -in my-app.crt -inkey my-app.key -out my-app.p12
    Enter Export Password: keystorepwd 
    Verifying - Enter Export Password: keystorepwd

Import your p12 file into keystore using following command

keytool -importkeystore -destkeystore my-db.jks -srckeystore my-app.p12 -srcstoretype pkcs12 -alias dbkeystore

SET JVM Veriables

Finally, to use the keystore and truststore that you have generated, you need to set the following system properties of JVM

-Djavax.net.ssl.keyStore=/path/to/my-app.jks
-Djavax.net.ssl.keyStorePassword=keystorepwd
-Djavax.net.ssl.trustStore=/path/to/truststore
-Djavax.net.ssl.trustStorePassword=truststorepwd

Update your Connection URL to support SSL

You will also need to set useSSL to true in your connection parameters for MySQL Connector/J, either by adding useSSL=true to your URL

jdbc:mysql://<HOST>:<PORT>/DB?verifyServerCertificate=true&useSSL=true&requireSSL=true

Run Test Application to check everything is working file

static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://<HOST>:<PORT>/DB?verifyServerCertificate=true&useSSL=true&requireSSL=true"
    public static void main(String[] args) {    
        Connection conn = null;
        try {
            Class.forName(JDBC_DRIVER);
            // STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                }
            }
        }
    }

this steps work for me hope this will help you....!

Connect MariaDB through Spring and Hibernate using SSL (Self-Signed) certificates

1 Answers1