mba12 mba12 - 5 months ago 33
MySQL Question

Google Cloud SQL with SSL from Compute Engine and/or External Network

I'm trying to connect to an Google Cloud SQL (mysql) instance using SSL. I've enabled two IP addresses and a user with remote access permissions from those IP addresses. I have also generated the certificate files from Google's Developer Console.

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


Using this command from each of the two enabled IP addresses I successfully make a connection.

mysql --ssl-ca=server-ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem --host=xxx.xxx.xxx.xxx --user=username --password


Since the mysql client works I know my firewall settings, users, certs, etc. are set up correctly.

Now I'd like to make a similar connection with java from within an executable jar file.

Following the steps outlined here: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-using-ssl.html

Step 1: keytool -import -alias mysqlServerCACert -file cacert.pem -keystore truststore


NOTE: I substitute server-ca.pem for cacert.pem used in the instructions

Step 2: openssl x509 -outform DER -in client-cert.pem -out client.cert
Step 3: keytool -import -file client.cert -keystore keystore -alias mysqlClientCertificate


These steps create my keystore and truststore files. I associate distinct passwords for
each of these two files as they are generated.

Here is the relevant Java code:

public void testConnection() throws ClassNotFoundException, SQLException,
IllegalAccessException, InstantiationException {

String path = "/home/user/dir/"; // path to keystore and truststore file

System.setProperty("javax.net.ssl.keyStore",path + "keystore");
System.setProperty("javax.net.ssl.keyStorePassword",keyStorePassword);
System.setProperty("javax.net.ssl.trustStore",path + "truststore");
System.setProperty("javax.net.ssl.trustStorePassword",trustStorePassword);

String user = "dbuser";
String password = "dbUserPassword";

Connection conn = null;
int i = 0;
try {
String url = "jdbc:mysql://<databaseip>:3306/<databaseName>"
+ "?verifyServerCertificate=true"
+ "&useSSL=true"
+ "&requireSSL=true";

Class dbDriver = Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, user, password);

ResultSet rs = conn.createStatement().executeQuery(
"SELECT 1 + 1 as val");

while (rs.next()) {
i = rs.getInt(1);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
System.out.println("The MySQL value is: " + i);
}


Again, from both enabled IP addresses I am able to connect with the MySQL command line client with the same user and password being used in the java code.

I receive the following connection exception with the java. Given that the mysql client works with the same user/passwd credential I am a bit suspect of the error message. Any insights much appreciated. Has anyone else got SSL up and working with Google SQL? Thoughts, advice, tricks and tips appreciated. Thanks!

java.sql.SQLException: Access denied for user 'dbuser'@'xxx.xxx.xxx.xxx' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
at com.mysql.jdbc.MysqlIO.negotiateSSLConnection(MysqlIO.java:3988)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1293)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
at com.mysql.jdbc.Connection.<init>(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at com.test.UserData.getConnection(UserData.java:81)
at com.test.UserData.testConnection(UserData.java:52)

Answer

mba12 I had the same problem, thanks for the answer!

So, the solution is here (as you pointed out): How to connect to MySQL with X509 using JDBC?

I also found this one: importing an existing x509 certificate and private key in Java keystore to use in ssl

Just for clarification

javax.net.ssl.keyStore must be client cert + key combined, not just client cert as described in MySQL guide.

To summarize

  1. We have:

server-ca.pem - MySQL CA certificate, can be downloaded from "SSL Configuration -> View Server CA Certificate"

client-cert.pem - client public key, can be downloaded from "Client Certificates -> newly created certificate"

client-key.pem - client private key, can be downloaded only from "New SSL Certificate created dialog box"

Described here: https://cloud.google.com/sql/docs/configure-ssl-instance

Let's save them in server-instance/ folder and create jks/ folder for generated files in step 2.

  1. Create truststore file

    2.1. Copy original JAVA's cacerts to jks/truststore.jks:

    cp $JAVA_HOME/jre/lib/security/cacerts jks/truststore.jks

    2.2. Add MySQL Server CA Certificate / Google Cloud SQL Server CA server-ca.pem to JAVA's default truststore cacerts which we've copied in step 2.1.:

    keytool -importcert -noprompt -trustcacerts -keystore jks/truststore.jks -storepass changeit -alias googlecloudsqlservercacert -file server-instance/server-ca.pem

  2. Create keystore file

    3.1. Convert x509 certificate and private key to a pkcs12 file:

    openssl pkcs12 -export -in server-instance/client-cert.pem -inkey server-instance/client-key.pem -out jks/client.p12

    (enter mandatory password), for example: p4ssw0rd

    3.2. Convert the pkcs12 file to a java keystore:

    keytool -importkeystore -srckeystore jks/client.p12 -srcstoretype PKCS12 -destkeystore jks/keystore.jks -deststoretype JKS

    (enter same passwords), for example: p4ssw0rd

About conversion: http://blog.ejbca.org/2008/02/converting-keystores-between-jks-and.html