What a mission today has been. I think I’ll ultimately roll back to using Debian Jessie as Stretch isn’t a supported system, yet.

To get the MS SQL ODBC driver working even in Jessie appears to be a challenge. In Stretch I almost surrendered. It is working, but I do think it’s a bit of a hack as I’ve had to install an older libssl1.0.0 and enable the locale en_US.UTF-8.

PHP development voted out the inclusion of MS SQL to the project so now you must compile and install it yourself. There are some very good instructions out there to help you – even from Microsoft.

https://docs.microsoft.com/en-gb/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

Following this got it installed for me at least. I had to install some extra packages like pear so I’d be able to use pecl. I also needed to install php-dev or there’s an error about phpize not found.

You also need unixodbc-dev – it says optional, but the pecl bits will fail without it.

$ sudo install php-dev pear
$ sudo pecl install sqlsrv
$ sudo pecl install pdo_sqlsrv

Which drags in the necessary compilers and compiles and installs the MS SQL drivers for PHP.

After grinding for a little while, td he pecl installs should result in success like this:

Build process completed successfully
Installing '/usr/lib/php/20151012/sqlsrv.so'
install ok: channel://pecl.php.net/sqlsrv-4.3.0
configuration option "php_ini" is not set to php.ini location
You should add "extension=sqlsrv.so" to php.ini
...
Build process completed successfully
Installing '/usr/lib/php/20151012/pdo_sqlsrv.so'
install ok: channel://pecl.php.net/pdo_sqlsrv-4.3.0
configuration option "php_ini" is not set to php.ini location
You should add "extension=pdo_sqlsrv.so" to php.ini

Then I created two files under /etc/php/7.0/mods-available called sqlsrvr.ini and pdo_sqlsrvr.ini and added in the matching extension:

sqlsrvr.ini

extension=sqlsvr.so

pdo_sqlsrvr.ini

extension=pdo_sqlsvr.so

Then create symbolic links in the /etc/php/7.0/fpm/conf.d that I called 20-sqlsvr.ini and 20-pdo_sqlsvr.ini and restart php-fpm.

$ sudo ln -s /etc/php/7.0/fpm/conf.d/20-sqlsvr.ini /etc/php/7.0/mods-available/sqlsvr.ini
$ sudo ln -s /etc/php/7.0/fpm/conf.d/20-pdo_sqlsvr.ini /etc/php/7.0/mods-available/pdo_sqlsvr.ini
$ systemctl restart php7.0-fpm.service

Then we can start using it in PHP.

The Problems with Stretch

I was trying to use it in Laravel on my Stretch box, but it kept failing. I hit on a few posts that suggested the solutions. So I hacked it a bit and it works!

SQLSTATE[01000]: [unixODBC][Driver Manager]Can’t open lib ‘/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.1’ : file not found

This led me into having to install libssl1.0.0 and then I reran the pecl installs. Probably not elegant, but it’s development only for me so I’ll do it for now, but production I’ll probably stick with Jessie.

https://github.com/Microsoft/msphpsql/issues/484

Turns out the following isn’t just an issue in Stretch. I had to apply this locale fix in Jessie too.

local.ERROR: pdo_sqlsrv_db_handle_factory: Unknown exception caught

Which I then discovered needed a locale adding.

$ sudo vi /etc/locale.gen

Uncomment the line # en_US.UTF-8 UTF-8 and save it. Then update the locales.

$ sudo locale-gen

https://github.com/Microsoft/msphpsql/issues/391

 

Advertisements