Using MS SQL (dynamic ports) in PHP 7 with ODBC PDO

So I’ve just spent a painful number of hours to figure out how to connect to a Microsoft SQL Database (MS SQL) that’s using dynamic ports and named instances (if you’ve got a feeling I’m definitely not a M$ guy … you would be right).

Because I didn’t find a proper working how to on doing this and had to resort to reading mailinglist archives and far, far too many stackoverflow threads I’m posting everything here before I do my best to forget it.

Note: I use Arch locally and the server had Ubuntu, so this has been tested on both.

Step 1: Install required packages

# Arch
sudo pacman -S odbc php-odbc freetds

# Ubuntu
sudo apt install php7.0-odbc tdsodbc unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

Step 2: Enable extensions in php.ini

Uncomment extension=pdo_odbc.so

Make sure to restart PHP-FPM after you’re done.

Step 3: Configure ODBC and FreeTDS

Edit odbcinst.ini

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

If you’re not on arch you’ll have to find the location of libtdsodbc.so for your distro, or you can just use locate to find it.

Edit odbc.ini

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

 

Edit freetds.conf

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.

Step 4: Test connection

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

If this works, you’re good to go!

Step 5: Connect from PHP

$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 stuff.

Problems I faced

Images in database

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

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

Solution:

$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 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 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.

Missing data/truncated fields

The drive has a default maximum length of 64512 chars. If you need more than that (binary, text fields etc.) increase the setting in freetds.conf.

Leave a Reply

Your email address will not be published. Required fields are marked *