Problem connecting to database Azure MySQL (with SSL)

[HansB]

I’m trying to run passbolt docker in Kubernetes (Azure AKS) and using the Microsoft MySQL-service (with SSL enabled) as storage.

Using the instructions from https://docs.microsoft.com/en-us/azure/mysql/howto-configure-ssl

(changing slashes to underscore because 2-link-posting-policy)

We are supplying config using the following env-vars
APP_FULL_BASE_URL = ##########
DATASOURCES_DEFAULT_DATABASE = passbolt (name of mysql-database with utf8_general_ci collation)
DATASOURCES_DEFAULT_HOST = #######.mysql.database.azure.com
DATASOURCES_DEFAULT_USERNAME = passbolt
DATASOURCES_DEFAULT_PASSWORD = #############
DATASOURCES_DEFAULT_SSL_CA = _full_path_to_BaltimoreCyberTrustRoot.ca.pem

Done troubleshooting
We have confirmed that mounting the CA-cert works
We have other working services in same K8-cluster using the same MySQL-service

To reproduce

(using instructions from the “Docker passbolt installation”-page)
File “BaltimoreCyberTrustRoot.ca.pem” is located in $(pwd)

docker run -a STDOUT -a STDERR --rm --name passbolt
-p 443:443
-p 80:80
-v "$(pwd)":/dockermount
-e DATASOURCES_DEFAULT_HOST=#######.mysql.database.azure.com
-e DATASOURCES_DEFAULT_PASSWORD=#############
-e DATASOURCES_DEFAULT_USERNAME=passbolt
-e DATASOURCES_DEFAULT_DATABASE=passbolt
-e DATASOURCES_DEFAULT_SSL_CA=_dockermount_BaltimoreCyberTrustRoot.ca.pem
-e APP_FULL_BASE_URL==#############
passbolt_passbolt:latest

Log

gpg; directory ‘home_www-data.gnupg’ created
gpg; keybox ‘home_www-data.gnupg_pubring.kbx’ created
gpg; home_www-data.gnupg_trustdb.gpg; trustdb created
gpg; key 7B9453509FD22A59 marked as ultimately trusted
gpg; directory ‘home_www-data.gnupg_openpgp-revocs.d’ created
gpg; revocation certificate stored as ‘home_www-data.gnupg_openpgp-revocs.d_524D3E2CD1A7342BCFD16DF97B9453509FD22A59.rev’
gpg; key 7B9453509FD22A59; “Passbolt default user <passbolt(at)yourdomain_com>” not changed
gpg; Total number processed; 1
gpg; unchanged; 1
gpg; key 7B9453509FD22A59; “Passbolt default user <passbolt(at)yourdomain_com>” not changed
gpg; key 7B9453509FD22A59; secret key imported
gpg; Total number processed; 1
gpg; unchanged; 1
gpg; secret keys read; 1
gpg; secret keys unchanged; 1
Generating a RSA private key
…++++
…++++
writing new private key to ‘_etc_ssl_certs_certificate.key’
gpg; checking the trustdb
gpg; marginals needed; 3 completes needed; 1 trust model; pgp
gpg; depth; 0 valid; 1 signed; 0 trust; 0-, 0q, 0n, 0m, 0f, 1u
(passbolt-banner)
Open source password manager for teams
Running baseline checks, please wait…
Exception; SQLSTATE[HY000] [2002] in [_var_www_passbolt_vendor_cakephp_cakephp_src_Database_Driver.php, line 92]
(passbolt-banner)
Open source password manager for teams
Running migration scripts.
using migration paths
_var_www_passbolt_config_Migrations
using seed paths
_var_www_passbolt_config_Seeds
Exception; There was a problem connecting to the database; SQLSTATE[HY000] [2002] in [_var_www_passbolt_vendor_robmorgan_phinx_src_Phinx_Db_Adapter_MysqlAdapter.php, line 127]

[garrett]

