PHP connection with Mysql with SSL does not work PDO

Question

Mysql server is running php5.3. Php7.1 is running on the new web server (ported from php5.3). When I try to connect a Mysql server with ssl, it does not work.

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 verification failed

But, when I remove the SSL block from the connection, it works fine. I do not know what's happening. There may be a mismatch between the server and client versions. Becasue I am using the old public key and private key.

Is this due to a mismatch between the client and mysql server versions?

PS: I updated php7 only on the web server.

Answer 1

So, after searching and reading, I believe that the problem is due to the fact that SSL processing was approved in PHP 5.6, and now verification is enabled by default.

Although the following is not about mysql, but fsock, I think this post answers your question: fooobar.com/questions/166223 / ...

You can either disable peer-to-peer verification (which is not a good idea), or fix your root certificates. I think it would be nice to check by disabling peer-to-peer checking to make sure this is really your problem.

(Note that I added a second answer along with my previous answer. This was not the answer to your question, but it may be relevant to others)

Answer 2

You say that you "copied SSL keys from one server to another." So is there a new server? The new server has a new identifier, so the remote server will reject the certificates because the identifier does not match.

I think you should first delete the line on the remote server from the file "known_hosts". Then on your Nginx server, you must manually connect to the remote server using these SSH keys and establish a connection once to add a new identifier to known_hosts.

After that, I think it should work.

Even if the above does not work, I find it better to debug this problem by manually connecting to the remote host. When this works, you can try to establish a mysql connection.

Answer 3

You can create a new SSL certificate and make sure that you are using the correct "Common Name":

 CA: hostname Server: FQDN, eg hostname.example.com Client: somename 

An important part is the server certificate, in which the common name should be the same as the host to which you are connecting, for example. hostname.example.com.

Answer 4

You need to create a new SSL certificate and check the endpoint on the server to make sure that it works correctly. After that you should not have any problems. I recommend using Let encrypt : it is free, open, and highly supported. Make sure you follow the instructions here afterword.

Answer 5

With trial and error, I was able to fix this problem without disabling peer-to-peer testing, partly thanks to the MySQL documentation:

Important

Whatever method you use to create the certificate and key files, the common name used for the server / client certificate / key must be different from the common name used for the CA certificate. Otherwise, the certificate and key files will not work on servers compiled using OpenSSL. Typical error in this case:

 ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1) 

MySQL documentation

However, this only gave me part of the way. PHP defaults to VERIFY_IDENTITY , which requires a hostname match for a common name.

This satisfies everything:

CA: Assign a unique name. It could be anything. I just add root. in my fully qualified domain name.

client and server: assign the fully qualified domain name of the MySQL server. These two values ​​must match.

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

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