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.
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:
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.