Connect to MySQL using TLS

If iTop is hosted on a different server than the MySQL server, and a shared infrastructured is used, then you might want to use encryption between the application and the database.

One way to do that is to use the MySQL secure connection. See the official documentation : MySQL :: MySQL 5.6 Reference Manual :: 6.4 Using Encrypted Connections

Since version 2.5, iTop allows to use thoses parameters :

• enable encryption : db_tls.enabled
• certificate authority file : db_tls.ca

Before using such a connection in iTop, you need to check your configuration. In phpinfo (call setup/phpinfo.php) you should have, in the “mysqlnd” section the following :

• core SSL : supported
• extended SSL : supported

Combodo also hardly recommend to test the link using the following script :

mysql-tls-test.php

<?php
<?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);
 
 
 
assert_options(ASSERT_ACTIVE, true);
assert_options(ASSERT_WARNING, true);
assert_options(ASSERT_BAIL, true);
 
 
 
$sMySqlHost = 'mysqlserver'; //TODO
$iMySqlPort = 3306;
$sMySqlUser = 'user'; //TODO
$sMySqlPassword = 'password'; //TODO
$iMySqlFlag = MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT;
$sTlsKey = '/var/mysql/client-key.pem'; //TODO fix right value, null if not used
$sTlsCert = '/var/mysql/client-cert.pem'; //TODO fix right value, null if not used
$sTlsCa = '/var/mysql/ca.pem'; //TODO fix right value, null if not used
 
 
 
assert(is_readable($sTlsKey), 'Can\'t open SSL Key file');
assert(is_readable($sTlsCert), 'Can\'t open SSL Cert file');
assert(is_readable($sTlsCa), 'Can\'t open SSL CA file');
 
 
 
echo "Trying to connect using :\n host=$sMySqlHost, user=$sMySqlUser, port=$iMySqlPort\n";
echo "TLS options :\n key=$sTlsKey\n cert=$sTlsCert\n ca=$sTlsCa\n";
 
$oMysqli = new mysqli();
$oMysqli->init();
$oMysqli->ssl_set($sTlsKey, $sTlsCert, $sTlsCa, NULL, NULL);
$oMysqli->real_connect($sMySqlHost, $sMySqlUser, $sMySqlPassword, null, $iMySqlPort, NULL, $iMySqlFlag);
 
if ($oMysqli->connect_errno)
{
        die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
        if (!IsOpenedDbConnectionUsingTls($oMysqli)) {
                $oMysqli->close();
                die ('The connection can be opened but is not TLS encrypted !');
        }
 
        echo "Successfully connected using TLS !\n";
 
        $sTlsCipherValue = GetMySqlVarValue($oMysqli, 'ssl_cipher');
        $sTlsVersionValue = GetMySqlVarValue($oMysqli, 'ssl_version');
        echo "TLS cipher=$sTlsCipherValue\n";
        echo "TLS version=$sTlsVersionValue\n";
 
        $oMysqli->close();
}
 
 
 
/**
 * <p>A DB connection can be opened transparently (no errors thrown) without being encrypted, whereas the TLS
 * parameters were used.<br>
 * This method can be called to ensure that the DB connection really uses TLS.
 *
 * <p>We're using this object connection : {@link self::$m_oMysqli}
 *
 * @param \mysqli $oMysqli
 *
 * @return boolean true if the connection was really established using TLS
 * @throws \MySQLException
 *
 * @uses IsMySqlVarNonEmpty
 */
function IsOpenedDbConnectionUsingTls($oMysqli)
{
        $bNonEmptySslVersionVar = IsMySqlVarNonEmpty($oMysqli, 'ssl_version');
        $bNonEmptySslCipherVar = IsMySqlVarNonEmpty($oMysqli, 'ssl_cipher');
 
        return ($bNonEmptySslVersionVar && $bNonEmptySslCipherVar);
}
 
/**
 * @param \mysqli $oMysqli
 * @param string $sVarName
 *
 * @return bool
 * @throws \MySQLException
 *
 * @uses GetMySqlVarValue
 */
function IsMySqlVarNonEmpty($oMysqli, $sVarName)
{
        $sResult = GetMySqlVarValue($oMysqli, $sVarName);
 
        return (!empty($sResult));
}
 
/**
 * @param \mysqli $oMysqli
 * @param string $sVarName
 *
 * @return string
 * @throws \MySQLException
 *
 * @uses 'SHOW STATUS' queries
 */
function GetMySqlVarValue($oMysqli, $sVarName)
{
        $oResults = $oMysqli->query("SHOW SESSION STATUS LIKE '$sVarName'", 1);
 
        if ($oResults === false) {
                return false;
        }
 
        $aResults = $oResults->fetch_array(MYSQLI_NUM);
        $sResult = $aResults[1];
 
        return $sResult;
}

When successful, you should see :

Trying to connect using :
 host=192.168.10.70, user=sha256user, port=3306
TLS options :
 key=/var/www/html/client-key.pem
 cert=/var/www/html/client-cert.pem
 ca=/var/www/html/ca.pem
Successfully connected using TLS !
TLS cipher=DHE-RSA-AES256-SHA

If you receive warning or error regarding

--ssl

or

--ssl-mode

such as :

iTop >= 2.6.2 should handle these warnings with MySQL >= 5.7.0, if you still encounter these warnings or encounter them with a lower version or another MySQL vendor, our can verify with :

SELECT Version()

and

SELECT @@version

That you receive your version and MySQL vendor in format similar to

 10.3.15-MariaDB-log 

These information will be processed by iTop to choose correct parameters according to your MySQL vendor/version and thus need to be be filled in.

If you have the following error when using mariadb mysqldump command check the version of mysqldump you are using: