Hello all

Spent 3 weeks now trying to get PHP to talk to MS SQL Server without success.
I know it used to years ago with earlier versions of IIS Express and PHP7. Had no issues back then.
Recently decided to pick up PHP again.

I started with PHP v 8.2 and the corresponding "82" drivers (ts and nts) but couldn't get them to load.
So went backwards to PHP 7.4 and associated "7"" drivers which at least appear to be loading.

Currently all versions of PHP work fine under IIS Express with or without MySQL data backend.
And IIS Express connects to MS SQL Server no problem with .Net webistes.
This makes me believe it's not an IIS problem.

There is no reference to SQL Server in the phpinfo() output even though the drivers are
included in the PHP.ini file.

Any advise as to what I am missing here would be appreciated.

I have listed below as much detail as I can for your information.

If you need any more just ask.

Thanks

Ray Pooley

Windows 10 version 21H2 (OD Build 19044, 2846)
IIS Express version (10.0.19041.1)
SQL Server Management Studio version 18.9.1
MySql version 8.01
PHP version 7.4.33 NTS x64 (PHP7.dll)

The statement :
$conn = new PDO("sqlsrv:server=$serverName ; Database = $database", $uid, $pwd);
throws an exception message : "could not find driver"

Note that if I use $conn = sqlsrv_connect($serverName, $connectionInfo);
I get error "Call to undefined function sqlsrv_connect()"

All files enabled in PHP Manager are present in the PHP/ext folder desinated in the PHP.ini file.

