asked on

Connect AWS EC2 instance to RDS MySQL through SSL using PHP

Hi:

I am unable to make my AWS EC2 instance connect to my RDS MySQL DB through SSL using PHP.

AWS EC2 Linux 2, Apache 2.4.39, PHP 7.3.10, MySQL 5.7.26

In order for my application that resides in EC2 to have a secure connection in transit, it must utilize SSL/TLS. My understanding that given my PHP/MySQL application, I need to perform the code below. In order not to affect my DB, I have set up a test DB in the same DB_server. The new user is called new-user with its own password. The bundled PEM file is rds-combined-ca-bundle.pem.

From various sources I put together the following code.

In AWS-test-ssl-script.php ..

34 require_once('AWS-test-config.php');
35 require(MYSQLI);

44 $sel = "CREATE USER IF NOT EXISTS 'new-user'@'%' IDENTIFIED BY 'password' REQUIRE SSL";
45 $sel_qry = mysqli_query($dbc, $sel);
46 mysqli_close($sel_qry);

// Simple test query ..

Open in new window

In AWS-test-config.php ..

define ('MYSQLI', 'AWS-test-connect.php');

Open in new window

In AWS-test-connect.php ..

12 $dbc=mysqli_init();
13 mysqli_ssl_set($dbc, NULL, "/dir/rds-combined-ca-bundle.pem", NULL, NULL, NULL);
14 mysqli_real_connect($dbc,"DB_server","new-user","password");

16 $res = mysqli_query($dbc, 'SHOW STATUS like "Ssl_cipher"');
17 print_r(mysqli_fetch_row($res));
18 mysqli_close($dbc);

Open in new window

Output ..

Warning: mysqli_real_connect(): Unable to set private key file `rds-combined-ca-bundle.pem' in AWS-test-connect.php on line 14
Warning: mysqli_real_connect(): Cannot connect to MySQL by using SSL in AWS-test-connect.php on line 14
Warning: mysqli_real_connect(): [2002] (trying to connect via (null)) in AWS-test-connect.php on line 14
Warning: mysqli_real_connect(): (HY000/2002): in AWS-test-connect.php on line 14
Warning: mysqli_query(): invalid object or resource mysqli in AWS-test-connect.php on line 16 
Warning: mysqli_fetch_row() expects parameter 1 to be mysqli_result, bool given in AWS-test-connect.php on line 17
Warning: mysqli_query(): Couldn't fetch mysqli in AWS-test-ssl-script.php on line 45
Warning: mysqli_close() expects parameter 1 to be mysqli, bool given in AWS-test-ssl-script.php on line 46

Open in new window

Any ideas as to why: "Unable to set private key file `rds-combined-ca-bundle.pem'" in line 14?
And how to make this SSL connection work?

Cheers

David Favor

Provide all steps you issued to generate your cert + associate your cert with your MySQL instance. This includes the grants you created to allow access for whatever AWS-test-config.php host/user/pass might be defined.

Torquil Beavis

ASKER

I got the pem file from https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem and placed it in a browser accessible directory as directed by the AWS page.

It seems creating grants is the knowledge I'm lacking. Please explain.

Torquil Beavis

ASKER

After the $sel = "CREATE USER IF NOT EXISTS .." statement above, I added ..

48 $grant = "GRANT SELECT, INSERT, UPDATE, DELETE
49 ON testdb
50 TO new-user@%";
51 $grant_qry = mysqli_query($dbc, $grant);
52 mysqli_close($grant_qry);

Open in new window

I get the same output.

These are all the steps I've taken to generate the cert and associate it with the MySQL instance.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.