Skip to main content

How install pdo_sqlsrv to connect to MSSQL Servers

Steven Sublett
  • Updated

Introduction

Note: This is a task that a certified administrator should perform to avoid conflicts and issues. Manually attempting to install any package or related functionality will result in a conflict that needs to be addressed by an administrator before a successful installation is achieved.

When connecting to a Microsoft SQL server from a cPanel server, installing pdo_sqlsrv on cPanel servers is necessary. Several options exist to connect to SQL servers, but utilizing pdo_sqlsrv is the best option and has few requirements.

The most typical options to connect to SQL from a cPanel server are:

  • pdo_sqlsrv ( By default, as of 03-07-2023, requires PHP 8.0 or newer )
    • This is the best option because many website software supporting SQL will include support for this driver.
  • pdo_odbc
    • Many users succeed with this option, but it is challenging to configure.
  • pdo_dblib
    • Only available in up to PHP 5.3. Version PHP 5.3 has already reached EOL. EasyApache 4 also doesn't support this module. Please use pdo_sqlsrv or pdo_odbc as an alternative.
  • PHP mssql extension
    • Warning: REMOVED in PHP 7.0.0.
    • not recommended to use this extension because the PHP version required is EOL. Please use pdo_sqlsrv or pdo_odbc as an alternative. 

Procedure

To proceed, login as the root user via SSH or terminal. If you don't have root access, please consult your server administrator.

Note: The below steps will allow you to enable pdo_sqlsrv for PHP version 8.0.

1. First, install the Microsoft SQLSRV Driver. Microsoft has documented the steps to automatically install the driver with the YUM package manager.

Installing the Microsoft SQL Driver for Linux

After the driver installs successfully, you can verify this with the below command.

odbcinst -q -d

The output will look similar to this:

odbcinst -q -d
[PostgreSQL]
[MySQL]
[ODBC Driver 17 for SQL Server]

In the output, Driver 17 for SQL Server was installed successfully.

2. Ensure "popen" is not in the disabled functions list for php.ini.

For example, the php.ini file will contain a line called "disable_functions" by default. The list is usually empty but will include a similar entry to the one below if customized.

disable_functions = popen

Remove "popen" from the "disable_functions" list, as it will cause a failure similar to the below error.

Starting to download sqlsrv-5.9.0.tgz (189,701 bytes)
.........................................done: 189,701 bytes
34 source files, building
running: phpize

Warning: popen() has been disabled for security reasons in 
PEAR/Builder.php on line 525
ERROR: failed to run `phpize'

3. Install ODBC-devel package requirement

yum install unixODBC-devel

Just so you know, if you installed ODBC manually, you need to remove it before proceeding. Please consult with a certified administrator on removing a manually compiled install of ODBC. If ODBC is compiled manually, you will find details at the below paths:

/usr/local/bin/odbc_config
/usr/local/bin/odbcinst
/usr/local/include ( do NOT remove unless you are 100% certain it will be ok )

Note: If users need access to odbc commands inside of CloudLinux CageFS, you must add the RPMs to CageFS.

cagefsctl --addrpm unixODBC
cagefsctl --force-update

4. Next, you can install the PHP SQLSRV module.

For cPanel's EA-PHP

/usr/bin/ea-php80-pecl install sqlsrv

You can verify the module installed with the below command:

/usr/local/bin/ea-php80 -m |grep "^sqlsrv"

The output will look similar to:

/usr/local/bin/ea-php80 -m |grep "^sqlsrv"
sqlsrv

For CloudLinux's Alt-PHP

See How To Enable A Particular PHP Extension For A Cloudlinux ALT-PHP## Installation?

5. After installing the SQLSRV module, you can install the PDO component.

For cPanel's EA-PHP

/usr/bin/ea-php80-pecl install pdo_sqlsrv

You can verify the module installed with the below command:

/usr/local/bin/ea-php80 -m |grep "pdo_sqlsrv"

The output will look similar to:

/usr/local/bin/ea-php80 -m |grep "pdo_sqlsrv"
pdo_sqlsrv

* The PECL SQLSRV package recently updated to require PHP 8.0 by default; this will cause issues with the above steps if you try installing with a PHP version older than 8.0. For example, the below error will occur when PHP 8.0 is the required version to install SQLSRV.

/usr/bin/ea-php72-pecl install sqlsrv
WARNING: channel "pecl.php.net" has updated its protocols, 
use "pecl channel-update pecl.php.net" to update
pecl/sqlsrv requires PHP (version >= 8.0.0), installed version is 7.2.34
No valid packages found
install failed

To work around this issue, a few options are available.

  • Use PHP 8.0 or newer, or follow the below steps to install an older version of the SQLSRV module with PECL. 

- For PHP 7.2 requires a different version; use the below command.

/usr/bin/ea-php72-pecl install pdo_sqlsrv-5.8.1

- PHP 7.3 requires a different version; use the below command.

/usr/bin/ea-php73-pecl install pdo_sqlsrv-5.9.0

- PHP 7.4 also uses a different version; use the below command.

/usr/bin/ea-php74-pecl install pdo_sqlsrv-5.10.0

* If the install fails with the below error:

Warning: popen() has been disabled for security reasons in 
PEAR/Builder.php on line 525
ERROR: failed to run `phpize'

This will occur because the php.ini option "disable_functions" has been modified to disable "popen". Removing "popen" from the list of disabled functions will resolve this error.

For CloudLinux's Alt-PHP

See How To Enable A Particular PHP Extension For A Cloudlinux ALT-PHP## Installation?

You can now verify the PDO driver is loaded, and it will be displayed in the below command:

/usr/local/bin/ea-php80 -i |grep 'PDO drivers'
PDO drivers => mysql, sqlite, sqlsrv

Websites utilizing the PHP version you installed pdo_sqlsrv for can now connect to the SQL server with the PDO driver called SQLSRV.

Apache Jail Caveat:

If the Apache Jail option is enabled via WHM Tweak Settings, it will prevent users from accessing the ODBC configuration file. This file is required for users to utilize the ODBC sqlsrv functions.

  • Jailed Apache virtual hosts that use the mod_ruid2 module via the EXPERIMENTAL: Jail Apache Virtual Hosts using mod_ruid2 and cPanel® jailshell option in WHM’s Tweak Settings interface (WHM >> Home >> Server Configuration >> Tweak Settings).

To work around this caveat, add odbcinst.ini into the additional files for jailed/Virtfs users:

echo "/etc/odbcinst.ini" >> /var/cpanel/jailshell-additional-files

Alternatively, you could change the user's shell to "/bin/bash".

Related articles