View source for SSL/TLS connection to the Mysql/MariaDB

{{DISPLAYTITLE:Securing the Database Connection with SSL/TLS}}

'''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:
#'''Enable SSL/TLS on the MySQL/MariaDB server''' and provide it with a server certificate.
#'''Configure the VoIPmonitor Sensor''' to use SSL/TLS when connecting to the database.
#'''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`):
<pre>
[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
</pre>
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.
<pre>
-- 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'@'%';
</pre>

== 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:
<pre>
# /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 = ...
</pre>
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) ===
# Navigate to '''Settings -> System configuration -> [database]'''.
# Fill in the paths to your SSL key, certificate, and CA files in the corresponding fields.
# Save the configuration.

=== Option B: Via `configuration.php` ===
Manually edit `/var/www/html/config/configuration.php` and add the following definitions:
<pre>
<?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", "");
</pre>

== 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.

=== Disabling SSL/TLS for Self-Signed Certificate Errors ===
If you are encountering SSL/TLS errors related to self-signed certificates (e.g., when deploying in a trusted network environment), you can disable SSL by commenting out all <code>mysqlssl*</code> parameters in the sensor configuration file:

<pre>
# /etc/voipmonitor.conf

# Comment out these lines to disable SSL/TLS
# 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 = ...
</pre>

{{Warning|Disabling SSL/TLS removes encryption for database connections. Only use this in trusted networks or development environments where network security is provided by other means (e.g., VPN, private LAN).}}

For the GUI, remove or comment out the <code>MYSQL_KEY</code>, <code>MYSQL_CERT</code>, and <code>MYSQL_CACERT</code> definitions in <code>configuration.php</code>.

=== 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`:
<pre>
<?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");
}
?>
</pre>

;2. Run the script from the command line:
<pre>php test_ssl.php</pre>
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?