connect PHP to SQL server on Debian

PHP doesn’t have a native driver to connect directly to SQL server (MSSQL) database. However, there are multiple ways to enable PHP to connect to MSQL. for example, you can achieve that by Installing ODBC on Linux or Microsoft native driver MSSQL for Linux. in short, This article explains both ways to connect PHP to SQL server on debian.

Open DataBase Connectivity is an interface to communicate with relational SQL-databases. It is built on top of Microsoft ODBC interface and therefore requires that you have an ODBC driver to the database that you want to connect to.

MySQL and SQL server provide high performance and scaling capabilities, however, SQL server ( MSSQL )has the advantage. For instance, according to this study, SQL Server consistently outperforms MySQL when it involves transactions on high volumes of data.

In addition, MySQL is considered to be Oracle’s “entry-level” database. In contrast, SQL server (MSSQL) is Microsoft’s flagship database. Usually, Oracle will Advice their customers to buy their flagship database Oracle Database.

This method will allow PHP to connect using the Linux ODBC interface to intermediate between PHP and SQL server. Next.

First and foremost, synchronize your local packages index files with their sources.

Then, Install ODBC interface packages.

Next, Install your programming language ODBC modules.

apt update
apt install -y unixodbc unixodbc-dev 
# Check which version of PHP
apt install -y php-odbc php7.4-odbc

This method will use Microsoft Native SQL server drivers to allow PHP connection to SQL server (MSSQL) instant.

In recent years Microsoft has a new attitude toward open-source software.in other words, it exported many projects and software to open-source communities.

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list

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

# 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

# optional: for unixODBC development headers
sudo apt-get install unixodbc-dev

# optional: kerberos library for debian-slim distributions
sudo apt-get install libgssapi-krb5-2

Needless to say, You need PHP packages to connect to your SQL server. therefore, you can install PHP from your OS repository. Read this article to get the latest PHP update from sury’s repository.

Install the ODBC driver for Debian by following the instructions on the Linux installation article.

sudo su
sed -i 's/# en_US.UTF-8 UTF-8/en_US.UTF-8 UTF-8/g' /etc/locale.gen
locale-gen

You may need to add /usr/sbin to your $PATH, as the locale-gen executable is located there.

Step 2. Install Microsoft SQL Server drivers

The following commands customized for PHP 7.4 on debian that has multiple PHP versions. Thus you need to update it with your version

sudo pecl install sqlsrv
sudo pecl install pdo_sqlsrv
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

If there is only one PHP version in the system, then the last step can be simplified to phpenmod sqlsrv pdo_sqlsrv.

Step 3. Restart PHP-FPM if you have it.

As we are adding modules for php. therefore, you will need to restart FastCGI Process Manager service.

systemctl restart php-fpm

Step 4. Install Apache and configure driver loading

sudo su
apt-get install libapache2-mod-php7.4 apache2
a2dismod mpm_event
a2enmod mpm_prefork
a2enmod php7.4

Step 5. Restart Apache and test the sample script

sudo service apache2 restart

To test your installation, see Testing your installation at the end of this document.