SSL protected PDO connection not working - SQLSTATE SSL Error - Access denied for user (using password: YES)

Question

I created a user via MySQL Workbench.

    CREATE USER 'fba_user'@'%' IDENTIFIED BY 'my_Pa$$word';
    CREATE USER 'fba_user'@'localhost' IDENTIFIED BY 'my_Pa$$word';
    FLUSH PRIVILEGES;
    GRANT ALL PRIVILEGES ON fallbackauthdb.* TO 'fba_user'@'%' IDENTIFIED BY 'my_Pa$$word' REQUIRE SSL;
    GRANT ALL PRIVILEGES ON fallbackauthdb.* TO 'fba_user'@'localhost' IDENTIFIED BY 'my_Pa$$word' REQUIRE SSL;
    FLUSH PRIVILEGES;

I configured the MySQL Server (5.5.34-0ubuntu0.13.04.1) to use SSL by generating the needed certificates and keys and configured them via /etc/mysql/my.cnf

If I try to connect via CLI on a client machine everything just works great.

    user@ClientPC:~$ mysql -h mysql.example.com --port=3306  -v --ssl-ca=/home/user/demo/ca-cert.pem --ssl-cert=/home/user/demo/client-cert.pem --ssl-key=/home/user/demo/client-key.pem -u fba_user -p
    Enter password: my_Pa$$word

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 99
    Server version: 5.5.34-0ubuntu0.13.04.1 (Ubuntu)

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Reading history-file /home/user/.mysql_history
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> SHOW STATUS LIKE "%SSL%";
    --------------
    SHOW STATUS LIKE "%SSL%"
    --------------

    +--------------------------------+------------------------+
    | Variable_name                  | Value                  |
    +--------------------------------+------------------------+
    | Com_show_processlist           | 0                      |
    | Ssl_accept_renegotiates        | 0                      |
    | Ssl_accepts                    | 0                      |
    | Ssl_callback_cache_hits        | 0                      |
    | Ssl_cipher                     | DHE-RSA-AES256-SHA     |
    | ...                            | ...                    |
    +--------------------------------+------------------------+

But if I use PHP (PHP/5.4.9-4ubuntu2.3) with PDO to connect to the database, the connection is not working. I tried php5-mysql driver and I tried the php5-mysqlnd (Native Driver). Both do not change a thing.

    <?php
    error_reporting(E_ALL);
    ini_set("display_errors", "1");

      if (!defined('PDO::ATTR_DRIVER_NAME')) {
        echo 'PDO unavailable';
      }
      elseif (defined('PDO::ATTR_DRIVER_NAME')) {
        echo 'PDO available';
      }

      $pdo = new PDO('mysql:host=mysql.example.com;dbname=fallbackauthdb;charset=utf8', 'fba_user', 'my_Pa$$word', 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'
      ));

      foreach ($pdo->query('SHOW STATUS LIKE "%Ssl%"') as $row) {
        print_r($row[0] ."=". $row[1] . PHP_EOL);
      }
    ?>

Thats the error message I got:

    PDO available
    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1045] Access denied for user 'fba_user'@'mysql.example.com' (using password: YES)' in /var/www/gfa/sql.php:17 Stack trace: #0 /var/www/gfa/sql.php(17): PDO->__construct('mysql:host=mysq...', 'fba_user', 'my_Pa$$...', Array) #1 {main} thrown in /var/www/gfa/sql.php on line 17

If I disable SSL by "not requireing" it on the fba_user, the connection with PDO just works fine. I think its a bug, but maybe someone else can help please?

EDIT 1: If I use the database-root user instead, the connection is working, but I'm not sure if ssl is used, because the query:

     SHOW STATUS LIKE "%SSL%";

does return

     DHE-RSA-AES256-SHA

but I think the system is lying and is not using SSL secured mysql-connection at all.

EDIT 2: I sniffed the packets and yes, for the root-User the connection is secured via SSL, but for the fba_user this isn't the case.

Why I thought the system is lying?: I can change the path in the PHP script for the client certificates and I can even delete them from the system and reboot the server. The connection is still SSL encrypted, WTF?

Why do you think the system is lying? You could always run a packet sniffer to inspect the data being sent to and from the database server. — madebydavid, Nov 22 '13 at 16:18
I just sniffed the connection, and indeed the traffic is transmitted via a SSL protected channel, if I use the root-User. But nevertheless the problem is still present, for the fba_user the ssl connection does not work. — m33x, Nov 23 '13 at 10:52
Why is the system lying? I can change the path to the client cert, I can delete them but the connection is still ssl encrypted. WTF? array( PDO::MYSQL_ATTR_SSL_KEY => 'not/a/valid/path/foo', PDO::MYSQL_ATTR_SSL_CERT => 'not/a/valid/path/bar', PDO::MYSQL_ATTR_SSL_CA => 'not/a/valid/path/lol' )); The connection is still ssl-enabled and encrypted, why the hell? — m33x, Nov 23 '13 at 10:55
Did you specify them in the my.cnf? I think PDO reads the my.cnf - http://php.net/manual/en/ref.pdo-mysql.php scroll down to the bit about PDO::MYSQL_ATTR_READ_DEFAULT_FILE. If it is reading my.cnf then maybe the invalid values are overridden. — madebydavid, Nov 23 '13 at 17:45
Yeah, I specified them there, but at least after I deleted them (client-key.pem, client-cert.pem and client-req.pem) and rebooted the server, the connection should be no longer ssl tunneled. But it still is! Why? And why does this only work for the mysql-root-User? — m33x, Nov 24 '13 at 11:41

score 0 · Answer 1 · edited May 23 '17 at 11:44

I have the same issue at post Can't connect with PDO using ssl but mysqli with ssl works

I would suggest not using root user because I don't think you can force SSL and PDO will fail silently making you think it's working or packets could be encrypted if you're already using SSL (https) I think. An easy test is to just make another user, force ssl, try command line remote connection than use PDO ssl and connection will fail/be blocked and tell you SSL is not working via PDO.

Anyways we had to switch to MySQLI to get DB SSL properly working and using PDO did no work at all. I am still looking for ways to diagnose/debug and figure out why we can't connect using PDO ssl because it's a problem and some third party code ships with PDO as well meaning we can't use their code if we can't figure this out and we do have cause to use the multiple drivers PDO supports so I am trying to figure this out.

If you've made any progress let me know :).

No progress yet. I think that this is maybe an apparmor issue? — m33x, May 01 '14 at 21:20

score -1 · Answer 2 · answered Jan 25 '16 at 06:01

Following code will help you

        PDO('mysql:host='.HOST.';dbname='.DBNAME.';charset=utf8', USER, PASSWORD, 
                array( PDO::MYSQL_ATTR_SSL_KEY =>'/mysqlsslcertificate1/client-key.pem',

                       PDO::MYSQL_ATTR_SSL_CERT=>'/mysqlsslcertificate1/client-cert.pem', 
                        PDO::MYSQL_ATTR_SSL_CA =>'/mysqlsslcertificate1/ca-cert.pem' )
 );

SSL protected PDO connection not working - SQLSTATE SSL Error - Access denied for user (using password: YES)

2 Answers2