SQL Server 2022, PHP 8.2, ODBC 17 & 18

goozak commented 1 year ago

Not sure if it's really a bug or just something I'm missing...

PHP version 8.2.7

PHP SQLSRV version
5.11

Microsoft ODBC Driver version
17.10.3 & 18.2.1

SQL Server version
2022 Express

Client operating system
Windows Server 2016

Problem description
Installed SQL Server 2022 Express (which includes ODBC Driver 17.4.1), ODBC Driver 18.2.1 and PHP Driver 5.11. Got an error connecting to DB (from ODBC 17), so I updated ODBC to 17.10.3 (because of this note in PHP Driver release notes: "... requires ODBC Driver 17.4.2 or above. Otherwise, a warning about failing to set an attribute will occur." [note is for version 5.10 of the PHP Driver])

When calling `sqlsrv_connect` without the `Driver` connection option, `sqlsrv_client_info` reports that ODBC 17 is used, while ODBC 18 is also installed.

Expected behavior and actual behavior
Documentation for Driver option of the PHP Driver says "When the Driver keyword is not specified, the Microsoft Drivers for PHP for SQL Server attempt to find supported Microsoft ODBC driver(s) in the system, starting with the latest version of ODBC and so on." Since the PHP Driver 5.11 is supporting both ODBC 17 & 18, I was expecting ODBC 18 to be used, but it's using 17 instead. Adding the Driver option with value ODBC Driver 18 for SQL Server works as expected, but I would prefer not to hard-code the version.

Repro code or steps to reproduce

<?php
$connectionInfo = array('ReturnDatesAsStrings' => true, 'Encrypt' => 'no');
// Including the Driver option does use ODBC 18
// $connectionInfo = array('Driver' => 'ODBC Driver 18 for SQL Server', 'ReturnDatesAsStrings' => true, 'Encrypt' => 'no');
$conn = sqlsrv_connect('.\SQLINSTANCE', $connectionInfo);
if ( $conn === false ) { die( print_r( sqlsrv_errors(), true)); }
var_export(sqlsrv_client_info($conn));

v-makouz commented 1 year ago

Looking at the code, it does seem like it'll try 17, then 18, then 13. I agree, it would make sense to go for the latest driver first.

goozak commented 1 year ago

That order was introduced when ODBC 11 was replaced with 18 in #1347 (back in Dec. 2021).

v-makouz commented 1 year ago

Upon further reflection, I believe it chooses 17 over 18, because some of the defaults on 18 tighten security and thus can be seen as a breaking change, so I suspect it maybe necessary to keep the current behavior to avoid messing up existing setups. I will discuss it further, though.

goozak commented 1 year ago

I installed ODBC 18 because this says it's the only version that works with SQL Server 2022. I was surprised to realize that SQL Server actually installed 17. If 2022 works with 17, I'll just update it and forget about 18 for now.

Lets-Rock-Again commented 1 year ago

After hours spent testing SQLSRV with PHP 8.2.9 - and downloading the Driver/DLL files/updating the INI file... still no luck on Windows Server 2019.

It seems some people had to downgrade to 8.1, despite the support for the 8.2 (and later) release that supposedly works and came out earlier this year.

After more time wasted, I think I'm done with 8.2.9 for now, and will try downgrading to the 8.1.25 version instead - are there other people out there who have downgraded from 8.2 just to get SQL working?

Thanks you all for the help!

pakogn commented 4 months ago

I faced this problem today and a problem I had was actually with odbc driver 17. Even I updated it but with no success. I ended using 18, but as some know it prefers 17 over 18. to select a driver while doing the connection I leave like this and it worked fine:

php 8.1, maybe this works for php 8.2

$connectionInfo = [
    'Database'=>'DB',
    'UID'=>'UID',
    'PWD'=>'PASSWORD',
    'CharacterSet' => 'UTF-8',
    'Driver' => '{ODBC Driver 18 for SQL Server}',
    'Encrypt' => 'yes',
    'TrustServerCertificate' => 'yes'
];

microsoft / msphpsql