connect mysql server from spring boot application with SSL

Hello Techrunnr, this document deals which how to connect mysql server from spring boot application with SSL enabled.
All the applications interact with the database in order to get data for your web application. It’s always better and recommended to have encrypted communication between database and web application. Setting up of an encrypted connection between these two is very simple and easy.

Here are the steps connect MySQL server from spring boot application with SSL enabled.

Prerequisites

* Java installed machine, if not installed follow this link
* Mysql Server installed and running, if not follow this link

Generate SSL certificate in Database server.
NOTE: Make sure Common name should be different 

1. Generate CA Certificate


openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

2. Create Server Certifcate and Sign with CA

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

3. Create Client certificate, Sign with CA

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

4. Verify the Certificate

After generating the certificates, verify certificates are correct

openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

You should get output like this.

server-cert.pem: OK
client-cert.pem: OK

Enter the following statements in my.cnf

vi /etc/mysql/my.cnf

[client]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
[mysqld]
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Restart the MySQL service to take effect new configuration.

/etc/init.d/mysql restart

5. Verify the SSL configuration.
Login to mysql server

mysql -u root -p

MariaDB [(none)]>  SHOW VARIABLES LIKE '%ssl%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| have_openssl        | YES                             |
| have_ssl            | YES                             |
| ssl_ca              | /etc/mysql/ssl2/ca.pem          |
| ssl_capath          |                                 |
| ssl_cert            | /etc/mysql/ssl2/server-cert.pem |
| ssl_cipher          |                                 |
| ssl_crl             |                                 |
| ssl_crlpath         |                                 |
| ssl_key             | /etc/mysql/ssl2/server-key.pem  |
| version_ssl_library | OpenSSL 1.0.1e-fips 11 Feb 2013 |
+---------------------+---------------------------------+


 

mysql> \s
————–
mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

Connection id: 3
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 13 sec

6. Generate trustore certificate.

keytool -importcert -file ca.pem -keystore truststore -storepass mypass

Trust this certificate? [no]: yes

7. Generate Keystore Certificate for client certificate

openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -passout pass:mypass -out client-keystore.p12


keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass mypass -destkeystore keystore -deststoretype JKS -deststorepass
mypass

8. Change the JDBC connection to use SSL in application properties.


spring.datasource.url=jdbc:mysql://192.168.10.11:3306/mydatabase?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&verifyServerCertificate=true&useSSL=true&requireSSL=true&clientCertificateKeyStoreUrl=file:/opt/clientkeystore.jks&clientCertificateKeyStorePassword=mypass&trustCertificateKeyStoreUrl=file:/opt/truststore.jks&trustCertificateKeyStorePassword=mypass

© 2018, Techrunnr. All rights reserved.

#1
#2
#3
Questions Answered
Articles Written
Overall Points

Prabhin Prabharkaran

He is Technical professional. He is a person who loves to share tricks and tips on the Internet. He Posts what he does!!

Leave a Reply

Please wait...

Subscribe to our newsletter

Want to be notified when our article is published? Enter your email address and name below to be the first to know.