Friday, April 29, 2011

Secure JDBC connection to MySQL from GlassFish

Introduction

Following up my article on Secure JDBC connection to MySQL from Java, It is often the case you Java code will not be connecting to the database directly but instead will lookup a connection from a container managed connection pool.  This article describes how to configure a GlassFish connection pool to connect to MySQL securely using keystore/truststore SSL keys.  Thanks much to Thomas Schaefer for this information.

GlassFish Connection Pool

Using the GlassFish administrator, creating a Connection Pool of MySQL database connections is easy.  To secure the communication, go to the "Additional Properties" tab and add these additional properties, of course replace the values between the [] with your own.


Name                               Value
requireSSL                         true
useSSL                             true
trustCertificateKeyStorePassword   [password_to_truststore]
clientCertificateKeyStoreUrl       file:/c:/temp/keystore.jks
clientCertificateKeyStoreType      JKS
clientCertificateKeyStorePassword  [password_to_keystore]
trustCertificateKeyStoreType       JKS
password                           [database_user_password]
trustCertificateKeyStoreUrl        file:/c:/temp/cacerts.jks  
user                               [databse_user_name]
url                                jdbc:mysql://[server_name]

The keystore.jks and cacerts.jks files are the tricky part of this configuration.  How you use them will depend on your situation but in general, cacerts.jks will have your certificate authority added to it and keystore.jks will have a certificate added to it signed by your certificate authority.  When the JDBC driver attempts to connect to the database, the certificate from keystore.jks is presented to the database server and the database server accepts it since the certificate was signed by your certificate authority.  In the same way, the database server will present a certificate to the GlassFish server and GlassFish will accept it because your certificate authority is in cacerts.jks.

Tuesday, April 26, 2011

Secure JDBC connection to MySQL from Java

Introduction

Connecting to a database with JDBC is easy but gets a little more complicated if a secure connection is needed.  This is how a secure connection to MySQL is established using keystore/truststore SSL keys.  Overall, nothing too spectacular here but a useful reference.

Java

The Java code to get a secure JDBC connection to MySQL is easy.  It is a matter of adding properties to the URL connection string which inform the MySQL JDBC driver to use a secure connection.

  String username = "[USERNAME]";
  String password = "[PASSWORD]";

        
  StringBuilder url = new StringBuilder();
  url.append("jdbc:mysql://[SERVER]/[SCHEMA]?")
     .append("useSSL=true&")
     .append("requireSSL=true&")
  ;

   

  Connection conn = DriverManager.getConnection(url.toString(), username, password);

System Properties

The properties on the URL connection string tell the MySQL JDBC driver to use a secure connection but you still need to tell your application where the keystore/truststore SSL keys are located.  Do this using the following system properties when you start the JVM.

  -Djavax.net.ssl.trustStore=C:\temp\cacerts.jks 
  -Djavax.net.ssl.trustStorePassword=[PASSWORD]
  -Djavax.net.ssl.keyStore=C:\temp\keystore.jks 
  -Djavax.net.ssl.keyStorePassword=[PASSWORD]

These files will of course need the the keys off your MySQL server.