TrustServerCertificate no longer working in v5.11.1

[esetnik]

TrustServerCertificate no longer working in v5.11.1

[absci]

This is not intended, looks like there's some inconsistency for the connection option. I'll look into a fix.

[esetnik]

I believe I have found the issue. It looks like Encrypt is using srv_encrypt_set_func and TrustServerCertificate is using bool_conn_str_func. bool_conn_str_func does not handle 'Yes' and 'No'.

https://github.com/microsoft/msphpsql/blob/servicing_v5.11.1/source/sqlsrv/conn.cpp#L456-L462
https://github.com/microsoft/msphpsql/blob/servicing_v5.11.1/source/sqlsrv/conn.cpp#L563C1-L571C7

[JohanNyholm]

I may be incorrect but it seems like #1460 changed bool_conn_str_func from defaulting to True to defaulting to False. May it be that in 5.11.0 setting bool-options to "yes" and "no" was interpreted as True and now in 5.11.1 they are not?

This should possibly be reported as a separate issue but MultiSubnetFailover also seems to be affected by this change.

As https://www.php.net/manual/en/function.sqlsrv-connect.php refers to connectionOptions using the following link http://msdn.microsoft.com/en-us/library/ff628167.aspx in which MultiSubnetFailover is documented as a "yes/no" option it is a bit confusing.

[absci]

The new release accepts true or false for connection options. You could change it like this.

        'Encrypt' => 'True',
        'TrustServerCertificate' => 'True'

[esetnik]

Was this change documented somewhere? Previously 'Yes' and 'No' were the only values that seemed to work.

[esetnik]

The new release accepts true or false for connection options. You could change it like this.

        'Encrypt' => 'True',
        'TrustServerCertificate' => 'True'

I tried changing the values per your recommendation and I now get the following error:

Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'Encrypt' [message] => [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'Encrypt' ) )

It looks as though only the following works:

        'Encrypt' => 'Yes',
        'TrustServerCertificate' => 'True'

Is this the expected behavior? It seems really odd for a point release to introduce this change without any release notes.

[absci]

I'm thinking of accepting yes/no/true/false for all options to avoid confusion.

[JohanNyholm]

I'm thinking of accepting yes/no/true/false for all options to avoid confusion.

Sounds good!
Any idea if 1/0 is also used?
It may be that in 5.11.0 '1' was interpreted as true while the string '0' was interpreted as false. I have not investigated this further though.

[esetnik]

I'm thinking of accepting yes/no/true/false for all options to avoid confusion.

I'm good with that as long as it also supports the capital case variants of 'Yes' and 'No' as well. It won't personally affect us, since we will modify our own code to use the true / false variants.