connect to azure database with laravel from azure virtual machine throws error as SQLSTATE[HY000] [2-CodePudding

I am able to connect via terminal as

mysql -h **.**.*.** -u ******@******* –p

able to connect via php code

mysqli_real_connect($conn, $host, $username, $password, $db_name, 3306, NULL, MYSQLI_CLIENT_SSL);

but when connecting via laravel connection getting refused

below are the configurations I did in Laravel

in .env file

MYSQL_SSL=true

new folder created in application folder as ssl and downloaded DigiCertGlobalRootCA.pem file

in config/database.php

'mysql' => [
    ...
    'sslmode' => env('DB_SSLMODE', 'prefer'),
    'options' => (env('MYSQL_SSL') && extension_loaded('pdo_mysql')) ? [
        PDO::MYSQL_ATTR_SSL_KEY    => '/ssl/DigiCertGlobalRootCA.crt.pem',
    ] : []
],

Followed link : enter link description here

Error deatils

php artisan migrate

Illuminate\Database\QueryException

SQLSTATE[HY000] [2002] (SQL: select * from information_schema.tables where table_schema = sample_db and table_name = migrations and table_type = 'BASE TABLE')

CodePudding user response：

PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false

adding this to options of MySQL array helped resolve the issue so the code in config/database.php looks like

'mysql' => [
...
'sslmode' => env('DB_SSLMODE', 'prefer'),
'options' => (env('MYSQL_SSL') && extension_loaded('pdo_mysql')) ? [
    PDO::MYSQL_ATTR_SSL_KEY    => '/ssl/DigiCertGlobalRootCA.crt.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
] : []

CodePudding user response：

I also had an issue with the SSL certificate provided by Microsoft to connect with Azure Databases for MySQL. What I did: uploading my own certificate to the database configuration and connect securely with PHP.

I even created a small helper function to convert the "ConnectionString" provided by Azure into a PDO object (using MS certificate).

function azureConnectionStringToPdo(string $connectionString, bool $ssl = false): PDO
{
    $connArray = explode(';', $connectionString);
    $connItems = [];
    foreach ($connArray as $pair) {
        list ($key, $value) = explode('=', $pair);
        $connItems[$key] = $value;
    }
    list ($host, $port) = explode(':', $connItems['Data Source']);
    $dsn = sprintf(
        'mysql:host=%s;port=%d;dbname=%s',
        $host, $port, $connItems['Database']
    );
    $options = [];
    if ($ssl) {
        $options = [
            PDO::MYSQL_ATTR_SSL_CA => __DIR__ . '/DigiCertGlobalRootG2.crt.pem',
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
        ];
    }
    return new PDO($dsn, $connItems['User Id'], $connItems['Password'], $options);
}

As you can see, I still set the verification to FALSE when using the Microsoft certificate. Once you use your own certificate, you can set this to TRUE.