Has anyone tried encrypting the application to database connection? SSL over JDBC to MySQL

Question: I’m trying to make the connection from the OpenMRS connection to MySQL encrypted with SSL.

I’ve followed - https://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html to create the certificates needed.

Used this document for using those certificates - https://dev.mysql.com/doc/refman/5.5/en/using-encrypted-connections.html#using-encrypted-connections-server-side-configuration

I’m facing the following error to connect to MySQL - https://dba.stackexchange.com/questions/91514/mysql-ssl-setup-failed. I confirmed that mysql user can read those certs as mentioned in the solution of the answer.

I’m unable to find other possible solutions other online forums. Did anyone try this in their environment and have any insights? @burke, @darius, @dkayiwa, @raff, @wyclif, @cintiadr and others?

I haven’t specifically handled SSL to MySQL, but I’ve handled my fair share of SSL problems.

Usually, for most applications using encryption, all you have to do is:

  • generate a openssl private/public key pair for the server
  • generate the Certificate Signing Request (CSR) for the public key of the server
  • sign the CSR on a Certificate Authority (by an external root like godaddy, letsencrypt, or an internal CA)
  • configure the server to use the certificate and the private key
  • make sure the client attempting to connect to the server has the CA certificates on its truststore

As far as you have the CA certificate in your truststore, you’ll trust any public key signed by it.

From the documentation, it appears that mysql have two modes:

  • If the mysql DB user doesn’t have any encryption requirements, the client only need to have the CA certs on its truststore

Suppose that you want to connect using an account that has no special encryption requirements or was created using a GRANT statement that includes the REQUIRE SSL option.

  • If the mysql DB user was configured to require certificate, the client has to be configured to have a key pair as well, with a certificate signed by the same CA.

To require that a client certificate also be specified, create the account using the REQUIRE X509 option. Then the client must also specify the proper client key and certificate files or the server will reject the connection:

https://dev.mysql.com/doc/refman/5.5/en/using-encrypted-connections.

So, what I’d recommend is:

  1. Confirm the mysql user doesn’t require client encryption
  2. Can you first try to connect to the server using the mysql command, and passing the ‘–ssl-ca=ca-cert.pem’?
  3. If that works, I’d add the CA certificate to Java root truststore before starting the application: https://docs.oracle.com/cd/E19906-01/820-4916/geygn/index.html There are ways to override the whole root truststore for a single JVM, but I’ve seen problems enough in the past as you need to create the truststore from scratch. I think adding it to the root truststore is simpler.

I believe your problem might be related to that. It usually is.

Gotchas for production

Certificates and keys are particularly tricky to keep on the long run.

The reason why you have to mess with the java truststore or configure the CA certificate for the mysql server/client is because you are not using a CA which is already trusted by the operational system/Java. So, if it’s a server which is accessible from the internet, I’d recommend instead to just use a public CA, like letsencrypt (it’s free :D). You can pay other companies to sign certificates for you, even if it’s a private machine.

Otherwise, the best solution in the long run is to have a machine somewhere, any machine, to be your internal CA. There are a lot of tutorials on how to do it, but it’s just a machine with openssl installed. Take the certificate of the CA, signed by itself (!), and deploy to all truststores of everything needing to consume the certificates. That could be your operational system, your JVM truststore, firefox, chrome, etc. The CA certificate will expire every so often, and when that’s about to happen you need to recreate a new cert and deploy to all truststores again.

On a machine requiring the certs, you generate the key pair and create a certificate signing request (CSR). You copy the CSR to the CA, sign it, and copy the signed certificate back to the original machine. CSR and certificates are public, there’s no need to protect them.

Also, make sure to put any monitoring or even entries in google calendar to remind you to renew certificates (for all machines, INCLUDING the CA one), and they have this terrible tendency to expire without notice and everything breaks. :smiley: The private keys and public keys will never change, you’ll just have to generate a new certificate. Clients won’t need be updated or changed, as they already trust the CA which signed this new cert.

2 Likes

@cintiadr, thanks for the pointers Cintia, they helped me a lot. I followed this wiki later - https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tls-for-mysql-on-ubuntu-16-04 and figured out that the error was related to mysql not having permission to the cert file on my local system.

The error message was very generic for me to figure out the solution