How to connect to mssql using pdo through PHP and Linux?

How to connect to mssql using pdo through PHP and Linux?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Connecting to MSSQL via PDO on Linux: Drivers and Configuration

Migrating from legacy extensions like mssql_* to PDO is a common requirement for modernizing PHP applications, particularly when moving from Windows Server environments to Linux. The transition isn't merely syntactic; it requires understanding the underlying driver architecture. The code snippet you are looking at uses the DSN string mssql:, which immediately signals a specific dependency that does not come with standard PHP distributions.

The legacy mssql extension relies on Microsoft's official client libraries, often requiring Windows-specific DLLs or complex setup for 64-bit Linux. In contrast, PDO requires you to explicitly choose and install a driver. For Linux systems, the de facto standard is FreeTDS. It provides a lightweight C library implementation that mimics the SQL Server protocol without needing native Microsoft binaries. This is why your php.ini indicates "Library version FreeTDS."

To make this work on Linux, you must first install the appropriate development packages and PHP extensions. On Debian-based systems like Ubuntu or Kali, you would typically execute:

sudo apt-get update
sudo apt-get install php-mssql libfreetds-dev freetds-bin

On RHEL or CentOS systems, the equivalent command is:

sudo yum install php-mssql freetds-devel

Once installed, you must verify that the PHP extension is loaded. While your php.ini shows "MSSQL Support enabled," PDO does not automatically utilize this if a dedicated DSN driver isn't registered. You often need to ensure the pdo_sqlsrv or pdo_dblib extensions are present. For FreeTDS, you usually interact with the pdo_dblib driver.

Constructing the Connection String

The core confusion in your original code lies in the DSN format. The string mssql:driver=???? is incomplete and will fail. When using the FreeTDS-based driver (pdo_dblib) on Linux, the DSN syntax changes significantly. You generally omit the mssql: prefix entirely unless you are specifically targeting the Microsoft native driver (which is rarely practical on Linux).

Instead of guessing drivers, rely on the dblib connection string format which allows you to pass FreeTDS configuration directly in the DSN. Your code should look like this:

$dsn = "dblib:host={$serverName};dbname={$databaseName}";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_PERSISTENT => false
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    // Handle connection errors here
    echo "Connection failed: " . $e->getMessage();
}

Handling Authentication and Character Sets

A critical pitfall when using FreeTDS on Linux is authentication. The legacy mssql_connect function often defaults to Windows-style NTLM or mixed modes if the underlying library isn't configured correctly. With PDO and FreeTDS, you must ensure your client configuration (freetds.conf) matches your server's security settings.

If your database uses SQL Server Authentication (username/password), you must explicitly set this in your FreeTDS config to avoid falling back to Integrated Windows Authentication, which will fail on a non-domain-joined Linux machine. In freetds.conf, the [ODBC] or default section should look like this:

[global]
    tds version = 7.3
    client charset = UTF8
    encrypt = yes
    socket timeout = 0

Setting client charset = UTF8 is vital because legacy FreeTDS defaults often use ISO-8859-1 or GB2312, causing encoding errors immediately upon fetching text data. Without this, your application might display garbled characters or throw encoding exceptions when reading NVARCHAR columns.

Additionally, the encrypt directive is frequently required in modern environments. If your SQL Server instance enforces Always On encryption or TLS 1.2, omitting this in the DSN or config will result in a connection refused error. You can also pass these parameters directly in the DSN if you prefer not to edit the global config file:

$dsn = "dblib:host={$serverName};dbname={$databaseName};charset=utf8;encrypt=yes";

Troubleshooting Connection Failures

If your new PDO call still throws an error, check the specific exception message. If you see a timeout, verify that the mssql.allow_persistent and mssql.timeout settings in php.ini are not inadvertently blocking long-running queries, although PDO::ATTR_PERSISTENT => false usually bypasses persistent connection pools entirely for this specific issue.

Another common mistake is assuming the DSN accepts all parameters. The dblib driver has a limited set of allowed keywords in the DSN string. If you need advanced FreeTDS options (like specifying a specific port or TDS version), they must be defined in the freetds.conf file, not appended to the DSN string.

By explicitly installing the FreeTDS libraries and configuring the connection string with the correct driver prefix (dblib) and charset settings, you achieve a robust MSSQL connection on Linux that is compatible with modern PDO best practices.

Our network