5

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.

Improve this question
7
  • anything from php's error reporting? and are the certificates still valid with SSL installed and enabled in config? Commented Jul 13, 2017 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. Commented Jul 13, 2017 at 21:25
  • @Fred-ii- : old webserver was running apache2 and new one nginx. Where to enable mysql-ssl in nginx? Commented Jul 13, 2017 at 21:26
  • Am not familiar with nginx Commented Jul 13, 2017 at 21:28
  • @Fred-ii- : Thanks. Can you please take a look again? I have updated the error message more precisely. Commented Jul 14, 2017 at 19:15

5 Answers 5

Reset to default
3
+25

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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?
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
Let me recreate the certificates and try in both servers. I'll keep you posted with that.
@vrijdenker you should not create multiple answers on the same question. Please write, edit and modify one answer next time.
1

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.

Improve this answer

2 Comments

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?
Hmm no, bummer. sorry.
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.

Improve this answer

2 Comments

What if there are 2 clients connecting to same server. What should be the common name of server and 2 clients?
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 info.ssl.com/article.aspx?id=10048
0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.