sqlsrv not working with Debian 12

[yoramdelangen]

Not sure where to put the "issue" because I dont know if its a bug or something else.

I have compiled a PHP 8.4.17 x86_64-gnu version with sqlsrv and pdo_sqlsrv. With a simple test script I was testing to make a connection to a SQL Server on a Debian 12 environment (Azure). Very plain an simple:

// test.php
<?php

$server   = "a-remote-sqlsrv-url,3342";
$database = "my_database";
$username = "sqlsrv_dbo";
$password = "a-very-strong-password";

$dsn = "sqlsrv:Server=$server;Database=$database;TrustServerCertificate=true;Encrypt=true";
echo 'Connecting to '. $server;
try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    echo "Connected to SQL Server successfully!";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

I have been testing this command with a 'statically compiled binary' and a PHP installed via apt:

# static binary call
./php-gnu test.php

# system binary call
php test.php

When running the static binary it will timeout after (a lot of seconds) Connecting to {server host},{port}.
When running the system PHP 8.4. (which is the same version as the static binary) it succeeds the connection and I see "Connected to SQL Server successfully" without any problems.

I noticed that the offical pdo_sqlsrv and sqlsrv driver didn't have support for Debian 12 yet. This has been recently been added. The pre-built binaries that can be used via the workflows are containing the previous version (5.12.0), so we build all dependencies from scratch and tested the above commands with that new version as well. Result still timeout.

We tested this in a Debian 12 Docker image, same issue. Tested it with Ubuntu 24.04, and it works perfectly fine, conclusion it has something to do with Debian.

Note: the drivers require you to install the msodbcsql17 or msodbcsql18.

Re-installed drivers, unixodbc, it dependencies, msodbcsql1X etc. but didn't make any difference. Also check if it was a certificate problem(ca-certificate), because of the timeout but no difference. Re-installed openssl 3 and considered to install openssl 1.1 but no difference either.

After that I started watching the LIB lookup trace with LD_DEBUG=libs,files the only difference between the system PHP and static PHP was the way it loads the msodbcsql driver.

#system php: 
$ LD_DEBUG=libs,files php test.php
file=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.6.so.1.1 [0];  dynamically loaded by /lib/x86_64-linux-gnu/libltdl.so.7 [0]
      3557:     file=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.6.so.1.1 [0];  generating link map
      3557:       dynamic: 0x000079450fb54360  base: 0x000079450f954000   size: 0x000000000021a840
      3557:         entry: 0x000079450f971500  phdr: 0x000079450f954040  phnum:                 11
      3557:
      3557:
      3557:     file=libdl.so.2 [0];  needed by /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.6.so.1.1

# static php:
$ LD_DEBUG=libs,files ./php-gnu test.php
file=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.6.so.1.1 [0];  dynamically loaded by ./php-gnu [0]
      3561:     file=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.6.so.1.1 [0];  generating link map
      3561:       dynamic: 0x000077a11389a360  base: 0x000077a11369a000   size: 0x000000000021a840
      3561:         entry: 0x000077a1136b7500  phdr: 0x000077a11369a040  phnum:                 11
      3561:
      3561:
      3561:     file=libodbcinst.so.2 [0];  needed by /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.6.so.1.1 [0]

I am stuck on this problem for a couple of days straight, and need some fresh ideas on how to approach/resolve it. Could it be something with one of the static dependency that are compiled into the binary, or a very hard Debian issue?

Any suggestions are welcome!

[crazywhalecc]

I currently have no sqlsrv test server, have you tested with strace?

My local build bin/spc-gnu-docker build sqlsrv,pdo_sqlsrv --build-cli with official debian msodbcsql install scripts works good on my debian vm. How did you build static php binary? More details, such as strace, ldd, and build files, would be helpful.

[yoramdelangen]

Thanks! Let's find out!

$ file ./php-gnu 
./php-gnu: ELF 64-bit LSB pie executable, x86-64, version 1 (GNU/Linux), dynamically linked, interpreter /lib64/ld-linux-x86-64.so.2, for GNU/Linux 2.6.32, BuildID[sha1]=5e56993eea167ecf3867f89c7a42bd38987f5d50, stripped

$ ldd ./php-gnu 
    linux-vdso.so.1 (0x00007ffe8c7a9000)
    libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fd2008c3000)
    libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007fd2008b2000)
    libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fd2008ad000)
    libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fd2007cd000)
    librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007fd2007c8000)
    libutil.so.1 => /lib/x86_64-linux-gnu/libutil.so.1 (0x00007fd2007c1000)
    libstdc++.so.6 => /lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fd2005a7000)
    libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fd200587000)
    libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fd2003a5000)
    /lib64/ld-linux-x86-64.so.2 (0x00007fd2037db000)

