CodeIgniter 3: connecting to MS SQL from Linux

ms-sql1-300x120Connecting to Windows/Microsoft SQL from Linux/CodeIgniter remains challenging. As PHP progresses, various old methods of connecting to MS SQL are being deprecated in favour of (e.g.) PDO. Unfortunately, reliable MS SQL server PDO drivers are hard to come by under Linux.

As I’ve written previously, the most successful method I’ve found of connecting from CodeIgniter to MS SQL is using a combination of unixODBC and FreeTDS. So here’s an updated guide for CodeIgniter 3 on Ubuntu 14/PHP 5 or Ubuntu 16/PHP 7.

On the server where your web application runs, install the following packages: unixodbc freetds freetds-dev tdsodbc php5-odbc. For Ubuntu 14:

apt-get install unixodbc freetds freetds-dev tdsodbc php5-odbc

For Ubuntu 16:

apt-get install unixodbc freetds-common freetds-dev tdsodbc php7.0-odbc

Restart Apache:

service apache2 restart

Add the details of your MS SQL server to the FreeTDS config file (at /etc/freetds/freetds.conf), e.g.:

[my-server]
host = my-server.domain.local
port = 1433
tds version = 7.4

Note: that the TDS version shown above is for SQL Server 2012 (version 11). For more information about the TDS protocol version numbers (which don’t follow the Microsoft SQL version numbers), read the official documentation.

Add to /etc/odbcinst.ini (you may need to check the precise location of these .so files):

[TDS]
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

Add details of your MS SQL server to /etc/odbc.ini:

[my-server]
Driver = TDS
Description = My Server
ServerName = my-server
Database = MyDatabase

The ServerName above here corresponds to the name of your server in the FreeTDS configuration file. In the CodeIgniter database configuration file, add something like this:

$db['mssql'] = array(
'dsn' => '',
'hostname' => 'dsn=my-server;uid=myusername;pwd=mypassword',
'username' => '',
'password' => '',
'database' => 'MyDatabase',
'database' => '',
'dbdriver' => 'odbc',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);

Then your models should begin something like this:

class WidgetModel extends CI_Model
{
public function __construct()
{
parent::__construct();
// Load MS SQL connection
$this -> widgetdb = $this->load->database('mssql', true);
}

You can get some strange results using this driver. Mainly you’ll have to resort to explicit SQL queries. And certain things won’t work as expected – e.g. using “AS” to rename columns only works on calculated columns.

Connecting to Windows/MSSQL 2008 from Linux/CodeIgniter/PHP

Update: I’ve written a new article, covering CodeIgniter 3 and Ubuntu 14/16. Read it here.

Microsoft SQL Connecting to Microsoft SQL Express 2008 from Linux/PHP is a lot trickier than I expected. These notes are really for my own benefit so I can reproduce the setup, but maybe they’ll help you too. One of the problems is that many existing PHP drivers for MS SQL have difficulty talking to SQL 2008. Here’s a workaround using FreeTDS and ODBC.

My web application is built using CodeIgniter, the PHP application framework. It resides on an Ubuntu Server box, running Apache. Prerequisites on that Ubuntu Server (which I installed using Aptitude, but your favourite package manager will do):

  • unixODBC
  • FreeTDS
  • FreeTDS development package/(header files and libraries)

To my freetds.conf file (in /etc/freetds on my server) I added a section that looks something like this:

[my-server]
host = my.server.local
port = 1433
tds version = 9.0

Note: TDS version 9.0 should work with SQL 2008.

In /etc/odbcinst.ini, add the following ODBC driver (32-bit):

[TDS]
Driver = /usr/lib/odbc/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib/odbc/libtdsS.so

or 64-bit:

[TDS]
Driver = /usr/lib64/libtdsodbc.so
Description = FreeTDS driver
Setup = /usr/lib64/libtdsS.so

(You may need to check the precise location of the driver and setup files.)

And to /etc/odbc.ini, I inserted a DSN similar to the following:

[my-server]
Driver = TDS
Description = My Server
ServerName = my-server
Database = my-db

Generally within CodeIgniter, I am connecting to MySQL databases and that’s how my default connection is configured. I therefore added a second configuration to my database.php configuration file, like this:

$db['my_server']['hostname'] = "dsn=my-server;uid=myusername;pwd=mypassword";
$db['my_server']['username'] = '';
$db['my_server']['password'] = '';
$db['my_server']['database'] = '';
$db['my_server']['dbdriver'] = 'odbc';
$db['my_server']['dbprefix'] = '';
$db['my_server']['pconnect'] = TRUE;
$db['my_server']['db_debug'] = TRUE;
$db['my_server']['cache_on'] = FALSE;
$db['my_server']['cachedir'] = '';
$db['my_server']['char_set'] = 'utf8';
$db['my_server']['dbcollat'] = 'utf8_general_ci';

Now the ODBC driver within CodeIgniter can produce queries that MS SQL doesn’t like. We can fix this with a hack. You really REALLY shouldn’t do it this way (!) but to get things working and as described >here<, I edited the CodeIgniter core file system/database/drivers/odbc_driver.php. The function _from_tables() has a line reading:

return '('.implode(', ', $tables).')';

I changed it to this:

return implode(', ', $tables);

(In other words, we’re removing the spurious parentheses.)

I created a database method m_my_server.php like this:

/**
 * NOTE: We're using a feature-incomplete driver here.  Don't attempt to use
 * CodeIgniter's ActiveRecord Class or num_rows().  Use bare queries instead.
 */
class M_my_server extends Model {

  var $my_server;

  function M_my_server() {
      parent::Model();
      $this->my_server = $this->load->database('my_server', TRUE);
  }

  function get() {
    $query = $this->my_server->query('SELECT TOP(100) * FROM dbo.tblUserSummary');
    $result = $query->result_array();  // note ->num_rows() doesn't work with this driver
    if(count($result) > 0) {
      return $result;
    } else {
      return false;
    }

  }
}

/* End of file m_my_server.php */
/* Location: ./application/models/m_my_server.php */

At the SQL Server end, I set up a new standard SQL user (myusername/mypassword) rather than attempting to get Windows authentication to work (I bet it wouldn’t).

My SQL instance wasn’t listening to TCP/IP connections by default. I fired up SQL Server Configuration Manager and browsed to SQL Server Network Configuration –> Protocols for [my db instance]. Then you have to right-click TCP/IP and click Enable.

With all that in place, the following controller produces successful results:

  function SQLtest() {
    $this->load->model('m_my_server');
    $result = $this->m_my_server->get();
    if($result) {
      print_r($result);
    } else {
      echo 'nada';
    }
    exit;
  }

It’s not ideal; for one thing, bare SQL queries involve learning Microsoft’s particular dialect of SQL (whereas I’m used to MySQL). The tables I’m querying are generated by Microsoft Forefront TMG though, so I’m basically stuck with MSSQL. At least now I can query those tables from my favourite PHP application framework.

Image copyright © Microsoft. Used with permission from Microsoft.