Answers.Body: <p>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. <a href="https://github.com/civicrm/civicrm-packages/blob/5.15.1/DB.php#L747" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-packages/blob/5.15.1/DB.php#L747</a></p>
  
  <p>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 <a href="https://lab.civicrm.org/dev/core/issues" rel="nofollow noreferrer">https://lab.civicrm.org/dev/core/issues</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-10T14:02:13.003Z
Answers.Text: Adding link to lab ticket: https://lab.civicrm.org/dev/core/issues/1137
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-07-19T19:06:20.367Z
Answers.LastActivityDate: 2019-07-19T19:06:20.367Z
Answers.UserDisplayName: Demerit
Body: <p>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.</p>
  
  <p>Has anyone dealt successfully with this?</p>
  
  <p>I attempted to alter the URI for the database connection in civicrm.settings.php from:</p>
  
  <pre><code>if (!defined('CIVICRM_DSN')) {
    if (CIVICRM_UF === 'UnitTests' &amp;&amp; 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');
    }
  }
  </code></pre>
  
  <p>to:</p>
  
  <pre><code>if (!defined('CIVICRM_DSN')) {
    if (CIVICRM_UF === 'UnitTests' &amp;&amp; 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&amp;useSSL=true&amp;ssl-ca=(/etc/mysql/ssl/cacert.pem)&amp;ssl-cert=(/etc/mysql/ssl/client-cert.pem)&amp;ssl-key=(/etc/mysql/ssl/client-key.pem)');
    }
  }
  </code></pre>
  
  <p>This did not enable SSL, but instead caused me to receive the following error when loading any page:</p>
  
  <pre><code>Initialization Error
  
  Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; simpleHandler
          )
  
      [code] =&gt; -24
      [message] =&gt; DB Error: connect failed
      [mode] =&gt; 16
      [debug_info] =&gt;  [nativecode=No such file or directory]
      [type] =&gt; DB_Error
      [user_info] =&gt;  [nativecode=No such file or directory]
      [to_string] =&gt; [db_error: message="DB Error: connect failed" code=-24 mode=callback callback=CRM_Core_Error::simpleHandler prefix="" info=" [nativecode=No such file or directory]"]
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>Update</strong></p>
  
  <p>I've found a (temporary) solution by making the following edits:</p>
  
  <p>Altered civicrm.settings.php, changing 'mysql' to 'mysqli' in the DSN definitions: </p>
  
  <pre><code>if (!defined('CIVICRM_DSN')) {
    if (CIVICRM_UF === 'UnitTests' &amp;&amp; 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');
    }
  }
  </code></pre>
  
  <p>Edited civicrm/civicrm/packages/DB/common.php, changing</p>
  
  <pre><code>var $options = array(
      'result_buffering' =&gt; 500,
      'persistent' =&gt; false,
      'ssl' =&gt; false,
      'debug' =&gt; 0,
      'seqname_format' =&gt; '%s_seq',
      'autofree' =&gt; false,
      'portability' =&gt; DB_PORTABILITY_NONE,
      'optimize' =&gt; 'performance',  // Deprecated.  Use 'portability'.
  );
  </code></pre>
  
  <p>to</p>
  
  <pre><code>var $options = array(
      'result_buffering' =&gt; 500,
      'persistent' =&gt; false,
      'ssl' =&gt; true,
      'debug' =&gt; 0,
      'seqname_format' =&gt; '%s_seq',
      'autofree' =&gt; false,
      'portability' =&gt; DB_PORTABILITY_NONE,
      'optimize' =&gt; 'performance',  // Deprecated.  Use 'portability'.
  );
  </code></pre>
  
  <p>Edited civicrm/civicrm/packages/DB/mysqli.php, changing</p>
  
  <pre><code>    if ($this-&gt;connection = @mysqli_real_connect(
              $init,
              $dsn['hostspec'],
              $dsn['username'],
              $dsn['password'],
              $dsn['database'],
              $dsn['port'],
              $dsn['socket'],))
      {
  </code></pre>
  
  <p>to</p>
  
  <pre><code>    if ($this-&gt;connection = @mysqli_real_connect(
              $init,
              $dsn['hostspec'],
              $dsn['username'],
              $dsn['password'],
              $dsn['database'],
              $dsn['port'],
              $dsn['socket'],
              MYSQLI_CLIENT_SSL))
      {
  </code></pre>
  
  <p>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.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-19T05:10:05.817Z
Comments.Text: Just wondering where you saw to add "useSSL" and put the filenames in brackets? The PEAR DB docs themselves never reference "useSSL"?
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-07-19T07:30:44.767Z
Comments.Text: @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](https://pear.php.net/manual/en/package.database.db.intro-connect.php), 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.
Comments.UserDisplayName: Taiming
Tags: <php><mysql><ssl><mariadb>
Title: SSL-encypted Database connection for CiviCRM?
ThreadText: # SSL-encypted Database connection for CiviCRM?
  
  (Asked by: username_0 on 2019-07-19)
  
  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.
  
  **Tags:** php, mysql, ssl, mariadb
  
  > **Comments:**
  > **username_1**: Just wondering where you saw to add "useSSL" and put the filenames in brackets? The PEAR DB docs themselves never reference "useSSL"?  
  > **username_0**: @username_1 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](https://pear.php.net/manual/en/package.database.db.intro-connect.php), 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.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2019-07-19. Score: 2 | Accepted answer
  
  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>
  
  > **Comments:**
  > **username_1**: Adding link to lab ticket: https://lab.civicrm.org/dev/core/issues/1137  
  
  ---
  
  