1

I am in the middle of deploying a CiviCRM instance and have decided for scalability and security reasons to separate my web server and database server. I have a set of self-signed certificates installed on both the database server and the web server and Wordpress is using SSL. While CiviCRM is able to connect to the database server, I cannot get it to use SSL, which is a concern since I do not control the data center where both machines reside.

Has anyone dealt successfully with this?

I attempted to alter the URI for the database connection in civicrm.settings.php from:

if (!defined('CIVICRM_DSN')) {
  if (CIVICRM_UF === 'UnitTests' && isset($GLOBALS['_CV']['TEST_DB_DSN'])) {
    define('CIVICRM_DSN', $GLOBALS['_CV']['TEST_DB_DSN']);
  }
  else {
    define('CIVICRM_DSN', 'mysql://USERNAME:PASSWORD@DBHOSTNAME/DATABASENAME?new_link=true');
  }
}

to:

if (!defined('CIVICRM_DSN')) {
  if (CIVICRM_UF === 'UnitTests' && isset($GLOBALS['_CV']['TEST_DB_DSN'])) {
    define('CIVICRM_DSN', $GLOBALS['_CV']['TEST_DB_DSN']);
  }
  else {
    define('CIVICRM_DSN', 'mysql://USERNAME:PASSWORD@DBHOSTNAME/DATABASENAME?new_link=true&useSSL=true&ssl-ca=(/etc/mysql/ssl/cacert.pem)&ssl-cert=(/etc/mysql/ssl/client-cert.pem)&ssl-key=(/etc/mysql/ssl/client-key.pem)');
  }
}

This did not enable SSL, but instead caused me to receive the following error when loading any page:

Initialization Error

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => simpleHandler
        )

    [code] => -24
    [message] => DB Error: connect failed
    [mode] => 16
    [debug_info] =>  [nativecode=No such file or directory]
    [type] => DB_Error
    [user_info] =>  [nativecode=No such file or directory]
    [to_string] => [db_error: message="DB Error: connect failed" code=-24 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info=" [nativecode=No such file or directory]"]

I checked if this was a permission error (though I suspected not since wordpress was using SSL) by running 'chmod 666' on all of the key files, but continued to recieve the above error.

Anyway, if anyone has any tips or insight into this it would be greatly appreciated. If it helps, I'm running Ubuntu 18.04 w/ PHP 7.2 and the latest version of CiviCRM on Nginx.

Update

I've found a (temporary) solution by making the following edits:

Altered civicrm.settings.php, changing 'mysql' to 'mysqli' in the DSN definitions:

if (!defined('CIVICRM_DSN')) {
  if (CIVICRM_UF === 'UnitTests' && isset($GLOBALS['_CV']['TEST_DB_DSN'])) {
    define('CIVICRM_DSN', $GLOBALS['_CV']['TEST_DB_DSN']);
  }
  else {
    define('CIVICRM_DSN', 'mysqli://USERNAME:PASSWORD@DBHOSTNAME/DATABASENAME?new_link=true');
  }
}

Edited civicrm/civicrm/packages/DB/common.php, changing

var $options = array(
    'result_buffering' => 500,
    'persistent' => false,
    'ssl' => false,
    'debug' => 0,
    'seqname_format' => '%s_seq',
    'autofree' => false,
    'portability' => DB_PORTABILITY_NONE,
    'optimize' => 'performance',  // Deprecated.  Use 'portability'.
);

to

var $options = array(
    'result_buffering' => 500,
    'persistent' => false,
    'ssl' => true,
    'debug' => 0,
    'seqname_format' => '%s_seq',
    'autofree' => false,
    'portability' => DB_PORTABILITY_NONE,
    'optimize' => 'performance',  // Deprecated.  Use 'portability'.
);

Edited civicrm/civicrm/packages/DB/mysqli.php, changing

    if ($this->connection = @mysqli_real_connect(
            $init,
            $dsn['hostspec'],
            $dsn['username'],
            $dsn['password'],
            $dsn['database'],
            $dsn['port'],
            $dsn['socket'],))
    {

to

    if ($this->connection = @mysqli_real_connect(
            $init,
            $dsn['hostspec'],
            $dsn['username'],
            $dsn['password'],
            $dsn['database'],
            $dsn['port'],
            $dsn['socket'],
            MYSQLI_CLIENT_SSL))
    {

This works as far as forcing SSL for the database connection (confirmed by enforcing SSL on the server side and via tcpdump). However, it's quite kludgy to say the least and will have to be redone after each update if common.php or mysqli.php are modified. If anyone more familiar with CiviCRM internals has a more elegant solution, I'd love to hear it.

  • Just wondering where you saw to add "useSSL" and put the filenames in brackets? The PEAR DB docs themselves never reference "useSSL"? – Demerit Jul 19 at 5:10
  • @Demerit I had been looking at some generic MySQL documentation. Now that I've dug into the PEAR DB documentation, I believe I have a good DSN in my civicrm.settings.php, which is define('CIVICRM_DSN', 'mysql://USERNAME:PASSWORD@DBHOSTNAME/DATABASENAME?new_link=true&key=/etc/mysql/ssl/client-key.pem&cert=/etc/mysql/ssl/client-cert.pem&ca=/etc/mysql/ssl/cacert.pem'); Looking at the documentation, it appears that I need to set all of the DB connect calls to include "ssl=true" in $options, but I can't figure it out. – Taiming Jul 19 at 7:30
1

You don't actually have to replace mysql with mysqli since core already has a "hack" that will do this for you if the php mysqli extension is loaded. https://github.com/civicrm/civicrm-packages/blob/5.15.1/DB.php#L747

For the ssl, it seems reasonable to propose an update to that same function that lets you set the ssl option, either as part of CIVICRM_DSN or a separate CIVICRM_XXX setting in civicrm.settings.php. You can file a proposal at https://lab.civicrm.org/dev/core/issues

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.