MySQL JDBC over SSL problem

Question

How one can make MySQL JDBC work over SSL (with X509 certificates validation)?

I've got self-created certificates as described in MySQL manual, in Using SSL for Secure Connections, specifically:

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 1000 \
         -key ca-key.pem > ca-cert.pem

# Create server certificate
shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Create client certificate
shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

After issuing GRANT ALL ON *.* TO vic@localhost IDENTIFIED BY '12345' REQUIRE X509; I am able to connect to MySQL over command-line:

mysql -u vic -p --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem mysql
...

mysql>  SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

However, when I try to run Java test, I get auth failure: Access denied for user 'vic'@'localhost' (using password: YES). Code follows:

public class Launcher {
    public static void main(String[] args) throws DbException, SQLException, ClassNotFoundException {
        StringBuffer sb = new StringBuffer("jdbc:mysql://localhost/bt?useSSL=true&");
        sb.append("user=vic&password=12345&");
        sb.append("clientCertificateKeyStorePassword=123456&");
        sb.append("clientCertificateKeyStoreType=JKS&");
        sb.append("clientCertificateKeyStoreUrl=file:///home/vic/tmp/client-keystore&");
        sb.append("trustCertificateKeyStorePassword=123456&");
        sb.append("trustCertificateKeyStoreType=JKS&");
        sb.append("trustCertificateKeyStoreUrl=file:///home/vic/tmp/ca-keystore");

        Class.forName("com.mysql.jdbc.Driver");
        Connection c = DriverManager.getConnection(sb.toString());

        Statement st = c.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM test_table");
        while (rs.next()) {
            System.out.println(rs.getInt("id"));
        }
        rs.close(); st.close(); c.close();
    }
}

And here's how I prepared Java keystore files:

keytool -import -alias mysqlServerCACert -file ca-cert.pem -keystore ca-keystore
keytool -import -file client-cert.pem -keystore client-keystore -alias client-key

UPDATE I am able to connect over SSL via JDBC if I use 'root' user instead of 'vic'. Then following code

    Statement st = c.createStatement();
    ResultSet rs = st.executeQuery("SHOW STATUS LIKE 'Ssl_cipher';");
    while (rs.next()) {
        System.out.println(rs.getString(1));
        System.out.println(rs.getString(2));
    }

prints

Ssl_cipher
DHE-RSA-AES128-SHA

But I can't use root in production, and I wonder why JDBC uses AES128, whereas command-line mysql client uses AES256.

UPDATE2 After I changed ssl_type to X509 in user table for root@localhost, requesting full auth of client, I get the same behavior for root as for vic -- can't login via JDBC.

UPDATE3 If I use REQUIRE SSL instead of REQUIRE X509 in GRANT statement, code works. Is it possible to make X509 work?

See http://stackoverflow.com/questions/11509751/how-to-connect-to-mysql-with-x509-using-jdbc — Mark Matten, Jul 23 '12 at 20:14

score 5 · Answer 1 · answered Jul 03 '13 at 15:28

Support for self-signed certificates was recently added to the MariaDB JDBC driver (which also works for connecting to MySQL). The latest version (1.1.3 as of writing this) also allows you to directly specify the server certificate at runtime so that you do not need to configure key stores or import certificates in advance.

The two properties to set are useSSL and serverSslCert. The latter can be either the certificate itself (a String value) or a path to a file that contains the certificate (either full path or classpath relative):

String url = "jdbc:mysql://" + host + ":" + port + "/" + database;
Properties info = new Properties();
info.setProperty("user", username);
info.setProperty("password", password);
info.setProperty("useSSL", "true");
info.setProperty("serverSslCert", "classpath:server.crt");
Connection conn = DriverManager.getConnection(url, info);

For a full working example of how to connect see here: https://github.com/properssl/java-jdbc-mariadb

score 0 · Answer 2 · answered Jul 16 '12 at 11:21

0

I can connect without even specifying the client certificate properties when the user has been configurated with only REQUIRES SSL.

answered Jul 16 '12 at 11:21

Mark Matten

381
3
15

score 0 · Answer 3 · answered Feb 27 '16 at 20:52

0

Seems the MariaDB driver folks have a test class that walks through all the scenarios including 'REQUIRES X509'.

https://github.com/MariaDB/mariadb-connector-j/blob/master/src/test/java/org/mariadb/jdbc/SslTest.java

answered Feb 27 '16 at 20:52

mohr_michael_a

204
1
3

score 0 · Answer 4 · answered Mar 18 '11 at 10:57

0

I've settled on using REQUIRE SSL. If someone knows how to make X509 work with JDBC, this info would be appreciated.

answered Mar 18 '11 at 10:57

Victor Sorokin

11,878
2
35
51

See my answer for how to use self-signed X.509 certificates with MySQL/MariaDB JDBC connections. – sehrope Jul 03 '13 at 15:32

score 0 · Answer 5 · answered Mar 03 '23 at 13:29

I know this question is quite old; however, I stumbled upon this when I faced this issue today. In my case had the certificate files server-ca.pem, client-key.pem & client-cert.pem provided to me already which I successfully passed to the mysql command-line client as parameters to the relevant options. After which began my quest for a way to use them for making a successful JDBC connection. I am writing this answer, just in case it helps someone save their precious hours.

I referred to "Setting up Client Authentication" at https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html

For simpilicity I had the certificates in the same directory I was working in.

Creating the trust key store to be used in the JDBC URL:

keytool -import -file server-ca.pem -storetype JKS -keystore trustkeystore

Creating the client key store to be used in the JDBC URL (note that this is a 2 step process):

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:<password> -out client-keystore.p12

keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass <password> -destkeystore clientkeystore -deststoretype JKS -deststorepass <password>

The JDBC URL which finally worked in may case:

jdbc:mysql://<host>:<port>/<db_name>?trustCertificateKeyStoreUrl=file://<absolute_path>/trustkeystore&trustCertificateKeyStorePassword=<password>&clientCertificateKeyStoreUrl=file://<absolute_path>/clientkeystore&clientCertificateKeyStorePassword=<password>

MySQL JDBC over SSL problem

5 Answers5

Linked