Install Microsoft ODBC drivers on Ubuntu 20.04 LTS for PHP

This article explains how to install the official Microsoft ODBC drivers for SQL Server on Linux on Ubuntu 20.04 LTS for version 7.4 of PHP-FPM.

However, you can follow the same steps to install them on any available Ubuntu 20.04 LTS image, such as the current HestiaCP preinstalled image.

You can also use this article to install them on another version of PHP by changing the commands to the corresponding version, and you can skip the PHP-FPM installation step if you already have all the required packages installed.

Installing PHP-FPM

The first step is to add the PHP repository:

# add-apt-repository ppa:ondrej/php -y

And update the repositories:

# apt-get update

Then install the PHP packages:

# apt-get install php7.4 php7.4-dev php7.4-fpm php7.4-xml -y --allow-unauthenticated

Installing drivers

First, you will need to install the following package for odbc on Linux:

# apt install unixodbc-dev

Retrieve and add the key for the Microsoft repository:

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

Then add the Microsoft repository for Ubuntu 20.04 LTS:

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

Update the repositories again to include the newly added ones:

# apt-get update

Then accept the terms and install the driver package:

# ACCEPT_EULA=Y apt-get install -y msodbcsql17

Do the same for the tools package: accept the terms and install the package:

# ACCEPT_EULA=Y apt-get install -y mssql-tools

Then add the path for the binary:

# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc && source ~/.bashrc

Configure the file for PHP:

# pecl config-set php_ini /etc/php/7.4/fpm/php.ini

And install both drivers:

# pecl install sqlsrv && pecl install pdo_sqlsrv

Add the pdo_sqlsrv extension:

# printf "; priority=30\nextension=pdo_sqlsrv.so\n" > /etc/php/7.4/mods-available/pdo_sqlsrv.ini

And the sqlsrv extension as well:

# printf "; priority=20\nextension=sqlsrv.so\n" > /etc/php/7.4/mods-available/sqlsrv.ini

Finally, restart the corresponding PHP-FPM service:

# systemctl restart php7.4-fpm

Checks

You can run the following command to verify that the extensions are working:

# phpenmod -v 7.4 sqlsrv pdo_sqlsrv && ls /etc/php/7.4/fpm/conf.d/*sqlsrv.ini

Then you can also create a file in the web directory to run a test (example HestiaCP path):

# vi /home/admin/web/ejemplo.com/public_html/testsql.php

Use the following file contents, changing the values for the connection to SQL Server (otherwise it should time out):

<?php
 try {
     $serverName = "IPonombredeservidorSQL";
     $databaseName = "Nombredebasededatos";
     $uid = "Nombredeusuario";
     $pwd = "Contraseñadeusuario";

     $conn = new PDO("sqlsrv:server = $serverName; Database = $databaseName;", $uid, $pwd);

     // Select Query 
     $tsql = "SELECT @@Version AS SQL_VERSION"; 
     // Executes the query 
     $stmt = $conn->query($tsql);
 } catch (PDOException $exception1) { 
     echo "<h1>Caught PDO exception:</h1>"; 
     echo $exception1->getMessage() . PHP_EOL; 
     echo "<h1>PHP Info for troubleshooting</h1>"; 
     phpinfo(); 
} 

?> 

<h1> Success Results : </h1> 

<?php 
try {
     while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       echo $row['SQL_VERSION'] . PHP_EOL; 
    } 
} catch (PDOException $exception2) { 
    // Display errors 
    echo "<h1>Caught PDO exception:</h1>"; 
    echo $exception2->getMessage() . PHP_EOL; 
} 

unset($stmt); 
unset($conn);
?>

Official sources in Spanish:

Installing Microsoft ODBC drivers for SQL Server on Linux

Installation tutorial for SQL Server drivers on Ubuntu with PHP-FPM

Have you tried it? Leave us your comments!