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, installing pdo_sqlsrv is necessary for PHP to communicate with this third-party SQL service. 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+.

Prerequisites

  • First, install the Microsoft SQL Driver using the documentation provided by Microsoft.

Installing the Microsoft SQL Driver for Linux | Microsoft Docs

Note: For AlmaLinux, Rocky Linux, and CloudLinux; follow the "RHEL and Oracle Linux" steps. Ubuntu users should follow the Ubuntu instructions.

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

odbcinst -q -d

The output will look similar to this:

[root@server]# odbcinst -q -d
[PostgreSQL]
[MySQL]
[ODBC Driver 17 for SQL Server]

A successful installation will list the "OOBC Driver XX for SQL Server" entry.

  • Ensure the local server has required development packages.

To install PECL modules, you must compile them. This will require the base development tools be installed.

For RPM-based systems:

yum install gcc-g++ libtool-ltdl-devel

For Ubuntu:

apt install g++ libltdl-dev
  • Ensure the unixODBC-devel package is installed.

For RPM-based systems:

yum install unixODBC-devel

For Ubuntu:

apt install unixodbc-dev

Please note, 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
  • Confirm PHP.ini is configured correctly for PECL installations.

Ensure "popen" and "pclose" is not in the disabled functions list for php.ini. The php.ini file will contain a line called "disable_functions"--this, by default, is empty. Ensure it does not contain either "popen" or "pclose" if it has been customized:

disable_functions = popen,pclose

Failure to remove "popen"  and "pclose" from the "disable_functions" list 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'

Installation

PECL modules can be installed via WHM, or via the command-line.

How to install PHP extensions using PECL using WHM

You can use this guide to install "sqlsrv" and "pdo_sqlsrv," or, to install via the command-line, run the following command:

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

Note: This step will install pdo_sqlsrv for PHP version 8.0. If you'd like to install the pdo_sqlsrv extension on a newer version of PHP, substitute that version in this command. pdo_sqlsrv cannot be installed on older versions of PHP.

For example, this would install to PHP 8.1 instead:

/usr/bin/ea-php81-pecl install sqlsrv-5.12.0

You can verify the module installed with the below command:

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

The output will look similar to:

[root@server]# /usr/local/bin/ea-php80 -m |grep "^sqlsrv"
sqlsrv

After confirming the "sqlsrv" module installed successfully, you can then install the "pdo_sqlsrv" module:

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

Note: This step will install pdo_sqlsrv for PHP version 8.0. If you'd like to install the pdo_sqlsrv extension on a newer version of PHP, substitute that version in this command. pdo_sqlsrv cannot be installed on older versions of PHP.

For example, this would install to PHP 8.1 instead:

/usr/bin/ea-php81-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:

[root@server]# /usr/local/bin/ea-php80 -m |grep "^pdo_sqlsrv"
pdo_sqlsrv

You can now verify the PDO driver is loaded:

[root@server]# /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.

 

Resources

Legacy PHP Versions

The current version of the SQLSRV PECL modules only support current PHP versions.

You can view a list of current PHP versions and their end-of-life dates in this article.

If you require this extension for an older, unsupported version of PHP, you can manually download older versions:

  • For PHP 7.2:
/usr/bin/ea-php72-pecl install pdo_sqlsrv-5.8.1
  • For PHP 7.3:
/usr/bin/ea-php73-pecl install pdo_sqlsrv-5.9.0
  • For PHP 7.4:
/usr/bin/ea-php74-pecl install pdo_sqlsrv-5.10.0

CloudLinux Alt-PHP

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

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.

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