[SQLSRV] connection class does not allow for passing TrustServerCertificate setting in database configurations.

[phanku]

PHP Version

8.0

CodeIgniter4 Version

4.3.0

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Windows 10 / Linux (Red Hat)

Which server did you use?

apache / Window 10 using ODBC 18.0

Database

MS SQL

What happened?

I would like to first apologize if any of this information is not 100% accurate. I am by no means a MS SQL server expert.

I am currently rewriting an application in CodeIgniter 4 that was originally built upon the CodeIgniter 3 framework.
The application connects to a MS SQL server to transfer data. The application and the MS SQL server are within a private network that only the application and the MS SQL server are able to use.

The MS SQL server is using a self signed for encryption.

Our development team had some issues connecting to the MS SQL server when the application was originally built but then eventually we realized that due to the situation we could, without much risk, disable the encryption configuration in the CI3 database configurations and that would make the connection work.

The development team encountered issues in CI4 while attempting to replicate this connection. After much research we finally determined that the connection is failing because the SQLSRV drivers, created by Microsoft, have now set the encrypt setting the driver to Yes/Mandatory. Microsoft also has changed the behavior of TrustServerCertificate to not be tied to the Encrypt setting. Source: ODBC Driver 18.0 for SQL Server Released

The encrypt setting in the SQLSRV connection class does work for us now, vs CI3, but the connection would continually fail until we managed to add the TrustServerCertificate setting into the SQLSRV connection class.

I believe the SQLSRV connection class should be updated to represent the changes within the drivers provided by Microsoft for those situations in which there is no need, or want, to validate the certificate for encryption.

Steps to Reproduce

1. Set up a MS SQL Server with a self-signed certificate.
2. Use CI4 to attempt to connect to the MS SQL server with and without the encrypt setting off or on.
3. The connection will fail and report that the ODBC, and I think this will eventually affect the drivers installed in Linux distros, refuses to connect because the certificate cannot be verified by a certificate authority.

Expected Output

The connection to work.

Anything else?

Our development team has determined, as far as we know, that the change the to SQLSRV connection class is easy to implement.

1. Add a protected field of $TrustServerCertifcate that defaults to false to the SQLSRV Connection class located at:
   /vendor/codeigniter4/framework/system/Database/SQLSRV/Connection.php
2. Add the following code to the $connection configuration array in the same file. Code below.

$connection = [
            'UID'                  => empty($this->username) ? '' : $this->username,
            'PWD'                  => empty($this->password) ? '' : $this->password,
            'Database'             => $this->database,
            'ConnectionPooling'    => $persistent ? 1 : 0,
            'CharacterSet'         => $charset,
            'Encrypt'              => $this->encrypt === true ? 1 : 0,
            'ReturnDatesAsStrings' => 1,
            'TrustServerCertificate' => $this->TrustServerCertificate === true ? 1 : 0,
];

[kenjis]

Thank you for reporting.
It seems the feature is missing now.

Ref https://learn.microsoft.com/en-us/sql/connect/php/connection-options?view=sql-server-2016