Hi @HansB, I might check with the MySQL end of things and see if there are error logs. Not knowing the incoming host could be an issue, or security requirements maybe? If there is nothing there you might want to make sure passbolt can resolve the Azure mysql url you provided.

[HansB]

I can connect to that server just fine from the same computer I’m running the docker command on using other tools.

[garrett]

Understood. How about passbolt resolution? Did your /etc/hosts file already include the Azure db ip?

[HansB]

Well, I do think the DNS-resolution works just fine, because if I omit the SLL_CA-env-var in the docker-command the 2 “Exception”-lines becomes:

Exception: SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry. in [/var/www/passbolt/vendor/cakephp/cakephp/src/Database/Driver.php, l
ine 92]

and

Exception: There was a problem connecting to the database: SQLSTATE[HY000] [9002] SSL connection is required. Please specify SSL options and retry. in [/var/www/passbolt/v
endor/robmorgan/phinx/src/Phinx/Db/Adapter/MysqlAdapter.php, line 127]

[HansB]

The following works just fine from my workstation:
<?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);

$db = mysqli_init();
mysqli_options ($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

$db->ssl_set('', '', 'BaltimoreCyberTrustRoot.crt.pem', NULL, NULL);
$link = mysqli_real_connect ($db, '#####.mysql.database.azure.com', 'passbolt', '####', 'passbolt', 3306, NULL, MYSQLI_CLIENT_SSL);
if (!$link)
{
die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
$res = $db->query('SHOW TABLES;');
print_r ($res);
$db->close();
}
?>

I have also searched (googled) for ssl-issues in “cakephp” but couldn’t find any pages that helped me.

[diego]

Hi @HansB,

I’m digging on this issue. Have you tried to execute the php snippet you posted on a passbolt container or just in your workstation?

[HansB]

I have run in on my workstation.

When i try to run it in the passbolt-image PHP says: “Call to undefined function mysqli_init()”
I’ve tried to add php_mysql (with variants) using apt install but that can’t be found. (missing some source maybe) (did some searching on the errors but nothing seemed to allow me to install that package)

[diego]

To install mysqli you should use docker-php-ext-install mysqli

[HansB]

That installed like a charm.

The test-scripts runs inside the passbolt-image with identical result as on my workstation.

[diego]

I have been debugging a bit with @remy
Could be that you are referencing the file on the env var incorrectly?

You use:

DATASOURCES_DEFAULT_SSL_CA = _full_path_to_BaltimoreCyberTrustRoot.ca.pem

But the file when downloaded comes with .crt.pem extension which is the one you use on the snippet:

$db->ssl_set('', '', 'BaltimoreCyberTrustRoot.crt.pem', NULL, NULL);

I have been banging my head for a good while.

[HansB]

The test-script (using mysqli) executes in the same folder as the .pem-file (and will give a different error if filename is misspelled)

The server is reported as “MariaDB or MySQL 5.7.27” and is an Azure-hosted service called “Azure Database for MySQL server”

[diego]

Hi @HansB!

I have succesfully ran passbolt against azure mysql instance using SSL with the following parameters (using docker-compose stack provided in https://github.com/passbolt/passbolt_docker)

DATASOURCES_DEFAULT_HOST=####.mysql.database.azure.com
DATASOURCES_DEFAULT_USERNAME=user@####
DATASOURCES_DEFAULT_PASSWORD=pass
DATASOURCES_DEFAULT_DATABASE=passbolt
DATASOURCES_DEFAULT_SSL_CA=/var/www/passbolt/cert/BaltimoreCyberTrustRoot.crt.pem

Ensure the pem file is mounted with proper permissions so www-data user can read it. Also ensure an empty database schema exists on the azure instance and that the user you are using has the proper rights to access and create tables on it.

[HansB]

After some more tries I’ve managed to get passbolt to start and talk to the database when I run a local docker-image, so the SSL-part works and this issue can be closed.

I’ve yet to get it to in our Kubernetes-cluster (in same cluster as a Grafana-instance that already talks to that same MySQL-server, so there is no firewalling-issue that’s the issue.