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