Troubleshooting‎ > ‎

Connect to MySQL using SSL

You can create self-signed certificates and install with a local copy of MySQL using the following scripts and command. You can use this to verify MySQL Secure Sockets Layer (SSL) connectivity at a JDBC-driver level. After you have completed this, you connect to Live API Creator using the verify URL that is printed.

Note: The combination of MySQL, JDBC drivers, and certificates creates a complex ecosystem. This ecosystem can be time consuming to configure correctly and to make work.

After you have properly configured SSL, you can define a JNDI data source in your container (Tomcat, Jetty, etc.) and use that JNDI data source to connect to your database using API Creator.

Configuring and testing MySQL/SSL

The following scripts assume OS/X on a Mac with OpenSSL 0.9.8zg:

ssltest.sh

#!/bin/bash

sslDir=ssltest

mkdir -p "${sslDir}"

rm "${sslDir}"/*

ls -l "${sslDir}"

caCertFile="${sslDir}/ssltest-ca-cert.pem"
caKeyFile="${sslDir}/ssltest-ca-key.pem"

serverCertFile="${sslDir}/ssltest-server-cert.pem"
serverKeyFile="${sslDir}/ssltest-server-key.pem"
serverReqFile="${sslDir}/ssltest-server-req.pem"

clientCertFile="${sslDir}/ssltest-client-cert.pem"
clientKeyFile="${sslDir}/ssltest-client-key.pem"
clientKeystoreFile="${sslDir}/ssltest-client-keystore.p12"
clientReqFile="${sslDir}/ssltest-client-req.pem"

clientP12KeystoreFile="${sslDir}/ssltest-client-keystore.p12"
clientJKSKeystoreFile="${sslDir}/ssltest-client-keystore.jks"

caSubj="/C=US/ST=SomeState/L=SomeTown/O=SSLTest/CN=sslca/emailAddress=sslca@example.com"
serverSubj="/C=US/ST=SomeState/L=SomeTown/O=SSLTest/CN=sslserver/emailAddress=sslserver@example.com"
clientSubj="/C=US/ST=SomeState/L=SomeTown/O=SSLTest/CN=sslclient/emailAddress=sslclient@example.com"

# generate a CA (certificate authority) key
openssl genrsa -out "${caKeyFile}" 2048

# create that CA certificate for self-siging
openssl req \
    -new \
    -x509 \
    -nodes \
    -days 365 \
    -key "${caKeyFile}" \
    -out "${caCertFile}" \
    -subj "${caSubj}"

# create a request for a server certificate
openssl req \
    -newkey rsa:2048 \
    -days 365 \
    -nodes \
    -keyout "${serverKeyFile}" \
    -out "${serverReqFile}" \
    -subj "${serverSubj}"

# and generate the signed server certificate
openssl x509 \
    -req \
    -in "${serverReqFile}" \
    -days 365 \
    -CA "${caCertFile}" \
    -CAkey "${caKeyFile}" \
    -set_serial 01 \
    -out "${serverCertFile}"

# create a request for a client certificate
openssl req \
    -newkey rsa:2048 \
    -days 365 \
    -nodes \
    -keyout "${clientKeyFile}" \
    -out "${clientReqFile}" \
    -subj "${clientSubj}"

# and generate the signed client certificate
openssl x509 \
    -req \
    -in "${clientReqFile}" \
    -days 365 \
    -CA "${caCertFile}" \
    -CAkey "${caKeyFile}" \
    -set_serial 01 \
    -out "${clientCertFile}"

# generate a keystore with BOTH the client cert & key
openssl pkcs12 \
    -export \
    -in "${clientCertFile}" \
    -inkey "${clientKeyFile}" \
    -out "${clientP12KeystoreFile}" \
    -name "mysqlAlias" \
    -passout pass:kspass

# convert pksc12 to jks for use in Java
keytool \
    -importkeystore \
    -deststorepass kspass \
    -destkeypass kspass \
    -destkeystore "${clientJKSKeystoreFile}" \
    -srckeystore "${clientP12KeystoreFile}" \
    -srcstoretype PKCS12 \
    -srcstorepass kspass \
    -alias "mysqlAlias"

ls -l "${sslDir}"

exit

# These may run to copy and configure my.cnf to use these certificates

sudo mkdir -p /etc/certtest
sudo cp "${caCertFile}" /etc/certtest/
sudo cp "${serverCertFile}" /etc/certtest/
sudo cp "${serverKeyFile}" /etc/certtest/
sudo cp "${clientJKSKeystoreFile}" /etc/certtest/

cat >ssltest-install.awk <<EOF
BEGIN { inmysqld = 0 }
# a simple awk script to remove existing ssl- comments in mysqld and use these newly generated ones
/^\[mysqld\]/ {
  inmysqld = 1
  print
  print "ssl-ca=/etc/certtest/ssltest-ca-cert.pem"
  print "ssl-cert=/etc/certtest/ssltest-server-cert.pem"
  print "ssl-key=/etc/certtest/ssltest-server-key.pem"
  next
}
/^\[/ { inmysqld = 0
  print
  next
}
inmysqld && $0 ~ /^ssl-/ {
  next
}
{
  print
}
EOF

sudo cp /etc/my.cnf my.cnf.original
sudo gawk -f doit.awk my.cnf.original >my.cnf.tmp
sudo cp my.cnf.tmp /etc/my.cnf


Java Test Program

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTest {

    public static void main(String[] args) {

        String url = "jdbc:mysql://192.168.1.100:3306/ssltest"
                + "?verifyServerCertificate=false"
                + "&useSSL=true"
                + "&requireSSL=true"
                + "&clientCertificateKeyStoreUrl=file:///etc/certtest/ssltest-client-keystore.jks"
                + "&clientCertificateKeyStorePassword=kspass"
                + "&serverSslCert=/etc/certtest/ssltest-ca-cert.pem"
                + "";

        String username = "ssluser";

        String password = "Password1";
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            System.out.println("Connected with database " + conn.getCatalog());
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("show status like 'ssl%'");
            ResultSetMetaData meta = rs.getMetaData();
            for (int col = 1; col <= meta.getColumnCount(); ++col) {
                if (1 != col) {
                    System.out.print("\t");
                }
                System.out.print(meta.getColumnLabel(col));
            }
            System.out.println();
            while (rs.next()) {
                for (int col = 1; col <= meta.getColumnCount(); ++col) {
                    if (1 != col) {
                        System.out.print("\t");
                    }
                    System.out.print(rs.getObject(col));
                }
                System.out.println();
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }
}