SSL/TLS connection to the Mysql/MariaDB

This guide provides a comprehensive, step-by-step tutorial on how to encrypt the connection between VoIPmonitor components (Sensor, GUI) and the MySQL/MariaDB database using SSL/TLS.

Overview

Encrypting the database connection is a critical security measure, especially when your database server runs on a different host than your sensor or GUI. This prevents credentials and sensitive CDR data from being transmitted in plaintext over the network.

The process involves three main parts:

1. Enable SSL/TLS on the MySQL/MariaDB server and provide it with a server certificate.
2. Configure the VoIPmonitor Sensor to use SSL/TLS when connecting to the database.
3. Configure the VoIPmonitor GUI to use SSL/TLS for its database connection.

Note: This guide assumes you have already generated the necessary SSL/TLS files (CA certificate, server certificate, server key, etc.).

Part 1: Configure the MySQL/MariaDB Server

First, you must configure your database server to accept encrypted connections.

Edit your MariaDB/MySQL configuration file (e.g., `/etc/mysql/mariadb.conf.d/50-server.cnf`)

[mysqld]
# Provide paths to your generated SSL files
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

# Optionally, restrict to modern, secure TLS versions
tls_version = TLSv1.2,TLSv1.3

After saving the changes, restart your database service.

Useful SQL Commands for Verification

You can verify the configuration and enforce SSL usage from the SQL command line.

-- Check if SSL variables are active
SHOW GLOBAL VARIABLES LIKE '%ssl%';

-- Enforce that all connections must use SSL/TLS
SET GLOBAL require_secure_transport = ON;

-- Or, enforce SSL on a per-user basis
GRANT USAGE ON *.* TO 'voipmonitor_user'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

-- Check a user's requirements
SHOW GRANTS FOR 'voipmonitor_user'@'%';

Part 2: Configure the VoIPmonitor Sensor

Next, configure the sensor to establish an encrypted connection to the database.

Edit `/etc/voipmonitor.conf` and provide the paths to the client-side SSL files

# /etc/voipmonitor.conf

# --- SSL/TLS settings for the main database connection ---
mysqlsslkey = /etc/voipmonitor/ssl/client-key.pem
mysqlsslcert = /etc/voipmonitor/ssl/client-cert.pem
mysqlsslcacert = /etc/voipmonitor/ssl/ca-cert.pem
# mysqlsslcapath = /etc/voipmonitor/ssl/certs/
# mysqlsslciphers = ...

# --- SSL/TLS settings for the database replication feature ---
# These are only needed if you use the database backup mode
# database_backup_from_mysqlsslkey = ...
# database_backup_from_mysqlsslcert = ...
# database_backup_from_mysqlsslcacert = ...

After saving, restart the sensor service (`systemctl restart voipmonitor`). Check the syslog for any connection errors.

Part 3: Configure the VoIPmonitor GUI

Finally, configure the GUI to use an encrypted connection. This can be done in the GUI itself or directly in the configuration file.

Option A: Via the GUI (Recommended)

1. Navigate to Settings -> System configuration -> [database].
2. Fill in the paths to your SSL key, certificate, and CA files in the corresponding fields.
3. Save the configuration.

Option B: Via `configuration.php`

Manually edit `/var/www/html/config/configuration.php` and add the following definitions:

<?php
// /var/www/html/config/configuration.php

define("MYSQL_KEY",    "/path/to/client-key.pem");
define("MYSQL_CERT",   "/path/to/client-cert.pem");
define("MYSQL_CACERT", "/path/to/ca-cert.pem");
// define("MYSQL_CAPATH", "");
// define("MYSQL_CIPHERS", "");

Troubleshooting & Compatibility

Securing the database connection can sometimes fail due to compatibility issues between PHP versions, the MySQL client library, and the database server's TLS protocol version.

PHP Version Compatibility Issues

