Using MS SQL with Dynamic Ports in PHP 7 with ODBC

While the pairing of PHP running on a Linux server and an MS SQL database running on a Windows server is rare and usually not the first choice, sometimes business realities force you to do it.

That’s what happened to me - we needed a connection between a modern web app and a 10-year-old Delphi application for which porting to a newer stack just didn't make sense.

After having to resort to reading mailing list archives and far, far too many StackOverflow threads, this is an account on how to go about doing it. The connection worked and was stable. I have not benchmarked the performance as that was not needed, but the queries returned as quickly as expected.

There was one bug I was unable to resolve - the workaround is explained later.

Environment

The connection was tested with PHP7 running on both Arch (Manjaro) and Ubuntu. The database server is running Microsoft SQL Server 2017 with dynamic port allocation and named instances. All servers are on a virtual private network.

I will only be covering the PHP side of things; a correctly configured MS SQL server and a working VPN connection (or lack of it) are presumed.

Client-Server Configuraton

Step 1: Install the required packages

We’ll be using the Open Database Connectivity (ODBC) engine, which provides a standard and predictable API for data sources, and FreeTDS, which is a Microsoft SQL Server driver for ODBC.

To start, install the following packages:

Arch instructions

sudo pacman -S odbc php-odbc freetds

Ubuntu instructions

sudo apt install php7.1-odbc tdsodbc unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

Step 2: Enable extensions in php.ini

If your system does not automatically enable new PHP extensions, uncomment or add extension=pdo_odbc.so manually in php.ini.

Make sure to restart PHP after you’re done to reload the configuration.

Step 3: Configure ODBC and FreeTDS

Now we edit a few interconnected configuration files. Let's start with odbcinst.ini, which defines drivers that are available to all system users.

You can find the file in /etc/odbcinst.ini

[FreeTDS] # This is your identifier! You'll need it in odbc.ini
Description = FreeTDS Driver
Driver = /usr/lib/libtdsodbc.so # Arch
# Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so # Ubuntu
fileusage=1
dontdlclose=1
UsageCount=1

Next, we edit the odbc.ini file which defines DSN's.

This can be found in sudo nano /etc/odbc.ini.

[mssqlconn] # This is important - you'll be refering to your DB with this
Description = MSSQL Server
Driver = FreeTDS # Same as identifier in odbcinst.ini
Database = NAME_OF_DATABASE # Not instance, database within the instance
ServerName = mssqlconn # This is the same identifier as in freetds.conf
TDS_Version = 8.0

And the last config file is freetds.conf, which defines the properties of your server and can be found at sudo nano /etc/freetds/freetds.conf

Add this to the end:

[mssqlconn]
host = # HOSTNAME or IP of your server
instance = # Name of the INSTANCE (not DB)
tds version = 8.0
client charset = UTF-8 # See below
text size = 64512 # You're gonna need to increase this if you're working with binary, image or large text fields.

Testing the Connection

Let's use a command-line tool to test if the connection to our MSSQL server works.

osql -S mssqlconn -U 'username' -P 'password'

If this works, you’re good to go!

Connect from PHP

We'll be manipulating the database with PDO:

$serverName = 'mssqlconn';
$dbName = 'YourDb';
$pdo = new PDO(sprintf("odbc:DRIVER=FreeTDS;SERVERNAME=%s;DATABASE=%s", $serverName, $dbName), $username, $pass);

That’s it! The rest is standard PDO.

Problems I Faced

Images in the database

So the job I was doing required me to extract images from an “image” field.

I should note that this was not the most frustrating part of the database I was working with. After years of battling with over-engineered databases that were developed in production, complete with "backup" and abandoned columns, per-developer naming/architecture styles, changing definitions of concepts and non-descriptive column names, a few embedded images didn't seem too bad.

Extracting images can’t (as far as I can tell) be done with PDO as you require binary mode for ODBC.

A minimal example:

$link = odbc_connect(
  sprintf(
    "DRIVER=%s;SERVERNAME=%s;DATABASE=%s",
    $odbc_driver,
    $odbc_name,
    $dbname
  ), 
  $user, 
  $pass
); 
$query = odbc_exec($link, "YOUR QUERY");
odbc_binmode($query, ODBC_BINMODE_RETURN);
while (odbc_fetch_row($query)) {
    $fieldValue = odbc_result($query, 'FIELD_NAME');
}

Unicode

This setup isn’t too fond of Unicode chars. The setting in freetds.conf seems to work most of the time, but I’ve found, on multiple systems, that the driver will randomly break and stop returning Unicode chars. This requires a PHP-FPM restart.

I should also note that on some machines it does not return a Unicode char (i.e. “č”) but rather a Unicode code (i.e. “\u010D”). This was less of a problem for me as all data from MSSQL was passed over a JSON API and json_decode will properly decode the codes into chars.

Conclusion

As you can see, atypical pairings like these are doable, even if they are painful for the developers implementing them. However, they make business sense - this solution cost us a fraction of what any other solution would have.

Having said that, I would never recommend going down this route on a new project - complicated and nonstandard technological stacks mean complicated and elusive bugs which always lead to unhappy developers and budget overruns.