configure jump server to use SSL for MySQL server 5.7 version

How to configure ezeelogin  to use SSL for MySQL database connections  on ubuntu 16.04 ?

Mysql - SSL setup on Ubuntu  mysql server 5.7 version

1. Check the Current SSL/TLS Status

   Log into a MySQL session 

root@gateway:~# mysql -u root -p -h 127.0.0.1

  Show the state of the SSL/TLS variables by typing:

mysql> SHOW VARIABLES LIKE ’%ssl%’;

Output
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.01 sec)

 

  The have_openssl and have_ssl variables are both marked as DISABLED. This means that SSL functionality has been compiled into the server, but that it is not yet enabled.

 

2. Generate SSL/TLS Certificates and Keys

To enable SSL connections to MySQL, we first need to generate the appropriate certificate and key files

 we can use the following command to generate the necessary files.

 The files will be created in MySQL’s data directory, located at  /var/lib/mysql

root@gateway:~# mysql_ssl_rsa_setup --uid=mysql

Check the generated files by typing:

root@gateway:~# find /var/lib/mysql -name ’*.pem’ -ls

output

256740 4 -rw-r--r-- 1 mysql mysql 1078 Mar 17 17:24 /var/lib/mysql/server-cert.pem
256735 4 -rw------- 1 mysql mysql 1675 Mar 17 17:24 /var/lib/mysqlsql/ca-key.pem<^>
256739 4 -rw-r--r-- 1 mysql mysql 451 Mar 17 17:24 /var/lib/mysqlsql/public_key.pem<^>
256741 4 -rw------- 1 mysql mysql 1679 Mar 17 17:24 /var/lib/mysqlsql/client-key.pem<^>
256737 4 -rw-r--r-- 1 mysql mysql 1074 Mar 17 17:24 /var/lib/mysqlsql/ca.pem<^>
256743 4 -rw-r--r-- 1 mysql mysql 1078 Mar 17 17:24 /var/lib/mysqlsql/client-cert.pem<^>
256736 4 -rw------- 1 mysql mysql 1675 Mar 17 17:24 /var/lib/mysqlsql/private_key.pem<^>
256738 4 -rw------- 1 mysql mysql 1675 Mar 17 17:24 /var/lib/mysqlsql/server-key.pem<^>

 

Enable SSL Connections on the MySQL Server

Restart the MySQL service

root@gateway:~# systemctl restart mysql

After restarting, open up a new MySQL session using the same command as before.

root@gateway:~# mysql -u root -p -h 127.0.0.1

Check state of the SSL/TLS variables by typing:

mysql> SHOW VARIABLES LIKE ’%ssl%’;

Output
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| have_openssl  | YES            |
| have_ssl      | YES            |
| ssl_ca        | Ca.pem         |
| ssl_capath    |                |
| ssl_cert      | server-cert.pem|
| ssl_cipher    |                |
| ssl_crl       |                |
| ssl_crlpath   |                |
| ssl_key       | server-key.pem |
+---------------+----------------+
9 rows in set (0.01 sec)

 

The have_openssl and have_ssl variables read "YES" instead of "DISABLED" this time.

 

Check the connection details by the following command:

 

 

root@gateway:~# mysql -u ezlogin_database_username -p -h hostname or ip --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem 


example :

 

 

root@gateway:~# mysql -u ezlogin_xxxx -p -h 10.11.1.11 --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem 

 

 

mysql> \s

--------------- 

. . .

SSL: Cipher in use is DHE-RSA-AES256-SHA

. . .

Connection: 127.0.0.1 via TCP/IP

. . .

---------------- 

 

SSL cipher is displayed, indicating that SSL is being used to secure our connection.

 

3. Configure ezeelogin jump server to use SSL for Mysql

 

Add mysql_ssl_key,mysql_ssl_cert,mysql_ssl_ca to /usr/local/etc/ezlogin/ez.conf

Edit the  /usr/local/etc/ezlogin/ez.conf file add the following

root@gateway:~# vi /usr/local/etc/ezlogin/ez.conf

#Add the following 

