Applicable to:
- Plesk Onyx for Linux
- Plesk for Linux
Question
It is required to connect to SQLServer (MSSQL) databases via PHP script on a Linux system.
What extension should be installed and how it can be done?
Answer
Modules sqlsrv
and pdo_sqlsrv
should be enabled for PHP engine.
Warning: These extensions were not properly tested with Plesk and are not officially supported. Perform all operations at your own risk.
Note: The installation of pecl/sqlsrv requires PHP version >= 7.2.0. sqlsrv
extension for PHP 7.1 was removed from pecl repository. Precompiled binaries for older sqlsrv
which support PHP 7.1 can be found at https://github.com/microsoft/msphpsql/releases/tag/v5.6.1. Precompiled binaries for older sqlsrv
which support PHP 7.0 can be found at https://github.com/microsoft/msphpsql/releases/tag/v5.3.0
-
Connect to the server via SSH.
-
Install necessary packages:
# apt-get install apt-transport-https make gcc g++ unixodbc unixodbc-dev plesk-php7X-dev
Note: "7X" is to be replaced with the PHP version. For example, "74" for PHP 7.4
-
Install the
msodbcsql
package:Note: This step is unique for each OS. The following example is for Ubuntu 18.04. If Debian is used, replaced the URL accordingly (for ie: "/debian/10/" or "/ubuntu/20.04/")
# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
# apt-get update
# ACCEPT_EULA=Y apt-get install msodbcsql18 -
Add the
pdo_sqlsrv
andsqlsrv-5.8.1
modules:-
For PHP 8.0 provided by Plesk:
# /opt/plesk/php/8.0/bin/pecl install pdo_sqlsrv
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/8.0/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/8.0/bin/pecl install sqlsrv
# echo "extension=sqlsrv.so" > /opt/plesk/php/8.0/etc/php.d/sqlsrv.ini -
For PHP 7.3/7.4 provided by Plesk:
# /opt/plesk/php/7.X/bin/pecl install pdo_sqlsrv
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/7.X/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/7.X/bin/pecl install sqlsrv
# echo "extension=sqlsrv.so" > /opt/plesk/php/7.X/etc/php.d/sqlsrv.ini
-
-
Reread PHP handlers:
# plesk bin php_handler --reread
- Reread the components properties to make modules visible in GUI:
# plesk sbin packagemng -sdf
-
Make sure that the required modules are enabled:
# /opt/plesk/php/7.X/bin/php -m | grep sqlsrv
pdo_sqlsrv
sqlsrvNote: "7X" is to be replaced with the PHP version. For example, "74" for PHP 7.4
-
Restart the PHP-FPM master process to apply changes to FPM sites
-
Connect to the server via SSH.
-
Install necessary packages:
# apt-get install apt-transport-https make gcc g++ unixodbc unixodbc-dev plesk-php8X-dev
Note: "8X" is to be replaced with the PHP version. For example, "81" for PHP 8.1
-
Install the
msodbcsql
package:# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
# apt-get update
# ACCEPT_EULA=Y apt-get install msodbcsql18 -
Add the
pdo_sqlsrv
andsqlsrv-5.8.1
modules:-
For PHP 8.1 provided by Plesk:
# /opt/plesk/php/8.1/bin/pecl install pdo_sqlsrv
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/8.1/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/8.1/bin/pecl install sqlsrv
# echo "extension=sqlsrv.so" > /opt/plesk/php/8.1/etc/php.d/sqlsrv.ini -
For PHP 8.0 provided by Plesk:
# /opt/plesk/php/8.0/bin/pecl install pdo_sqlsrv
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/8.0/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/8.0/bin/pecl install sqlsrv
# echo "extension=sqlsrv.so" > /opt/plesk/php/8.0/etc/php.d/sqlsrv.ini
-
-
Reread PHP handlers:
# plesk bin php_handler --reread
- Reread the components properties to make modules visible in GUI:
# plesk sbin packagemng -sdf
-
Make sure that the required modules are enabled:
# /opt/plesk/php/8.X/bin/php -m | grep sqlsrv
pdo_sqlsrv
sqlsrvNote: "8X" is to be replaced with the PHP version. For example, "81" for PHP 8.1
-
Restart the PHP-FPM master process to apply changes to FPM sites
-
Connect to the server via SSH.
-
Install necessary packages:
# apt-get install apt-transport-https make gcc g++ unixodbc unixodbc-dev plesk-php8X-dev
Note: "8X" is to be replaced with the PHP version. For example, "81" for PHP 8.1
-
Install the
msodbcsql
package:# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list
# apt-get update
# ACCEPT_EULA=Y apt-get install msodbcsql18 -
Add the
pdo_sqlsrv
andsqlsrv-5.8.1
modules:-
For PHP 8.1 provided by Plesk:
# /opt/plesk/php/8.1/bin/pecl install pdo_sqlsrv
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/8.1/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/8.1/bin/pecl install sqlsrv
# echo "extension=sqlsrv.so" > /opt/plesk/php/8.1/etc/php.d/sqlsrv.ini -
For PHP 8.0 provided by Plesk:
# /opt/plesk/php/8.0/bin/pecl install pdo_sqlsrv
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/8.0/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/8.0/bin/pecl install sqlsrv
# echo "extension=sqlsrv.so" > /opt/plesk/php/8.0/etc/php.d/sqlsrv.ini
-
-
Reread PHP handlers:
# plesk bin php_handler --reread
- Reread the components properties to make modules visible in GUI:
# plesk sbin packagemng -sdf
-
Make sure that the required modules are enabled:
# /opt/plesk/php/8.X/bin/php -m | grep sqlsrv
pdo_sqlsrv
sqlsrvNote: "8X" is to be replaced with the PHP version. For example, "81" for PHP 8.1
-
Restart the PHP-FPM master process to apply changes to FPM sites
-
Connect to the server via SSH.
-
Install the
msodbcsql
package:# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
# yum remove unixODBC-utf16 unixODBC-utf16-devel
# ACCEPT_EULA=Y yum install msodbcsql18 -
Install necessary packages:
# yum install unixODBC-devel make gcc-c++ gcc autoconf automake plesk-phpXX-devel libtool-ltdl.x86_64 libtool-ltdl-devel.x86_64
Note: "XX" is to be replaced with the PHP version. For example, "74" for PHP 7.4 or "80" for PHP 8.0
-
Add the
pdo_sqlsrv
andsqlsrv-5.8.1
modules:-
For PHP 7.2 provided by Plesk:
# /opt/plesk/php/7.2/bin/pecl install pdo_sqlsrv-5.8.1
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/7.2/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/7.2/bin/pecl install sqlsrv-5.8.1
# echo "extension=sqlsrv.so" > /opt/plesk/php/7.2/etc/php.d/sqlsrv.ini -
For later PHP 7.X and PHP 8.X versions provided by Plesk:
# /opt/plesk/php/7.X/bin/pecl install pdo_sqlsrv
# echo "extension=pdo_sqlsrv.so" > /opt/plesk/php/X.X/etc/php.d/pdo_sqlsrv.ini
# /opt/plesk/php/X.X/bin/pecl install sqlsrv
# echo "extension=sqlsrv.so" > /opt/plesk/php/X.X/etc/php.d/sqlsrv.iniNote: "X.X" is to be replaced with the PHP version. For example, "7.4" for PHP 7.4 or "8.0" for PHP 8.0
-
-
Reread PHP handlers:
# plesk bin php_handler --reread
- Reread the components properties to make modules visible in GUI:
# plesk sbin packagemng -sdf
-
Make sure that the required modules are enabled:
# /opt/plesk/php/X.X/bin/php -m | grep sqlsrv
pdo_sqlsrv
sqlsrvNote: "X.X" is to be replaced with the PHP version. For example, "7.4" for PHP 7.4 or "8.0" for PHP 8.0
- Restart the PHP-FPM master process to apply changes to FPM sites
Additional information
Instructions are taken from the Microsoft documentation page and adapted to Plesk PHP:
Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS
It is also possible to use an older system version of PHP. For example, in order to connect to MSSQL on CentOS 7 with system PHP version 5.4, the following command needed to be executed:
# yum install epel-release
# yum install php-mssql
Comments
5 comments
Hello!
I try to install php-mssql on a older php version (5.4), but it not woks. The extensio msssql not apear in Settings for 5.4.45 FPM application
After run
# yum install php-mssql
it shows
Package php-mssql-5.4.16-9.el17.x86_64 already installed and latest version
I don't know the next step.
Can anyone help me to solve this?
Thanks
Marc
Hello again!
I just try also install sqlsrv extension for the Plesk on centos 7 for php 7.2. I follow all steps and I can see de modules are available but when I try to connecte to remote SQL Server it returns this error:
Can anyone help and explain me the next step? What I need to install now?
Thanks
Systems Galileu any luck? I am in the same spot...
I have checked I have the ODBC driver in latest version:
I checked and have ini files for php7.2 in /opt/plesk/php/7.2/etc/php.d: odbc.ini
from console (php cli) also says that I have ODBC Driver 18 for SQL Server]
unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
but when trying to connect it says
Why is that ? Am i missing something?
I got it work.
And this answer is confusing- in example it is sugessted to install Microsoft ODBC 18 (msodbcsql18) and sqlsrv-5.8.1 for PHP 7.2 - and that will not work together.
Double check versions of drivers to be sure that they can work together. As it turned out - it is very important.
Check here if you use good version of
ODBC Drivers
to work withPHP sqlsrv
.For sqlsrv-5.8.1 you need to use Microsoft ODBC 17
Further info how to install it is on MS docs site here
So in step 2 should be added:
For PHP 7.2 and pdo_sqlsrv-5.8.1 install
msodbcsql17 :
Please sign in to leave a comment.