4

I have configured MySQL SSL in ubuntu server.

show variables like "%ssl%";
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/ca-cert.pem     |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+

Current PDO connection in PHP script is something like this:

try {
    $this->_conn = $this->dbh = new PDO('mysql:host=' . DB_SERVER . ';dbname='. DB_NAME, DB_USER, DB_PASS);
    $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Couldn't connect to database. Please try again!");
}

I tried to adding this array in connection. but, it's not working.

array(PDO::MYSQL_ATTR_SSL_KEY  => '/etc/mysql/client-key.pem',
      PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/client-cert.pem',
      PDO::MYSQL_ATTR_SSL_CA   => '/etc/mysql/ca-cert.pem'
     );

Now, the question is: How can I connect with MySQL database under secure connection (SSL)? What changes do I need to do in order to add make it secure?

I have generated client and server certificate by this:

openssl genrsa -out ca-key.pem 2048;
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -out ca-cert.pem;
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem -out server-req.pem;
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem;
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem -out client-req.pem;
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem;

ERROR MESSAGE

SQLSTATE[HY000] [2026] SSL connection error: Unable to get private key

RNK
  • 5,582
  • 11
  • 65
  • 133
  • 1
    Are those `/etc/mysql/*.pem` paths on the server or the client? http://stackoverflow.com/questions/26121917/pdo-not-working-with-ssl – Michael Berkowski Nov 13 '15 at 20:25
  • all client and server `.pem` files are under this path: `/etc/mysql` – RNK Nov 13 '15 at 20:26
  • I see that, but is that path /etc/mysql on the server's filesystem or on the client's filesystem? The client certs & keys must exist on the client's filesystem according to that other question. – Michael Berkowski Nov 13 '15 at 20:29
  • Also, those files should have read access by the user/group running the php scripts. – hjpotter92 Nov 13 '15 at 20:31
  • @MichaelBerkowski: I don't know what do you mean by client and server file system. Sorry about that. But I have this permission for all files: `-rw-r--r--` – RNK Nov 13 '15 at 20:32
  • If you are using SSL, it usually implies that the MySQL server you are connecting to does not reside on the same hardware as the PHP application code connecting to it (because there's no great need for encrypted connections on the same hardware). I assume then that your PHP code is connecting to a _remote_ MySQL server, and if that is the case, the client certs/keys must be on the machine where the PHP code is executing while the server certs/keys must be on the MySQL server. – Michael Berkowski Nov 13 '15 at 20:36
  • @MichaelBerkowski: I have `MySQL` installed under same server with `PHP`. I am learning this because in future I am planning to do replica of current database on different server. – RNK Nov 13 '15 at 20:40
  • Ok then - you must check the actual error from PDO. `echo $e->getMessage()` inside your `catch` block to see what MySQL's reported error is. – Michael Berkowski Nov 13 '15 at 20:43
  • @MichaelBerkowski: Error message is: `SQLSTATE[HY000] [2026] SSL connection error: Unable to get private key` – RNK Nov 13 '15 at 20:44
  • http://stackoverflow.com/q/17704325/1190388 – hjpotter92 Nov 13 '15 at 20:48
  • Here's something to verify: http://forums.mysql.com/read.php?11,400856,401127#msg-401127 And is there a passpharse? http://stackoverflow.com/questions/17704325/mysql-ssl-remote-connection-error-unable-to-get-private-key – Michael Berkowski Nov 13 '15 at 20:49
  • Now, the error is: `protocol version mismatch` – RNK Nov 13 '15 at 21:13
  • https://bugs.mysql.com/bug.php?id=64870 – hjpotter92 Nov 13 '15 at 21:55
  • Thanks.. I figured that out. I directly changed `server-key.pem` header with `RSA` – RNK Nov 13 '15 at 21:56
  • Is it good practice to use SSL if `PHP` and `MySQL` are under same server? Does it make any difference? – RNK Nov 13 '15 at 21:57
  • 1
    @Ronak Patel, i think that looks like; husband and wife are talking with encrypted in the bedroom. That is useless. – HddnTHA Nov 13 '15 at 22:36
  • 2
    so, what is the answer? – hakki Nov 20 '15 at 15:15

0 Answers0