[PHP 8] [ODBC Driver SQL Server] self-signed certificate

During upgrade from PHP 8.0 to 8.1, the following error appeared:

SQLSTATE[08001]: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]

Two solutions here...

• Add Encrypt=No; to the connection string, e.g.:

$dsn = 'sqlsrv:Server=IP,PORT;Database=DB_NAME;Encrypt=No;';

• Or get your hands on the x509 certificate public key, add it to the trusted certificates store and append TrustServiceCertificate=yes; to the connection string instead.

Roots of the problem

During a PHP8 minor upgrade, the underlying pdo_sqlsrv extension was updated. Having several dry-runs, it gotten to the point that on PHP 8.0.9 the pdo_sqlsrv extension version 5.0.9 was used. In this version there was no issue connecting to MSSQL server. Upgrading to PHP 8.0.27 already relied on pdo_sqlsrv extension version 5.10.1 which was already causing the error.

The pdo_sqlsrv extension relies on the system ODBC driver, which in PHP 8.0.27 used the version 18 of the driver mentioned.

Hence, in the error message itself:

[Microsoft][ODBC Driver 18 for SQL Server]

Definitely there was a change in behavior in this ODBC driver version. Reading through the changelog, actually revealed the reason behind it - https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228

 

• BREAKING CHANGE - Default Encrypt to Yes/Mandatory.
• BREAKING CHANGE - Changed certificate validation behavior to validate when encryption is negotiated from either the client or the server side, not just the client side.

In other words, the encryption is now mandatory enforced by the driver itself. To disable it, we tell this in the connection string via Encrypt=No.

Drupal 9 context

In Drupal 9, there's no easy way to alter how the DSN string is constructed, hence:

    // web/modules/contrib/sqlsrv/src/Driver/Database/sqlsrv/Connection.php

    // Build the DSN.
    $options = [];
    $options['Server'] = $connection_options['host'] . (!empty($connection_options['port']) ? ',' . $connection_options['port'] : '');
    // We might not have a database in the
    // connection options, for example, during
    // database creation in Install.
    if (!empty($connection_options['database'])) {
      $options['Database'] = $connection_options['database'];
    }

    // Build the DSN.
    $dsn = 'sqlsrv:';
    foreach ($options as $key => $value) {
      $dsn .= (empty($key) ? '' : "{$key}=") . $value . ';';
    }

But, seems it's possible to hack it by appending the required option for the port value, in the web/sites/default/settings.php

$databases['default']['default'] = [
  'database' => '',
  'username' => '',
  'password' => '',
  'prefix' => '',
  'host' => '',
  'port' => '1337;Encrypt=No',
  'namespace' => 'Drupal\\sqlsrv\\Driver\\Database\\sqlsrv',
  'driver' => 'odbc',
];