How to install Microsoft’s SQL PHP PDO and so on drivers for Linux NGINX – Tested

Background

Microsoft has made massive strides in supporting PHP on Linux in the last few years. A common need in a PHP application might be to connect to a Microsoft SQL Server. This guide is designed to explain how to install Microsoft SQL PHP drivers and some of the pitfalls that might occur. It’s heavily based on the Microsoft documentation but all the noise stripped out. There really exists just one pitfall (spoiler install ODBC Dev), but the entire procedure is a bit cumbersome so we’ve documented it from head to toe.

TL;DR Updated 2024

These TL;DR instructions were tested on Laravel Forge on Ubuntu and overrides the old information. Note it was tested on 24.04 but below it says 22.04 because 24.04 didn’t work as of 27 July 2024.

mssql-cli is deprecated. Use sqlcmd when testing.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo apt-get install mssql-tools18 unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile

With luck, and no certificate or encryption errors, you can now use sqlcmd to connect to your database.

Prerequisites

  • Ubuntu 18.04 or any of it’s derivatives
    • Refer to the Microsoft documentation if you’re using another Linux distribution
  • PHP Version: 7.4
    • The only tricky park with regards to other distributions is the PECL compilation

Step 1

Install PHP & PHP-DEV and XML Libraries

Note: All three these items might already be installed on up to date PHP servers. If you’re using a different PHP version, substitute 7.4 with whichever version.

sudo su
add-apt-repository ppa:ondrej/php -y
apt-get update
apt-get install php7.4 php7.4-dev php7.4-xml -y --allow-unauthenticated

Step 2

Install ODBC Driver prerequisites

At this point the Microsoft documentation takes you to another site to install the ODBC drivers. This can be quite overwhelming for an already complex and convoluted installation process but hang in there.

Once you get to this destination website, your first challenge is to understand which actual version you are supposed to install, and which one is not working or was a temporary one that is now deprecated. It’s thoroughly confusing and as you’re only starting the installation, it can be really annoying. But hang in there, we’ve done the work so you can just sit back and relax and read on.

So next one has to install the ODBC driver for Ubuntu by following the instructions on the Linux installation article. We’ve put the link for you don’t have to follow it, we won’t take you to another page.

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

If you are going to follow links, skip to Ubuntu because the default link at the top of the page has lots of confusing information:

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu17

Now you probably need a big screen because the instructions scroll off the screen. Bigger screens are highly recommended for PHP Development.

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

Next be careful! You might choose the wrong Ubuntu version and then who knows what will happen.

#Ubuntu 18.04

curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

Next be careful again! There are three sets of instructions, and two are optional, but in my experience the ODBC drivers are key to getting things working. First you have to deal with Microsoft’s weird sudo su / exit experience:

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17

Next your optional packages, before, long further down this convoluted installation you might need ODBC dev libraries! Skip at your own peril!

# optional: for bcp and sqlcmd

sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Here is the non-optional steps marked at optional!

# optional: for unixODBC development headers

sudo apt-get install unixodbc-dev

You can now continue to the first page. That’s if you remember where you came from. It’s called Step 3

Step 3

Install the PHP drivers for Microsoft SQL Server

Warning! The procedure below only works for PHP 7.4! If you use another version kittens will die! The reasons for this kitten nightmare is because PECL will try to compile for the currently installed PHP version. If you have another version you’re trying to do this for, this tutorial is not the right one but I’d love your comments below if you gave help. I did it once and was so tired afterwards I decided to go and sleep.

The fun below starts at the very first command pecl install sqlsrv. This goes into some complex compilation process and god forbid it works the first time. The lovely favourite here is:

fatal error: sql.h: No such file or directory
#include <sql.h>

One thing you have to understand about this pecl command is it will interrogate your local PHP install and compile the library for that version. If you’re using multiple PHP versions you might have to play with update alternatives.

The next command takes ages and it’s output is completely overwhelming. It tells you if it’s successful but you have to carefully scrutinize the final output.

sudo pecl install sqlsrv

Look for

Build process completed successfully
...noise...
You should add "extension=sqlsrv.so" to php.ini

Then if you need PDO (do it anyway, you’ve come this far):

sudo pecl install pdo_sqlsrv

Final steps, please note that this is for PHP 7.4 and probably won’t work as the compiled libraries are for 7.4.

sudo su
printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.4/mods-available/sqlsrv.ini
printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.4/mods-available/pdo_sqlsrv.ini
exit
sudo phpenmod -v 7.4 sqlsrv pdo_sqlsrv

The last command is supposed to work for Apache by in my experience it also works for NGINX.

 

Share this article

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top