Using SQL Server from your PHP apps (on Ubuntu 16.04/18.04)

When I had to use SQL Server for a project I was undertaking, I struggled for hours to setup the connection between PHP (7.2) and SQL Server.

My choice of PHP framework is Laravel but that is totally irrelevant for the steps you need to take to set it all up.

There are basically three steps, you first need to install the Microsoft ODBC Driver for SQL Server then you need to install the Microsoft Drivers for PHP for SQL Server and finally you need to load the Microsoft PHP Drivers for SQL Server. That’s it!

1. Install the Microsoft ODBC Driver for SQL Server

sudo su  
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -  
# Download appropriate package for the OS version. Choose only ONE of the following, corresponding to your OS version  
# Ubuntu 16.04, change to 18.04 for bionic
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list  
exit 
sudo apt-get update 
sudo ACCEPT_EULA=Y apt-get install msodbcsql17 
# For unixODBC development headers 
sudo apt-get install unixodbc-dev mssql-tools

You can find the steps for other platforms from Microsoft from this url (https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017)

2. Install the Microsoft Drivers for PHP for SQL Server

sudo apt-get -y install php-pear php7.2-dev
sudo pecl install sqlsrv 
sudo pecl install pdo_sqlsrv

3. Load

Make sure that the two driver files (sqlsrv.so and pdo_sqlsrv.so) have been added to /usr/lib/php/20170718 (your path might be different, just check your php dynamic libraries path)

sudo su 
echo "extension=sqlsrv.so" > /etc/php/7.2/mods-available/sqlsrv.ini
echo "extension=pdo_sqlsrv.so" > /etc/php/7.2/mods-available/pdo_sqlsrv.ini
exit

From here, you can then enable the extension for either cli, fpm or both.

# Enable sqlsrv extensions for cli
sudo ln -s /etc/php/7.2/mods-available/sqlsrv.ini /etc/php/7.2/cli/conf.d/20-sqlsrv.ini
sudo ln -s /etc/php/7.2/mods-available/pdo_sqlsrv.ini /etc/php/7.2/cli/conf.d/20-pdo_sqlsrv.ini
# Enable sqlsrv extensions for fpm
sudo ln -s /etc/php/7.2/mods-available/sqlsrv.ini /etc/php/7.2/fpm/conf.d/20-sqlsrv.ini
sudo ln -s /etc/php/7.2/mods-available/pdo_sqlsrv.ini /etc/php/7.2/fpm/conf.d/20-pdo_sqlsrv.ini

Then restart your php-fpm and nginx/apache services and you should be able to connect to SQL Server from PHP!

Test!

You can test whether you can connect by using the following PHP code.

$serverName = "yourServername"; 
$connectionOptions = [
    "Database" => "yourDatabase",
    "Uid" => "yourUsername",
    "PWD" => "yourPassword"
]; 
//Establishes the connection 
$conn = sqlsrv_connect($serverName, $connectionOptions);
//Executes a query 
$getResults= sqlsrv_query($conn, "SELECT @@Version as SQL_VERSION");

Edit — Jan 22, 2019

I have created ansible script for the above steps.

I have also created an ansible-galaxy role for it called odbc_driver_for_mssql_on_ubuntu

You can install and use the role as follows:

ansible-galaxy install amestsantim.odbc_driver_for_mssql_on_ubuntu