I have built it via the 'Build CI' workflow.

Commands it ran:

./bin/spc-gnu-docker download --with-php=8.4 --for-extensions=odbc,pdo_odbc,bcmath,calendar,ctype,curl,dba,dom,exif,fileinfo,filter,gd,libxml,mbregex,mbstring,msgpack,mysqli,mysqlnd,opcache,openssl,pcntl,pdo,pdo_mysql,pdo_pgsql,pdo_sqlite,pdo_sqlsrv,pgsql,phar,posix,readline,redis,session,simplexml,sockets,sodium,sqlite3,sqlsrv,tokenizer,xml,xmlreader,xmlwriter,zip,zlib --ignore-cache-sources=php-src

./bin/spc-gnu-docker build odbc,pdo_odbc,bcmath,calendar,ctype,curl,dba,dom,exif,fileinfo,filter,gd,libxml,mbregex,mbstring,msgpack,mysqli,mysqlnd,opcache,openssl,pcntl,pdo,pdo_mysql,pdo_pgsql,pdo_sqlite,pdo_sqlsrv,pgsql,phar,posix,readline,redis,session,simplexml,sockets,sodium,sqlite3,sqlsrv,tokenizer,xml,xmlreader,xmlwriter,zip,zlib --build-cli

Build artifacts are still available

Here the strace results:

# static PHP
strace -ff -s 200 \
  -e trace=openat,close,read,write,access,stat,newfstatat,mmap,mprotect,munmap,brk,rt_sigaction,rt_sigprocmask,fcntl,ioctl,connect,getsockopt,setsockopt,sendto,recvfrom,sendmsg,recvmsg,poll,ppoll,select,pselect6,nanosleep,clock_gettime,getrandom \
  ./php-gnu test.php

# System PHP
strace -ff -s 200 \
  -e trace=openat,close,read,write,access,stat,newfstatat,mmap,mprotect,munmap,brk,rt_sigaction,rt_sigprocmask,fcntl,ioctl,connect,getsockopt,setsockopt,sendto,recvfrom,sendmsg,recvmsg,poll,ppoll,select,pselect6,nanosleep,clock_gettime,getrandom \
  php test.php

static-php.strace.txt
system-php.strace.txt

I had to redact the credentials etc

It almost seems like to me it looping to get shared objects from the systen..

[yoramdelangen]

I think I found the culprit:

Left: strace of the static PHP binary.
Right: strace of the system PHP binary.

So the static PHP binary tries to load the libssl.so.3 from the same location as msodbcsql18 is located.. doesnt seems right to me XD

[henderkes]

If you compile with --with-suggested-libs, does that fix the issue? The static binary shouldn't be trying to load a shared ssl library.

[yoramdelangen]

It was resolved by enabling suggested libs. Therefore I have added support in the workflows to add the --with-suggested-libs flag for the build command. Which package it resolves I dont know. Becuase sqlsrv and pdo_sqlsrv do not have 'suggested libs'. Neither does unixodbc and pcntl.

I ran into a different issue when compiling my entire set of extension (namely: odbc,pdo,pdo_odbc,bcmath,calendar,ctype,curl,dba,dom,exif,fileinfo,filter,zlib,gd,xml,libxml,mbstring,mbregex,session,msgpack,mysqlnd,mysqli,opcache,openssl,pcntl,pdo_mysql,pgsql,pdo_pgsql,sqlite3,pdo_sqlite,sqlsrv,pdo_sqlsrv,phar,posix,readline,redis,simplexml,sockets,sodium,tokenizer,xmlreader,xmlwriter,zip). But I am investinging that at the moment, it has something to do with libxml and libxslt.

update:
It fails because of this error during compiling from libxslt.

error: Version 2.12.5 found. You need at least libxml2 2.15.1 for this version of libxslt

[henderkes]

remove your old downloaded libxslt and download it again

[henderkes]

actually, scratch that, you're best off just deleting the whole download folder and downloading everything again

[yoramdelangen]

I am building via the workflows and forgot about the build caches deleting those and retrrying, fingerscross! Thanks! @henderkes

[yoramdelangen]

Removing build caches resolved the issue! Nothing to see here anymore!

[henderkes]

Haha don't worry, I've been caught by the same more than once.