MySQL connection over SSL with Laravel

Question

I have a problem connecting to my database over ssl in a Laravel application. My config is as follows:

       'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
            'sslmode' => 'require',
            'options'   => array(
                PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
                PDO::MYSQL_ATTR_SSL_KEY => '/certs/client-key.pem',
                PDO::MYSQL_ATTR_SSL_CERT => '/certs/client-cert.pem',
                PDO::MYSQL_ATTR_SSL_CA => '/certs/ca.pem',
            ),
        ],

DB_CONNECTION=mysql
DB_HOST=xxx.xxx.xxx.xxx
DB_PORT=3306
DB_DATABASE=db_name
DB_USERNAME=db_user
DB_PASSWORD=xxxx

These setting give me the following error on the MySQL server.

2018-10-30T09:18:25.403712Z 71 [Note] Access denied for user 'user'@'xxx.xxx.xxx.xxx' (using password: YES)

Through mysql-client it works perfectly from the client server.

mysql -u user -p -h xxx.xxx.xxx.xxx --ssl-ca=/certs/ca.pem --ssl-cert=/certs/client-cert.pem --ssl-key=/certs/client-key.pem

Output of `\s' command performed on the client while connected with the above command.

mysql  Ver 15.1 Distrib 10.1.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:      16
Current database:   database
Current user:       [email protected]
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server:         MySQL
Server version:     5.7.24-0ubuntu0.18.10.1 (Ubuntu)
Protocol version:   10
Connection:     167.99.215.179 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306
Uptime:         23 min 29 sec

Threads: 2  Questions: 38  Slow queries: 0  Opens: 135  Flush tables: 1  Open tables: 128  Queries per second avg: 0.026
--------------

The application runs inside a docker container based on the php:7.2.11-fpm image. And is configured with the following php extensions.

RUN docker-php-ext-install bcmath zip pdo_mysql json tokenizer

MySQL version:

Server version: 5.7.24-0ubuntu0.18.10.1 (Ubuntu)

PHP version:

PHP 7.2.11 (cli) (built: Oct 16 2018 00:46:29) ( NTS )

PDO version:

PDO

PDO support => enabled
PDO drivers => sqlite, mysql

pdo_mysql

PDO Driver for MySQL => enabled
Client API version => mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387

OpenSSL version:

openssl

OpenSSL support => enabled
OpenSSL Library Version => OpenSSL 1.1.0f  25 May 2017
OpenSSL Header Version => OpenSSL 1.1.0f  25 May 2017
Openssl default config => /usr/lib/ssl/openssl.cnf

Directive => Local Value => Master Value
openssl.cafile => no value => no value
openssl.capath => no value => no value

Any thoughts on this? This is keeping me busy for hours...

did you tried set PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to true? — Egretos
– Egretos, Commented Oct 30, 2018 at 9:55
@Egretos This gives the following error. 3 [Note] Bad handshake the certificate is a self signed. — Odyssee
– Odyssee, Commented Oct 30, 2018 at 10:02
What's your PHP version, openSSL version and PDO & PDO MySQL versions? You can check each in phpinfo();. Also what is the version of the MySQL server. Please don't comment and update the question. Is your MySQL server configured to handle SSL connections? Also keep in mind that connecting with SSL to the localhost is pointless. — Mike Doe
– Mike Doe, Commented Oct 30, 2018 at 10:09
@emix I've updated the question. MySQL if configured for SSL, I've also added the output of \s executed on the client server while connected with mysql-client. — Odyssee
– Odyssee, Commented Oct 30, 2018 at 10:26

Odyssee · Accepted Answer · 2018-10-30 11:57:38Z

8

I finally solved the issue. The config above is actually good. I was working in the docker container directly. For some reason the config kept in cache.

Following commands did not clear the config cache:

php artisan config:clear
php artisan config:cache
php artisan cache:clear

I noticed this when I created a new user to connect with the database to test something. I rebuild the container with the new config and everything works perfectly now.

answered Oct 30, 2018 at 11:57

Odyssee

2,4612 gold badges24 silver badges38 bronze badges

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

1 Comment

Eden WebStudio Over a year ago

Having similar issues. Just want to ask the path certs/client-key.pem is that a path on the client server? How do i find out what it is for the config?

WebguruInfosystems · Accepted Answer · 2018-10-31 07:15:24Z

6

Please check your .env file. This file will be on website root folder.

DB_CONNECTION=mysql
DB_HOST=hostip
DB_PORT=3306
DB_DATABASE=database_name
DB_USERNAME=database_user_name
DB_PASSWORD=database_password

2.also update config -> database.php file as:

    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'sslmode' => env('DB_SSLMODE', 'prefer'),
        'options'   => array(
            PDO::MYSQL_ATTR_SSL_CA      => '/home/.../ca-cert.pem',
            PDO::MYSQL_ATTR_SSL_CERT    => '/home/.../cert.pem',
            PDO::MYSQL_ATTR_SSL_KEY     => '/home/.../key.pem'
        ),
        'strict' => true,
        'engine' => null,
    ],

answered Oct 31, 2018 at 7:15

WebguruInfosystems

874 bronze badges

1 Comment

Odyssee Over a year ago

Thanks for your answer. I figured it out what went wrong, you can read it in my answer. Basically, the cache:clear `config:cache',... did not clear my env cache. Rebuilding and deploying a new container did the trick.

Marc Ferreira · Accepted Answer · 2022-06-16 13:52:07Z

1

I was getting this error when running docker build. I fixed it by adding a check for pdo_mysql.

'options' => extension_loaded('pdo_mysql') ? array([
   PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
   PDO::MYSQL_ATTR_SSL_KEY => '/var/www/html/certs/client-key.pem',
   PDO::MYSQL_ATTR_SSL_CERT => '/var/www/html/certs/client-cert.pem',
   PDO::MYSQL_ATTR_SSL_CA => '/var/www/html/certs/ca.pem',
]) : [],

answered Jun 16, 2022 at 13:52

Marc Ferreira

111 bronze badge

Collectives™ on Stack Overflow

MySQL connection over SSL with Laravel

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related