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

Question

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')

Bindu Chilukuri · Accepted Answer · 2022-02-20 19:07:26Z

1

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
] : []

],

answered Feb 20, 2022 at 19:07

Bindu Chilukuri

292 bronze badges

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

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

DragonBe · Accepted Answer · 2022-02-22 09:28:28Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related