Friday, 5 April 2013

Calling SQL Server from Yii on Ubuntu

I hit an issue today that I didn't think would still exist. There is no native way to call SQL Server from Linux despite both their existence for a long time. It seems Microsoft have created a Native driver but it only exists on Windows (not surprisingly).
Anyway, there is a way and it uses ODBC (generic database connections) + TDS (some old-school database protocol that SQL Server supports). More specifically, we will use free-tds, a Linux project.
Most of the information at this blog post is correct but the example you are supposed to copy did not exist in my installation (Ubuntu 12.10) and the Yii specific part was missing (although I assume this would mostly be the same as per other database technologies and frameworks). So it is in a nutshell:

  1. Install freetds-bin, freetds-common, tdsodbc, odbcinst, php5-odbc and unixodbc from the package manager. Some of these will pull in some of the others as dependencies.
  2. Edit /etc/odbcinst.ini and add in the following lines:

    [mssql] # note this must match the driver name in the connection string
    Description = MS Sql Server
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    UsageCount = 1
    FileUsage = 1
  3. Restart your web server: sudo service apache2 restart
  4. Set your Yii connection string thus:

    'connectionString' => 'odbc:Driver=mssql;Port=1433;Server=192.168.1.115;Database=MyDatabase;UID=username;PWD=password123'
  5. Note the colon between odbc and Driver, it is not a semi-colon.
  6. It should all work fine
  7. Note this is not a great solution because TDS is old-school and no doubt not optimised. Also odbc is an abstraction level which may or may not help performance. I think I eventually might run the php on a Windows box which can access a native PHP PDO driver.

Post a Comment