SSL connection to MySQL server with Java

Question

I am trying to connect to a MySQL server using Java over SSL. I am getting the following exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Cannot connect to MySQL server on www.mysite.com:3306.

Make sure that there is a MySQL server running on the machine/port you are trying to connect to and that the machine this software is running on is able to connect to this host/port (i.e. not firewalled). Also make sure that the server has not been started with the --skip-networking flag.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
at com.mysql.jdbc.Util.getInstance(Util.java:382)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:827)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:378)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Caused by: java.lang.UnsupportedOperationException: The method shutdownInput() is not supported in SSLSocket
at sun.security.ssl.BaseSSLSocketImpl.shutdownInput(Unknown Source)
at com.mysql.jdbc.MysqlIO.forceClose(MysqlIO.java:506)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2404)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2163)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
... 25 more

This is my code:

properties = new Properties();
properties.put("user", "myuser");
properties.put("password", "mypassword");
properties.put("useSSL", "true");
System.setProperty("javax.net.ssl.keyStore", "keystore.jks");
System.setProperty("javax.net.ssl.keyStorePassword", "keystorepass");
System.setProperty("javax.net.ssl.trustStore", "truststore.jks");
System.setProperty("javax.net.ssl.trustStorePassword", "truststorepass");
connection = DriverManager.getConnection("jdbc:mysql://www.mysite.com:3306/mydatabase", properties);

Details

Keystore and truststore creation

I generated the keystore and truststore as follows:

openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem > keystore.p12
keytool -importkeystore -srckeystore keystore.p12 -srcstoretype pkcs12 -destkeystore keystore.jks -deststoretype JKS

openssl x509 -outform der -in ca.pem -out ca.der
keytool -import -file ca.der -alias myCA -keystore truststore.jks

Original certificates

This is a list of my certificates:

• ca.pem
• client-key.pem
• client-cert.pem
• server-key.pem
• server-cert.pem

Connecting without SSL

The following code works fine, just to demonstrate I can connect to the MySQL server in Java:

properties = new Properties();
properties.put("user", "myuser");
properties.put("password", "mypassword");
connection = DriverManager.getConnection("jdbc:mysql://www.mysite.com:3306/mydatabase", properties);

Connection over SSL works when using MySQL on command line

I start the MySQL server with the command:

mysqld --ssl-ca=ca.pem --ssl-cert=server-cert.pem --ssl-key=server-key.pem

I then connect to it using the MySQL command:

mysql --host=www.mysite.com --user=myuser --password=mypassword --database=mydatabase --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

This works, and after querying SHOW STATUS LIKE 'Ssl_cipher' I get the correct result:

+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

Leduc

have you checked the mysql documentation page for ssl connection? – Repeat

@Repeat That is the page that got me this far, yes. Side comment: I have since completely overthrown this code and changed so much it's hard to provide good feedback, but my code now works. I'll add a comment below detailing an important feature that potential readers should check. – Leduc

What is the origin of those certificates you list? – Burkes

@Burkes I generated them using OpenSSL. – Leduc

even the ca.pem? – Burkes

@Burkes Yes, even the ca.pem – Leduc

Hi @Infima, as you have worked with similar task, can you guide me on this #60333747 – Wealth

Answer 1

If you are having this problem, at least make sure that your CA certificate is imported into the keystore of the JRE!

keytool -import -noprompt -trustcacerts -alias <uniqueCAAlias> -file ca.pem -keystore <pathtocacerts>

(You may have to change the name of the "ca.pem" file to your own CA certificate filename)

Remember to keep the <uniqueCAAlias> something unique. Also, change the <pathtocacerts> so that it matches the path of your JVM. Note that this is most likely the highest version JRE, but it may also be the JRE of an SDK or a different JRE that is installed (like "C:\Program Files\Java\jre1.8.0_25\lib\security\cacerts")

You will need to enter a password for the JVM truststore: this password is always 'changeit'.

I can't guarantee this will solve the problem, but it was a major step I took while working on this, and I got it working in the end.

Leduc

Something i'd like to add: it probably makes sense to install the certificates to every JRE's truststore. Also make sure to keep the truststores up to date, eg. when upgrading to a new JRE. Also you mention the default password for the JVM truststore. But this can be changed by any user with propper permissions. – Clastic