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

i'm trying connect google cloud sql (mysql) instance using ssl. i've enabled 2 ip addresses , user remote access permissions ip addresses. have generated certificate files google's developer console.

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

using command each of 2 enabled ip addresses make 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 mysql client works know firewall settings, users, certs, etc. set correctly.

now i'd make similar connection java within executable jar file.

following 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: substitute server-ca.pem cacert.pem used in 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 keystore , truststore files. associate distinct passwords each of these 2 files generated.

here relevant java code:

public void testconnection() throws classnotfoundexception, sqlexception,         illegalaccessexception, instantiationexception {      string path = "/home/user/dir/"; // path keystore , 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 = 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 val");          while (rs.next()) {             = rs.getint(1);         }     } catch (exception ex) {         ex.printstacktrace();     } {         if (conn != null) {             try {                 conn.close();             } catch (exception e) {                 e.printstacktrace();             }         }     }     system.out.println("the mysql value is: " + i); } 

again, both enabled ip addresses able connect mysql command line client same user , password being used in java code.

i receive following connection exception java. given mysql client works same user/passwd credential bit suspect of error message. insights appreciated. has else got ssl , working google sql? thoughts, advice, tricks , tips appreciated. thanks!

java.sql.sqlexception: access denied user 'dbuser'@'xxx.xxx.xxx.xxx' (using password: yes) @ com.mysql.jdbc.sqlerror.createsqlexception(sqlerror.java:946) @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:2985) @ com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:885) @ com.mysql.jdbc.mysqlio.secureauth411(mysqlio.java:3421) @ com.mysql.jdbc.mysqlio.negotiatesslconnection(mysqlio.java:3988) @ com.mysql.jdbc.mysqlio.dohandshake(mysqlio.java:1293) @ com.mysql.jdbc.connection.createnewio(connection.java:2775) @ com.mysql.jdbc.connection.<init>(connection.java:1555) @ com.mysql.jdbc.nonregisteringdriver.connect(nonregisteringdriver.java:285) @ java.sql.drivermanager.getconnection(drivermanager.java:571) @ java.sql.drivermanager.getconnection(drivermanager.java:215) @ com.test.userdata.getconnection(userdata.java:81) @ com.test.userdata.testconnection(userdata.java:52) 

mba12 had same problem, answer!

so, solution here (as pointed out): how connect mysql x509 using jdbc?

i found one: importing existing x509 certificate , private key in java keystore use in ssl

just clarification

javax.net.ssl.keystore must client cert + key combined, not client cert described in mysql guide.

to summarize

1. we have:

server-ca.pem - mysql ca certificate, can downloaded "ssl configuration -> view server ca certificate"

client-cert.pem - client public key, can downloaded "client certificates -> newly created certificate"

client-key.pem - client private key, can downloaded "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 , create jks/ folder generated files in step 2.

2. create truststore file

   2.1. copy original java's cacerts 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 java's default truststore cacerts 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

3. create keystore file

   3.1. convert x509 certificate , private key pkcs12 file:

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

   (enter mandatory password), example: p4ssw0rd

   3.2. convert pkcs12 file java keystore:

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

   (enter same passwords), example: p4ssw0rd

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