Accessing Microsoft SQL Server from PHP

Hestia Control Panel
#1

I understand that this question might be a more “general” one and might seem to have little to do with Hestia. The reason posting it here, is by concern that Hestia’s multi-php setup might need special care/treatment when installing packages, to avoid breaking things.

The need
One of my Hestia servers (Ubuntu 18.04) needs to access an MSSQL Database from within PHP. Does anyone have any experience with that?

What I have found out so far…
I need to install some PHP extensions (SQLSRV, PDO_DBLIB) on one of the PHP versions (since I’m using MultiPHP with PHP-FPM). Specifically on the version that is going to be used for connecting to MSSQL.

In the PHP-FPM section of Microsoft docs (because of MultiPHP / PHP-FPM), I read that I need to install the PHP drivers for Microsoft SQL Server using pecl and then enable the two modules.

What problems I have faced…
Running pecl install sqlsrv has produced lots of errors like

Warning: Invalid argument supplied for foreach() in Command.php on line 249
PHP Warning:  Invalid argument supplied for foreach() in /usr/share/php/PEAR/Command.php on line 249
XML Extension not found

To solve this, I did apt install php-xml.

Now after running pecl install sqlsrv I get

downloading sqlsrv-5.8.1.tgz ...
Starting to download sqlsrv-5.8.1.tgz (186,879 bytes)
........................................done: 186,879 bytes
34 source files, building
running: phpize
sh: 1: phpize: not found
ERROR: `phpize' failed

I read on Ask Ubuntu, that I need to have php-dev which installs phpize. But I noticed that by doing apt install php-dev a whole lot of packages will get installed. Am I on the right track here?

Other considerations…
Server’s command line PHP = 7.4
PHP of the web site that will be connecting to MSSQL = 7.3

#2

Seems like fine never tried it before… You can always try it on a “test” server.

For the other considerations
v-change-user-php-cli 7.3 to change for php for the user logged as user
If you want to use it in an cronjob (Most likely) use /usr/bin/php7.3

#3

After lots of trial+error and documentation reading, I managed to get it working. I run a successful test with Adminer connecting to MSSQL Server Database :smiley:

So here is the complete guide to install PHP drivers for Microsoft SQL Server for php7.3-FPM on Ubuntu 20.04.

Install needed packages to run pecl

apt install php-xml php7.3-dev

Install needed packages to compile the drivers

apt install g++ make unixodbc-dev

Install the prerequisite ODBC driver 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
pecl config-set php_ini /etc/php/7.3/fpm/php.ini
Download source, compile and install the latest version of the driver 
pecl install sqlsrv
pecl install pdo_sqlsrv
Enable the driver and restart the service. 
printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.3/mods-available/sqlsrv.ini
printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.3/mods-available/pdo_sqlsrv.ini
phpenmod -v 7.3 sqlsrv pdo_sqlsrv
systemctl restart php7.3-fpm

Helpful resources

  1. Microsoft documentation for PHP-FPM
  2. The ODBC driver is listed as a prerequisite
  3. Askubuntu thread about phpize
  4. MSDN Forum Post about needing to install compilation packages
  5. GitHub suggestion to install unixodbc-dev
1 Like