PHP.ini extensions
[PHP_CURL]
extension=php_curl.dll
[PHP_GD2]
extension=php_gd2.dll
[PHP_GETTEXT]
extension=php_gettext.dll
[PHP_MYSQLI]
extension=php_mysqli.dll
[PHP_MBSTRING]
extension=php_mbstring.dll
[PHP_OPENSSL]
extension=php_openssl.dll
[PHP_SOAP]
extension=php_soap.dll
[PHP_XMLRPC]
extension=php_xmlrpc.dll
[PHP_PDO_SQLITE]
extension=php_pdo_sqlite.dll
[PHP_ODBC]
extension=php_odbc.dll
[PHP_SQLITE3]
extension=php_sqlite3.dll
[PHP_PDO_SQLSRV_7_NTS_X64]
extension=php_pdo_sqlsrv_7_nts_x64.dll
[PHP_SQLSRV_7_NTS_X64]
extension=php_sqlsrv_7_nts_x64.dll
[PHP_PDO_ODBC]
extension=php_pdo_odbc.dll
[PHP_PDO]
extension=php_pdo.dll

    Sorry nobody has chimed in with a solution for you. I have essentially zero experience with PHP in a MS-centric environment. Best I can contribute is to confirm that you looked at https://www.php.net/manual/en/ref.pdo-sqlsrv.php for the installation/configuration issues? (I'm suspecting you did, but just in case 🙂 )

    I went to say something but found my SQL Server-in-PHP experience was way out of date.

    I thought I'd see what I could do short of installing SQL Server and IIS themselves. Copied php_sqlsrv_82_ts_x64.dll and php_pdo__sqlsrv_82_ts_x64.dll to the extension directory alongside all the others, and added the lines

    extension=php_sqlsrv_82_ts_x64.dll
    extension=php_pdo_sqlsrv_82_ts_x64.dll

    And did a phpinfo() from the command line. I got it listed, which I understand is more success than you've been having. (Incidentally, putting the full filename is legacy; PHP will add the php_*.dll as needed so the line could be extension=sqlsrv_82_ts_x64. In fact, renaming the file to php_sqlsrv.dll and referring to it as extension=sqlsrv is possible and would save having to re-edit .ini every time the version changes. But I digress.)

    Because the custom extension was there, I tried function_exists('sqlsrv_connect') and got back true. phpinfo also listed sqlsrv under PDO drivers.

    Like I said, I did all this from the command line. If it works from there then suspicion falls back on IIS. Let's assume that you don't get joy from the command line so problems must be somewhere before that. One possibility is that the .ini file you're editing isn't the one being used. Check up near the top of the phpinfo() output to see what it says for "Loaded Configuration File" and "Additional .ini files parsed" and whether those match the file you're working on.

    (Speaking of, I do remember a weird incident once where the file I was editing was owned by a different user from the one I was working as: I could save and edit and reload and everything, but it turned out that I was actually working on a different copy from the "real" one. I don't know why Windows would give two different files the same path for two different users, but that was apparently what was happening. I recall a desktop.ini file being involved.)

    Likewise, getting phpinfo() served up through IIS and comparing it (especially the lines about which .ini files are loaded) with what the command-line call does might yield further clues.

    NogDog
    Thanks for reaching out anyway.
    Yes I have done the rounds pretty much.
    Just thought I would reach out before abandoning it as a lost cause.
    As I noted earlier I used it years ago on a Windows 7 platform with IIS6 with no problem
    connecting to MS SQL Server V12.

        Weedpacket
        Thanks Weedpacket.
        I haven't actually explored the command line options.
        I will give it a go even though I don't see much practical use for them.
        But it might suggest the solution is to re-installing IIS which I don't particularly want to
        do because I have a lot of websites that work fine on there. It would be a lot of
        work.
        The php.ini file is definitely the right one according to the phpinfo() and I have
        tested it by adding and removing things to see if phpinfo() picks the changes
        up just to be certain. Which it does no problem there. Except for when I'm trying
        to install these sqlsvr extensions.
        I also checked the ext folder path that's correct too.
        But no matter what I do it doesn't show any refence to the sqlsrv extensions.
        If someone has a php.ini that that works with sql server that I could compare mine to that would
        a huge help.
        Might identify something that is missing from min.

            Well, here's the thing I just tried.

            I took php.ini-production in the distribution (to make sure none of the customisations mine has accrued over the years are involved, and should probably review that now I think about it), copied it to php.ini, and at the end of the list of extensions (line 965) added the line

            extension=pdo_sqlsrv_82_ts_x64

            Adding that line was the only change I made to what was otherwise identical to php.ini-production.

            I put php_pdo_sqlsrv_82_ts_x64.dll (which version of course matches my PHP version) in the extension directory (ext).

            Ran:

            C:\php> php -r "phpinfo();" > phpinfo.txt

            And the resulting file has the section

            PDO

PDO support => enabled
PDO drivers => sqlsrv

pdo_sqlsrv

pdo_sqlsrv support => enabled
ExtensionVer => 5.11.0-beta1+16628

Directive => Local Value => Master Value
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0
pdo_sqlsrv.report_additional_errors => 1 => 1

            (As the extension version suggests, I downloaded the most recent sqlsrv zip file for this.)

            Note that I have neither SQL Server nor SSMS installed, so I can't say whether the extension would work as installed or if more initialisation is needed, but the extension is at least being recognised and registered with that minimum amount of work.

            In my normal php.ini where I have a couple of other database extensions registered (PostgreSQL and SQLite), there was no trouble adding SQL Server as a third, so your including MySQL as well shouldn't be a problem.

            Weedpacket
            Thanks man.

            I will paste those directives into my ini file and see what happens.
            Btw I'm not getting notifications from you guys when you reply.
            I need to figure that out.

                RayPooley Btw my own php.ini.production file has practiaclly nothing in it,

                Oh? The official one is over 70kB, mostly documentation. Are you using a download from php.net or something from another source? Usually those files (that and the -development variant) are left as is, and working ini files are based off one of them as appropriate.

                  Weedpacket
                  I think it's time to abandon it to be honest.
                  One thing I don't get is why there are so many drivers and why each new version of PHP has to have
                  it's own unique driver. There should be one backward compatible driver fits all for each database.
                  It all seems a bit of a mess frankly. Ill thought out.
                  I have been using MS Sql Server for 20 years and functionally speaking it hasn't changed
                  that much in that time.

                    RayPooley I think what happened is that Microsoft declared that they would take over maintaining PHP's SQL Server driver. So it no longer ships with PHP itself and upgraded alongside it with each release.

                      Finally got there. Took a reinstall of IIS Express. Version 10.0.19041.1
                      Then a reinstall of PHP.
                      I chose version 8.2 and appropriate driver (nts) version.

                      Connection syntax :
                      $conn = "";
                      $serverName = "YourMachine\SqlServerInstance";
                      $database = "YourDBName";
                      $uid = "YourDBAccount";
                      $pwd = "YourDBAccountPassword";

                      try {
                      $conn = new PDO("sqlsrv:server=$serverName;Database=$database", $uid, $pwd);
                      //If the exception is thrown, this text will not be shown
                      //echo("If you see this, it worked");
                      }
                      //catch exception Provider=SQLOLEDB;
                      catch(Exception $e) {
                      echo ("Message: " .$e->getMessage() . " " . $e->getTraceAsString());
                      }
                      PHP Version

                      php-8.2.5-nts-Win32-vs16-x64

                      PHP Driver version

                      php_pdo_sqlsrv_82_nts_x64.dll

                      I copied the php.ini.development file to php.ini and added the sqlsvr driver and it worked.
                      The php.ini file is too large to include in this post.
                      Here are the important lines
                      extension_dir = "C:\php-8.2.5-nts-Win32-vs16-x64\ext\"
                      The folowing are the last entries in the PHP.ini file
                      [PHP_CURL]
                      extension=php_curl.dll
                      [PHP_GD2]
                      extension=php_gd2.dll
                      [PHP_GETTEXT]
                      extension=php_gettext.dll
                      [PHP_MYSQLI]
                      extension=php_mysqli.dll
                      [PHP_MBSTRING]
                      extension=php_mbstring.dll
                      [PHP_OPENSSL]
                      extension=php_openssl.dll
                      [PHP_SOAP]
                      extension=php_soap.dll
                      [PHP_XMLRPC]
                      extension=php_xmlrpc.dll
                      [PHP_ODBC]
                      extension=php_odbc.dll
                      [PHP_PDO_ODBC]
                      extension=php_pdo_odbc.dll
                      [PHP_PDO_SQLSRV_82_NTS_X64]
                      extension=php_pdo_sqlsrv_82_nts_x64.dll

                        Write a Reply...