Connecting MariaDB with Spring and Hibernate using SSL (self-signed) certificate
I have developed J2EE application with Spring and Hibernate. Now, I want to connect my application with MariaDB using SSL certificate (without password), I can connect to my database using following command
mysql -h myhost.com -P3306 -umyuser --ssl-ca ca.crt --ssl-cert app.crt --ssl-key my-key.pem
But I am not able to connect my app with the database due to the 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 to the keystore using:
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 the following error is my config file
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 with this issue. Thanks in advance...
Below are the steps for the problem I am trying to solve, this is not my creativity, just a collection of some of the steps mentioned in the following article.
refer to
In order to connect to the database with SSL encryption, you will need:
- With the assistance of the database administrator, configure the MySQL server to accept SSL connections.
- The SSL PEM public key file corresponding to the server's encryption key.
Steps to Check if Your MySQL Server Supports SSL
Check if your server supports SSL using the following command
show variables like 'have_ssl'
If the answer given by your server is
No, the server does not have SSL support compiled into it. DISABLED , your system administrator will not start the MySQL server with SSL enabled. Yes , you might be able to use SSL.
If you want to configure MySQL for SSL, follow the MySql documentation
6.3.6.2 Configuring MySQL for MySQL
Follow the steps below to configure your Java environment to use the server's SSL certificate with JDBC.
The system works through two Java Truststore files,
- A file containing the server's certificate information (truststore in the example below)
- Another file contains the client's certificate (the keystore in the example below)
Note: All Java truststore files are password protected by providing the appropriate password to keytool when creating the file. You need the filename and associated password to create an SSL connection.
Create a truststore file.
First, you need to import the MySQL server CA certificate into the Java truststore. Example MySQL server CA certificates are located in the SSL subdirectory of the MySQL source distribution. This is what SSL uses to determine if you are communicating with a secure MySQL server. Alternatively, use a CA certificate generated or provided by your SSL provider.
Note: Assuming keytool is in your path. The keytool is usually located in the bin subdirectory of the 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 a keystore file.
First, we have to generate an empty Java keystore file using the 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 the client certificate (my-app.crt) and private key (my-app.key) to a pkcs12 file using the 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 the p12 file into the keystore using the following command
keytool -importkeystore -destkeystore my-db.jks -srckeystore my-app.p12 -srcstoretype pkcs12 -alias dbkeystore
SET JVM Veriables
Finally, to use the generated keystore and truststore, the following JVM system properties need to be set.
-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 also need to set useSSL to true in the connection parameters of MySQL Connector/J by adding useSSL=true to the URL
jdbc:mysql://<HOST>:<PORT>/DB?verifyServerCertificate=true&useSSL=true&requireSSL=true
Run the test application to check that all files are working
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 step worked for me, hope it helps you.