OSticket remote Azure Mysql db connection with SSL error

helpdesksulekha

I have OSticket installed in a VM and trying to configure the database in Azure Mysql service. If i disable the Azure Mysql service with ssl option, the webserver able to connect to database and site works. But if i enable the azure mysql service with SSL security i get website connectivity errors. IS SSL with remote mysql supported by osticket?
If yes what configuration to be done in OSt-config file in ssl options section? I have downloaded the PEM file from Azure Mysql. How to mention this pem in the config file?

PHP Fatal error: Uncaught mysqli_sql_exception: Connections using insecure transport are prohibited while --require_secure_transport=ON. in C:\inetpub\wwwroot\osTicket\include\mysqli.php:69
Stack trace:
#0 C:\inetpub\wwwroot\osTicket\include\mysqli.php(69): mysqli->real_connect()
#1 C:\inetpub\wwwroot\osTicket\bootstrap.php(217): db_connect()
#2 C:\inetpub\wwwroot\osTicket\main.inc.php(28): Bootstrap::connect()
#3 C:\inetpub\wwwroot\osTicket\client.inc.php(21): require_once('...')
#4 C:\inetpub\wwwroot\osTicket\index.php(16): require('...')
#5 {main}
thrown in C:\inetpub\wwwroot\osTicket\include\mysqli.php on line 69

KevinTheJedi

helpdesksulekha

You need to modify your config file and update this section:

https://github.com/osTicket/osTicket/blob/develop/include/ost-sampleconfig.php#L57

Cheers.

helpdesksulekha

Yes. But i have a pem file downloaded from AzureSQL. How to get these three files(CA, Client & Key) from this pem.

define('DBSSLCA','/path/to/ca.crt');

define('DBSSLCERT','/path/to/client.crt');

define('DBSSLKEY','/path/to/client.key');

KevinTheJedi

helpdesksulekha

You can read online how to get those from Azure. If not there are ways to grab information from the pem file.

Cheers.

helpdesksulekha

I have checked the PEM file and found no keys associated with it. the below openssl command helped in checking that.

openssl storeutl -keys d:\mysql.pem > d:\private.key

KevinTheJedi

helpdesksulekha

Then please contact your hosting provider for further assistance.

Cheers.

helpdesksulekha

But why this osticket is demanding a certificate with a key? why not use the PEM file or CRT file alone for SSL access?

KevinTheJedi

helpdesksulekha

Because we use the mysqli::ssl_set() method which requires such.

References:

Try typing the full path to the CRT/PEM file in the DBSSLCA line and retest.

Cheers.

helpdesksulekha

Tried that as well The Key file is mandatory it seems. The error throws like this without a key file
thrown in C:\inetpub\wwwroot\osTicket\bootstrap.php on line 211
[13-Dec-2023 08:31:00 UTC] PHP Fatal error: Uncaught Error: Undefined constant "DBSSLKEY" in C:\inetpub\wwwroot\osTicket\bootstrap.php:211

KevinTheJedi

helpdesksulekha

Did you try putting the path in all three?

Cheers.

helpdesksulekha

Tried that as well. it's not taking that file as key file.

KevinTheJedi

helpdesksulekha

Go to the mysqli file I linked above, and change:

    if (isset($options['ssl']))
        $__db->ssl_set(
                $options['ssl']['key'],
                $options['ssl']['cert'],
                $options['ssl']['ca'],
                null, null);

... to:

    if (isset($options['ssl']))
        $__db->ssl_set(
                $options['ssl']['key'] ?: null,
                $options['ssl']['cert'] ?: null,
                $options['ssl']['ca'] ?: null,
                null, null);

.. and retest.

Cheers.

helpdesksulekha

[20-Dec-2023 08:01:59 UTC] PHP Fatal error: Uncaught mysqli_sql_exception: Connections using insecure transport are prohibited while --require_secure_transport=ON. in C:\inetpub\wwwroot\osTicket\include\mysqli.php:69
Stack trace:
#0 C:\inetpub\wwwroot\osTicket\include\mysqli.php(69): mysqli->real_connect()
#1 C:\inetpub\wwwroot\osTicket\bootstrap.php(217): db_connect()
#2 C:\inetpub\wwwroot\osTicket\main.inc.php(28): Bootstrap::connect()
#3 C:\inetpub\wwwroot\osTicket\client.inc.php(21): require_once('...')
#4 C:\inetpub\wwwroot\osTicket\index.php(16): require('...')
#5 {main}
thrown in C:\inetpub\wwwroot\osTicket\include\mysqli.php on line 69