CODEWITHSUNDEEP
javamysqlssljdbc
Victor Sorokin
Victor Sorokin

Reputation: 11996

MySQL JDBC over SSL problem

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?

Upvotes: 4

Views: 20062

Answers (5)

alwayslearning
alwayslearning

Reputation: 101

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>

Upvotes: 0

mohr_michael_a
mohr_michael_a

Reputation: 204

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

Upvotes: 0

sehrope
sehrope

Reputation: 1777

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

Upvotes: 5

Mark Matten
Mark Matten

Reputation: 401

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

Upvotes: 0

Victor Sorokin
Victor Sorokin

Reputation: 11996

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

Upvotes: 0

Related Questions