SSL/TLS connection to the Mysql/MariaDB

Requirements

• sensor 25.9 or higher
• gui 24.4 or higher

Mysql/MariaDB

Add adjusted options to the mysql config (a key/cert generation is out of scope).

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

Usefull additional tls settings

tls-version=TLSv1,TLSv1.1,TLSv1.2

In the latest versions (2023) only

tls-version=TLSv1.1,TLSv1.2,TLSv1.3

Some useful console commands:

SHOW GLOBAL VARIABLES LIKE '%ssl%';
SHOW GLOBAL VARIABLES LIKE '%tls%';
set global require_secure_transport = on;
grant all on *.* to USERNAME require ssl;
grant all on *.* to USERNAME require none;
show grants for USERNAME;
flush privileges;

Sensor

it's simple. You will set/adjust the needed options in the cfg

# SSL/TLS setting for the mysql connection. You can use key + cert + cacert. Or you can use the cacert only (in the Azure environment). Etc.
# file with key
#mysqlsslkey = /etc/ssl/client-key.pem
# file with certificate
#mysqlsslcert = /etc/ssl/client-cert.pem
# file with ca certificate
#mysqlsslcacert = /etc/ssl/ca-cert.pem
# directory with certs
#mysqlsslcapath = /etc/ssl/capath
# list of allowed ciphers
#mysqlsslciphers =

## for backup db
# SSL/TLS settings
#database_backup_from_mysqlsslkey =
#database_backup_from_mysqlsslcert =
#database_backup_from_mysqlsslcacert =
#database_backup_from_mysqlsslcapath =
#database_backup_from_mysqlsslciphers =

• then restart the sensor and the sensor should connect.

GUI

1) manually add/adjust these options into config/configuration.php file:

// define("MYSQL_KEY", "/var/www/sslsniff/client-key.pem");
// define("MYSQL_CERT", "/var/www/sslsniff/client-cert.pem");
// define("MYSQL_CACERT", "/var/www/sslsniff/ca-cert.pem");
// define("MYSQL_CAPATH", "");
// define("MYSQL_CIPHERS", "");

2) Or use GUI->Settings->System configuration->[database] menu

3) in new installation you can enter these options on the setting page

Troubleshooting

There exist some combinations of php and mysql which doesn't work together.

E.g. php versions 5.6,7.0,7.1 don't work with the mysql/mariadb with enabled protocol TLSv1.2 or higher because there is a bug in the php (https://bugs.php.net/bug.php?id=74445) The problem is that the php don't try more protocols in the connection (it tries TLSv1 or TLSv1.1 only). Fixed in the latest 7.2 But php 5.3 in the Centos6 works without problem.

Here is a part of the code for testing purposes which must work:

<?php

$db = mysqli_init();
mysqli_options($db, MYSQLI_OPT_CONNECT_TIMEOUT, 5);
mysqli_ssl_set($db, '/var/www/sslsniff/client-key.pem', '/var/www/sslsniff/client-cert.pem', '/var/www/sslsniff/ca-cert.pem', NULL, NULL);
$client_flags = 0;
if (defined('MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT')) {
        $client_flags |= MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT;
}
$link = mysqli_real_connect ($db, 'HOST', 'USER', 'PASS', 'DBNAME', 3306, NULL, $client_flags);
if (!$link) {
        die ('My Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
        mysqli_close($db);
        die('Success... '. "\n");
}

?>

You can see:

# php7.1 testssl.php 
PHP Warning:  mysqli_real_connect(): SSL operation failed with code 1. OpenSSL Error messages:
error:1409442E:SSL routines:ssl3_read_bytes:tlsv1 alert protocol version in /var/www/git-web/testssl.php on line 14
PHP Warning:  mysqli_real_connect(): Cannot connect to MySQL by using SSL in /var/www/git-web/testssl.php on line 14
PHP Warning:  mysqli_real_connect(): [2002]  (trying to connect via (null)) in /var/www/git-web/testssl.php on line 14
PHP Warning:  mysqli_real_connect(): (HY000/2002):  in /var/www/git-web/testssl.php on line 14
My Connect error (2002): 

# php7.3 testssl.php 
Success... 

So our recommendation is the php7.3 compiled with the openssl library 1.1 or higher.

PHP report 2023

Test environment: Debian 11, Mariadb server 10.5, php packages from 'deb [trusted=yes] https://packages.sury.org/php bullseye main' + original php 7.4 from debian

• TLSv1.1 not compiled in and can't be enabled.
• TLSv1.2 functional PHP: 7.2, 7.3, 7.4 (both), 8.0, 8.1, 8.2
• TLSv1.3 functional PHP: 7.4 (both), 8.0, 8.1, 8.2

Sensor report 31.7.x and older (till 2023)

• static build from Voipmonitor site: TLSv1, TLSv1.1, TLSv1.2
• own dynamic build: depends on the system's libssl/libmysql/libmariadb libraries. TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 is tested and works (with debian 11, libmariadb.so.3 library)

Sensor report 31.8 and newer (from 2023)

• static build from Voipmonitor site: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3
• own dynamic build: depends on the system's libssl/libmysql/libmariadb libraries. TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 is tested and works (with debian 11, libmariadb.so.3 library)