An infamous bug in older PHP versions (`5.6`, `7.0`, `7.1`) prevents them from connecting to databases that enforce modern TLS protocols like TLSv1.2 or TLSv1.3. The PHP client fails to negotiate a compatible protocol and the connection is dropped.

• Symptom: You will see an error like `SSL operation failed with code 1. OpenSSL Error messages: error:1409442E:ssl3_read_bytes:tlsv1 alert protocol version`.
• Solution: The bug was fixed in PHP 7.2 and later. It is strongly recommended to use PHP 7.4 or a newer version (8.x) for your GUI server.

Recent Compatibility Report (as of 2023)

• TLSv1.2 Support: Works reliably on PHP versions 7.2, 7.3, 7.4, 8.0, 8.1, 8.2.
• TLSv1.3 Support: Works reliably on PHP versions 7.4, 8.0, 8.1, 8.2.

Sensor TLS Version Support

The TLS versions supported by the sensor depend on how it was compiled.

• Static Binaries (from voipmonitor.org):
  • Versions before 31.8 (pre-2023) supported up to TLSv1.2.
  • Versions 31.8 and newer support up to TLSv1.3.
• Dynamically Compiled Binaries: Support depends on the OpenSSL/MariaDB/MySQL libraries present on the system where it was compiled.

Standalone PHP Test Script

If you are having trouble connecting from the GUI, you can use this simple PHP script to test the connection directly from the command line, which helps isolate the problem.

1. Create a file named `test_ssl.php`

<?php
$db = mysqli_init();
mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
mysqli_ssl_set($db,
    '/path/to/your/client-key.pem',
    '/path/to/your/client-cert.pem',
    '/path/to/your/ca-cert.pem',
    NULL,
    NULL
);

$link = mysqli_real_connect($db, 'YOUR_DB_HOST', 'YOUR_DB_USER', 'YOUR_DB_PASS', 'voipmonitor', 3306, NULL, MYSQLI_CLIENT_SSL);

if (!$link) {
    die('Connection Error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
    mysqli_close($db);
    die("Success! SSL/TLS connection to the database works.\n");
}
?>

2. Run the script from the command line

php test_ssl.php

A "Success" message confirms that your PHP environment is capable of making the encrypted connection. If it fails, the error message will provide valuable clues.

AI Summary for RAG

Summary: This guide provides a comprehensive tutorial for securing the database connection between VoIPmonitor components (Sensor, GUI) and a MySQL/MariaDB server using SSL/TLS. It is structured into a three-part process: 1) Configuring the database server by setting `ssl-ca`, `ssl-cert`, and `ssl-key` in `my.cnf`. 2) Configuring the sensor by setting `mysqlssl*` parameters in `voipmonitor.conf`. 3) Configuring the GUI by defining `MYSQL_*` constants in `configuration.php` or through the web interface. The guide includes useful SQL commands for verifying the server's SSL status and enforcing SSL on user accounts. A critical "Troubleshooting & Compatibility" section details known issues with older PHP versions (pre-7.2) failing to connect to servers enforcing modern TLS protocols (like TLSv1.2/TLSv1.3) and provides a standalone PHP script for testing the connection directly. It also provides a compatibility matrix for different sensor and PHP versions. Keywords: ssl, tls, encrypt, database, mysql, mariadb, secure connection, `require_secure_transport`, `mysqlsslkey`, `mysqlsslcert`, `MYSQL_KEY`, php, compatibility, `tlsv1 alert protocol version` Key Questions:

• How do I encrypt the database connection for VoIPmonitor?
• How to configure SSL/TLS for MariaDB or MySQL?
• What `voipmonitor.conf` settings are needed for a secure database connection?
• How do I configure the GUI to connect to the database using SSL?
• Why am I getting a "tlsv1 alert protocol version" error when connecting from the GUI?
• What PHP versions are compatible with TLSv1.2 and TLSv1.3?
• How can I test my PHP's SSL connection to MySQL from the command line?