system_folder /var/www/ezlogin/
force_https no
uri_path /ezlogin/
db_host 10.10.1.11
db_port 3306
db_name ezlogin_qzms
db_user ezlogin_edcjwz
db_pass dsH)$s5xAE[QgFms
db_prefix aqvo_
cookie_encryption_key ASvs8^pnu^^X9
cookie_name lcrrfs
cookie_path /ezlogin/
www_folder /var/www/html/ezlogin/
admin_user admin
mysql_encrypt yes
mysql_ssl_key /var/lib/mysql/client-key.pem
mysql_ssl_cert /var/lib/mysql/client-cert.pem
mysql_ssl_ca /var/lib/mysql/ca.pem
mysql_ssl_capath /var/lib/mysql
mysql_ssl_cipher ALL:!ADH
mysql_ssl_verify no

 

Make sure that you have changed db_port to 3306 & db_host to IP Address of your host

 4. Change the bind-address & allow the ezeelogin jump server user to access the database.

   

 Edit the  /etc/mysql/mysql.conf.d/mysqld.cnf & change bind-address 

root@gateway:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf

 

Change bind-address to host ip(server ip)

bind-address x.x.x.x (Host ip)

 

  Restart the MySQL service

root@gateway:~# systemctl restart mysql

 

  you can find out ezeelogin jump server dbname and  mysql username from the ez.conf file

root@gateway:~# cat /usr/local/etc/ezlogin/ez.conf


system_folder /var/www/ezlogin/
force_https no
uri_path /ezlogin/
db_host 10.10.1.11
db_port 3306
db_name ezlogin_qzms
db_user ezlogin_edcjwz
db_pass dsH)$s5xAE[QgFms
db_prefix aqvo_
cookie_encryption_key ASvs8^pnu^^X9
cookie_name lcrrfs
cookie_path /ezlogin/
www_folder /var/www/html/ezlogin/
admin_user admin
mysql_encrypt yes
mysql_ssl_key /var/lib/mysql/client-key.pem
mysql_ssl_cert /var/lib/mysql/client-cert.pem
mysql_ssl_ca /var/lib/mysql/ca.pem
mysql_ssl_capath /var/lib/mysql
mysql_ssl_cipher ALL:!ADH
mysql_ssl_verify no

 

Use this command for granting privileges for root " GRANT USAGE ON ezlogin_databasename.* TO 'root'@'Hostname or ip' WITH GRANT OPTION;

Login to mysql 

root@gateway:~# mysql -u root -p 

[Enter password]

mysql> grant all on ezlogin_databasename.* to 'mysql_username'@'%' identified by 'password';

example : mysql > grant all on ezlogin_xxx.* to 'ezlogin_xxxx'@'%' identified by 'dsH)$s5xAE[QgFmfsfgg';

mysql > flush privileges;

mysql > exit 

 

 Check if you can login to mysql using ezeelogin jump server databases

root@gateway:~# mysql -u ezeelogin_database_username -h 10.11.1.11 -p

Enter Password:

mysql >

mysql > exit

 

If you have any difficulties please contact support 

5 (1)
Article Rating (1 Votes)
Rate this article
    Attached Files
    There are no attachments for this article.
    Related Articles RSS Feed
    Enable or Disable or force ssl for the web interface
    Viewed 2700 times since Thu, Jun 15, 2017
    configure jump server to use SSL for MySQL
    Viewed 2859 times since Mon, Apr 30, 2018
    Integrate OpenLdap / Windows Active Directory ( AD ) authentication in ezeelogin jump server (Centos &Ubuntu)
    Viewed 6881 times since Thu, Jun 15, 2017
    How do i change the authentication from ldap to internal in the database?
    Viewed 1777 times since Thu, Jun 15, 2017
    Add a server using ssh key pair in ezeelogin
    Viewed 1048 times since Fri, Mar 22, 2019
    How to configure Ezeelogin to authenticate using Open_Ldap(Pam-Ldap) in ubuntu?
    Viewed 3194 times since Fri, Feb 23, 2018
    How can i add more than one public key to ezeelogin user ?
    Viewed 1745 times since Wed, Oct 18, 2017
    How to reset cluster keys in ezeelogin Master-slave Configuration ?
    Viewed 925 times since Fri, Jun 8, 2018
    Configure Nginx webserver on Jump server / Bastion host
    Viewed 7434 times since Fri, Nov 3, 2017
    Set SSH User Expiry
    Viewed 1613 times since Thu, Sep 20, 2018