PHP Mysql connection with SSL is not working PDO

Question

Mysql server is running php5.3. New webserver is running php7.1 (migrated from php5.3). When I try to connect Mysql server with ssl its not working.

try {
$dbh = new PDO($dsn, $user, $password, 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')
              );
    echo "Connestion established";
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Connection failed: SQLSTATE[HY000] [2002]

PDO::__construct(): SSL operation failed with code 1. OpenSSL Error messages: error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed

But, When I remove SSL block from connection, its working fine. I don't know what's going on. May be version mismatch of server and client. Becasue I am using old public key and private key.

Is it because of mysql client and server version mismatch?

PS: I have upgraded php7 in webserver only.

anything from php's error reporting? and are the certificates still valid with SSL installed and enabled in config? — Funk Forty Niner, Jul 13 '17 at 21:22
@Fred-ii- Nothing in `php` error as I am using try-catch. I have never changed certificates. Just copy-paste from old webserver to new one. — RNK, Jul 13 '17 at 21:25
@Fred-ii- : old webserver was running apache2 and new one nginx. Where to enable mysql-ssl in nginx? — RNK, Jul 13 '17 at 21:26
@Fred-ii- : Thanks. Can you please take a look again? I have updated the error message more precisely. — RNK, Jul 14 '17 at 19:15
@RickJames: Yeah. But, I couldn't find anything related to that online. Hope to get the solution for this soon! — RNK, Jul 17 '17 at 19:09

score 3 · Answer 1 · edited Jun 22 '19 at 18:12

3

So, after searching and reading I believe that the problem is due to the fact that SSL handling has been approved as of PHP 5.6 and peer verification is now on by default.

While the following is not about mysql but about fsock, I think this post answers your question: https://stackoverflow.com/a/32366242/2459026

You could either disable peer verification (which tends not to be a good idea) or fix your root certificates. I think it would be good to test by disabling peer verification to be sure that this is your problem, indeed.

(Please note that I added a second answer, alongside my previous answer. That wasn't the answer to your question, but might be relevant to others)

edited Jun 22 '19 at 18:12

answered Jul 17 '17 at 17:13

vrijdenker

1,371
1
12
25

Added this block before connecting to the DB server. `stream_context_set_default([ 'ssl' => [ 'verify_peer_name' => false, 'verify_peer' => false ] ]);` Still, getting the same error. Am I missing something? – RNK Jul 17 '17 at 17:35
The one you send me is the solution for this error: `SSL3_GET_SERVER_CERTIFICATE:certificate verify failed` but in my case error is `tls_process_server_certificate:certificate verify failed` – RNK Jul 17 '17 at 17:47
Let me recreate the certificates and try in both servers. I'll keep you posted with that. – RNK Jul 17 '17 at 19:49
@vrijdenker you should not create multiple answers on the same question. Please write, edit and modify one answer next time. – Christos Lytras Jul 24 '17 at 14:59

score 1 · Answer 2 · answered Jul 17 '17 at 15:14

You say that you "copied the SSL keys from one server to another". So there is a new server? The new server has a new identity, so the remote server will reject the certificates because the ID doesn't match.

I think you should first remove the line on the remote server from the "known_hosts" file. Then on your Nginx server you should manually connect to the remote server using those SSH keys and make the connection one time in order to add the new identity to the known_hosts.

After that I think it should work.

Even if the above doesn't work, I think it's best to debug this issue by connecting to the remote host manually. When that works you can try to setup the mysql connection.

keypairs generates from `MySQL` server. I am just changing the client side of part. Secondly, if I create new client-server with php5.3 everything is working as before. May be its php version issue or apache2 or nginx config. What are your thoughts on that? — RNK, Jul 17 '17 at 15:21

score 1 · Answer 3 · answered Jul 23 '17 at 08:33

1

You could generate new SSL certificate and make sure that you use the right "Common Name":

CA: hostname 
Server: FQDN, e.g. hostname.example.com 
Client: somename

The important part is the server certificate where the Common Name has to be the same as the host you are connecting to, e.g. hostname.example.com.

answered Jul 23 '17 at 08:33

Max Chernopolsky

617
6
17

What if there are 2 clients connecting to same server. What should be the common name of server and 2 clients? – RNK Jul 24 '17 at 14:23
1

When you'll generate a certificate, you will be asked for several arguments, e.g. State (S), Locality (L), Common Name (CN). So CN should be exact same address, you connecting from clients. If your MySQL running on `db.myhost.com`, you should use `db.myhost.com` as a CN and connect to `db.myhost.com` from the clients http://info.ssl.com/article.aspx?id=10048 – Max Chernopolsky Jul 25 '17 at 09:20

score 0 · Answer 4 · answered Jul 20 '17 at 12:22

You need to generate a new SSL Certificate and test an endpoint on the server to ensure that it is working properly. You should have no problem after. I recommend using Let's Encrypt: it is free, open, and highly supported. Make sure you follow the instructions here afterword.

smcjones · Answer 5 · 2018-07-02T23:07:41.593

With some trial and error I was able to fix this issue without disabling peer verification, thanks in part to MySQL documentation:

Important

Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. A typical error in this case is:
ERROR 2026 (HY000): SSL connection error:
error:00000001:lib(0):func(0):reason(1)
MySQL documentation

However, this only got me part of the way. By default PHP has VERIFY_IDENTITY enabled, which requires a hostname match for the Common Name.

This satisfies everything:

CA: assign a unique name. Can be anything. I just prepend root. to my FQDN.

client and server: Assign the FQDN of the MySQL server. These two values must match.

If the FQDN does not match between client and server, then VERIFY_IDENTITY will fail.

If the FQDN does match between ca, client and server, then OpenSSL in PHP will fail as promised in MySQL documentation.

PHP Mysql connection with SSL is not working PDO

5 Answers5