来自计算引擎和/或外部网络的带有SSL的Google Cloud SQL [英] Google Cloud SQL with SSL from Compute Engine and/or External Network

问题描述

我试图使用SSL连接到Google Cloud SQL（mysql）实例。我已经启用了两个IP地址和一个具有来自这些IP地址的远程访问权限的用户。我还从Google的开发者控制台生成了证书文件。

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

使用此命令从两个启用的IP地址中的每一个中成功建立连接。

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

由于mysql客户端的工作原理，我知道我的防火墙设置，用户，证书等都设置正确。

现在我想创建一个类似的连接在可执行jar文件中使用java。

遵循以下步骤： http://dev.mysql.com/doc/connector-j/en/connector-j-reference-using-ssl.html

 步骤1：keytool -import -alias mysqlServerCACert -file cac ert.pem -keystore truststore 
  

注意：我将server-ca.pem替换为cacert.pem用于指令

 步骤2：openssl x509 -outform DER -in client-cert.pem -out client.cert 
步骤3：keytool -import -file client.cert -keystore keystore -alias mysqlClientCertificate 
  

这些步骤会创建我的密钥库和信任库文件。在生成这两个文件时，我将这两个文件中每一个的
关联到不同的密码。

以下是相关的Java代码：

  public void testConnection（）throws ClassNotFoundException，SQLException，
 IllegalAccessException，InstantiationException {
 
 String path =/ home / user / DIR /; // keystore和truststore文件的路径
 
 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; 
 
连接conn = null; 
 int i = 0; 
尝试{
 String url =jdbc：mysql：//< databaseip>：3306 /< databaseName> 
 +？verifyServerCertificate = true
 +& useSSL = true
 +& requireSSL = true; 
 
类dbDriver = Class.forName（com.mysql.jdbc.Driver）; 
 conn = DriverManager.getConnection（url，user，password）; 
 
 ResultSet rs = conn.createStatement（）。executeQuery（
SELECT 1 + 1 as val）; （rs.next（））{
 i = rs.getInt（1）; 
 
 while 
} 
} catch（Exception ex）{
 ex.printStackTrace（）; 
} finally {
 if（conn！= null）{
 try {
 conn.close（）; 
} catch（Exception e）{
 e.printStackTrace（）; 
 
 
 
 System.out.println（MySQL的值是：+ i）; 
} 
  

同样，从两个启用的IP地址中，我都可以连接MySQL命令在Java代码中使用相同的用户和密码。

我收到以下java连接异常。鉴于mysql客户端使用相同的用户/密码凭证，我有点怀疑该错误消息。任何见解都非常感谢。是否有其他人使用SSL并使用Google SQL？想法，建议，技巧和提示表示赞赏。谢谢！

  java.sql.SQLException：拒绝用户'dbuser'@'xxx.xxx.xxx.xxx'的访问权限（使用密码：是）
 at com.mysql.jdbc.SQLError.createSQLException（SQLError.java:946）
 at 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）
 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）
在java.sql.DriverManager.getConnection（DriverManager.java:571）
在java.sql.DriverManager.getConnection（DriverManager.java:215）
在com.test.UserData.getConnection（UserData。 java：81）
 at com.test.UserData.testConnection（Use rd.java:52）
  

解决方案

mba12 我有同样的问题，感谢您的答案！

因此，解决方案在这里（如您所指出的）：
如何使用JDBC与X509连接MySQL？

我也发现了这个：

$ b中的私有密钥$ b

仅供说明

javax.net.ssl.keyStore 必须是客户端证书+密钥的组合，而不仅仅是MySQL指南中描述的客户端证书。

总结

1. 我们有：
   




server-ca。 PEM - MySQL CA证书，可以从SSL配置 - >查看服务器CA证书下载



client-cert.pem - 客户端公钥，可以从客户端证书 - >新创建的证书下载



client-key.pem - 客户端私钥，只能从新SSL证书创建对话框下载。





这里描述： https://cloud.google.com/sql/docs/configure-ssl-instance





让我们将它们保存在 server-instance / 文件夹中，并在步骤2中为生成的文件创建 jks / 文件夹。




2. 创建 truststore file
   
   

   2.1。将原始JAVA的 cacerts 复制到 jks / truststore.jks ：
   
   < 2.2 $。code $ c $ $ JAVA_HOME / jre / lib / security / cacerts jks / truststore.jks

   
   
   

   2.2。将MySQL服务器CA证书/ Google Cloud SQL Server CA server-ca.pem 添加到JAVA的默认信任库 cacerts 在步骤2.1中复制：
   
   

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




3. 创建 keystore file
   
   

   3.1。将x509证书和私钥转换为pkcs12文件：
   
   

   openssl pkcs12 -export -in服务器实例/ client-cert.pem -inkey服务器-instance / client-key.pem -out jks / client.p12

   
   
   

   （输入强制密码），例如： p4ssw0rd

   
   
   

   3.2。将pkcs12文件转换为java密钥库：

   
   
   

   keytool -importkeystore -srckeystore jks / client.p12 -srcstoretype PKCS12 -destkeystore jks / keystore.jks例如：

   
   
   

   （输入相同的密码）关于转换：
   





关于转换：
http://blog.ejbca.org/2008/02/converting-keystores-between-jks-and.html




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)

解决方案

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.

2. 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

3. 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

这篇关于来自计算引擎和/或外部网络的带有SSL的Google Cloud SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！