Connecting to Secure MySQL from Java JDBC.

Like any other enterprise database, mysql supports secure jdbc connections using ssl/tls. I had a requirement to connect to Mysql using secure JDBC, the initial setup took longer than I anticipated due to the requirement of converting .pem files to jks . I had to look up multiple articles, not satisfied with quality of available articles I decided to write one covering end to end instructions.

I’l assume mysql server is already setup for ssl connections , if it is not and you are looking for steps, I found following article useful with my server setup  https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html

Now lets look into the steps for creating a Java program client, I am only discussing one way authentication i.e client validating server in the following steps.

  1. I am assuming mysql java connector is installed if not you can install the same using following yum command, ” yum install mysql-connector-java”
  2. Next we will convert server certificate file from pem format into jks (java keystore) which can be used with java client programs. In order to do so there is an intermediate step of converting pem file to der as shown following.
    1. Convert server certificate (server-cert.pem) to der format , i.e “openssl x509 -outfrom der -in server-cert.pem -out certificate.der”
    2. Create an empty trustore file, using “keytool -genkey -alias mysql -keystore truststore.jks -storepass password” where -storepass parameter configures the password for this trustStore which you’l be using whenever accessing or modifying the trustStore.
    3. Once truststore is created, lets delete the default key and certificate pair using “keytool -delete -alias mysql -keystore truststore.jks” since we will be importing mysql server certificate to this trustStore. When prompted for password enter the storepass.
    4. Lets import the der file created in 1. into our truststore using the command “keytool -import -alias mysql -keystore truststore.jks -file certificate.der”.
    5. To check if its imported successfully use the following command to verify, “keytool -list -keystore truststore.jks” .
  3. Following is a sample Java program to connect to secure MySQL Jdbc. Notice the flag useSSL which is set to true.
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
public class MySQL {
 public static void main(String args[]){
 try{
       Class.forName("com.mysql.jdbc.Driver");
       Properties props = new Properties();
       props.setProperty("user", "mysqluser");
       props.setProperty("password", "password");
       props.setProperty("useSSL","true");
       Connection con=DriverManager.getConnection(
       "jdbc:mysql://mysql.server.com:3306/userdb",props);
       Statement stmt=con.createStatement();
       ResultSet rs=stmt.executeQuery("select * from x_user");
       while(rs.next())
            System.out.println(rs.getInt(1));
       Scanner in=new Scanner(System.in);
       in.next();
       con.close();
      }
      catch(Exception e)
      { 
        System.out.println(e);
      }
 }

}

  • To compile and run the program use the following commands, notice the javax.net.ssl.trustStore flag which points to trustStore we created above and also the javax.net.ssl.trustStorePassword.
javac MySQL.java
java -cp .:/usr/share/java/mysql-connector-java.jar -Djavax.net.ssl.trustStore=/etc/mysql-ssl/truststore.jks -Djavax.net.ssl.trustStorePassword=password MySQL

The program should display the first field of the queried tables’ resultset.

Advertisements

One thought on “Connecting to Secure MySQL from Java JDBC.

  1. Pingback: Connecting to Secure MySQL from Java JDBC. — BigData Analytics Simplified – SutoCom Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s