By bclark0001 on

I'm trying to get "data in transit" via SSL between Drupal 7 to RDS Mysql. I've confirmed the following:

* SSL is configured by default in all Mysql RDS instances.
* The Mysql instance I have brought up can be connected to using Mysql client using the following command:

mysql --ssl-ca=/path/to/ssl-cert --user=USERID --host=HOSTNAME -p

I've also added a Require SSL directive for this user I am trying to connect as:

GRANT USAGE ON *.* TO 'USERNAME'@'%' REQUIRE SSL
GRANT USAGE ON *.* TO 'USERNAME'localhost'%' REQUIRE SSL

I've confirmed (using the above command) that this user can login if the --ssl-ca argument is passed, but cannot login without it.

I then started digging in to determine how to configure Drupal 7 to have it connect to Mysql using SSL. I found this post

This post implies quite strongly that this support should now be configurable. I've confirmed that the version of Drupal I'm using does have this patch applied. What I cannot seem to find or figure out is exactly how I turn SSL support on for the connection. I've tried the following configurations for my databases array:

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'DBNAME',
      'username' => 'USERNAME',
      'password' => 'PASSWORD',
      'host' => 'HOSTNAME',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
      'ssl-ca' => 'ssl-ca=/path/to/ssl-cert',
    ),
  ),
);

// Other versions that don't work
'ssl-ca' => '/path/to/ssl-cert',

'init_commands' => array(
  'ssl-ca' => 'ssl-ca=/path/to/ssl-cert',
),
'init_commands' => array(
  'ssl-ca' => 'ssl-ca=/path/to/ssl-cert',
),
'init_commands' => array(
  'ssl-ca' => 'set MYSQL_ATTR_SSL_CA=/path/to/ssl-cert',
),

In each case, the database authentication fails. Note: I have confirmed that this same databases array works when I turn off REQUIRES SSL in the database.

Has anyone gotten this to work?

⋅ Categories: Drupal 7.x

Comments

webdrips’s picture

webdrips commented

Hi bclark0001, did you ever get this working?

Looking to Migrate from Drupal 6/7 to Drupal 10/11? Have a look at our Drupal 11 demo site and request access.

pozzobalbi’s picture

pozzobalbi commented

I did not find a way to setup a new site using SSL. So during the setup phase you cannot use SSL. That means, after turning on SSL you should change your passwords to ensure data safty and hope that no one use the small window during setup and change of password in a malicious way.

After you have your drupal site up and running, update settings.php as follows:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'drupal7',
      'username' => 'drupaladmin',
      'password' => 'P@ssw0rd',
      'host' => '12.23.34.45',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
      'pdo' => array (
            PDO::MYSQL_ATTR_SSL_KEY => '/etc/pki/tls/private/client-key.pem',
            PDO::MYSQL_ATTR_SSL_CERT => '/etc/pki/tls/certs/client-cert.pem',
            PDO::MYSQL_ATTR_SSL_CA => '/etc/pki/tls/certs/ca-cert.pem',
                     ),
      ),
  ),
);

Adjust the paths and names to your certificates as needed. Restart apache or what ever webserver you are using.

On the MYSQL server use 

GRANT USAGE ON database.* TO 'USERNAME'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

to force use of SSL.

If you should need to revoke that, you can use the following command:

GRANT USAGE ON database.* TO 'USERNAME'@'%' REQUIRE NONE;
FLUSH PRIVILEGES;

I hope that helps.

gateway69’s picture

gateway69 commented

I have just run into the same problem, here is what currently is setup.

Basically used this guide https://www.digitalocean.com/community/tutorials/how-to-configure-ssl-tl...

Servers have private ip's to communicate though.

  • Dedicated Mysql Server
    • mysql setup following the guide above. 
    • SSL Certs Generated with sudo mysql_ssl_rsa_setup --uid=mysql
    • Existing Database dumped from current server, and imported into dedicated mysql box
    • Created an account on the dedicated server with the *private* ip
    • Tested SSL connection from client machine to mysql server from command line (worked)
    • Copied over ssl certs and added the PDO values to the settings
    • Tested connecting to the db via Drupal (didnt work)

Error:

PDOException: SQLSTATE[HY000] [2002] in lock_may_be_available() (line 167 of /var/www/xxxx/includes/lock.inc).

settings block:

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'xxx',
      'username' => 'xxx',
      'password' => 'xxxx',
      'host' => 'xxx',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
      'pdo' => array (
        PDO::MYSQL_ATTR_SSL_KEY => '/var/www/abc/client-key.pem',
        PDO::MYSQL_ATTR_SSL_CERT => '/var/www/abc/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_CA => '/var/www/abc/ca-cert.pem',
      ),
    ),
  ),
);
  • Drupal Version: 7.58
  • Mysql 5.7.21
  • PHP 7.1

Any ideas? 

rjbrown99’s picture

rjbrown99 commented

I realize the last post was over a year ago, but this is a helpful reference:

https://www.kamenov.biz/php-application-cannot-connect-to-mysql-over-ssl/

--
Like us, follow us, or visit us!