PHP: Accessing MSSQL/Sybase from a Linux server


Want to access a MSSQL or Sybase server from a CentOS server with PHP? Ouch...

But there is hope!


I'm going to use ODBC (Open Database Connectivity) as the database driver, and for that we need to install a couple of things. First of, we need FreeTDS to be able to talk to the MSSQL/Sybase database. FreeTDS is an open source implementation of the Tabular Data Stream format which is used by these databases. Second, we need the unixODBC driver, which gives other programs an API to talk to. And finally, we need to install the php-odbc module in order for PHP to be able to talk to unixODBC.

Installing is easy, just run the following commands:

yum install unixODBC
yum install freetds
yum install php-odbc
service httpd restart


After installation you should have the following files on your filesystem:

  • /etc/freetds.conf
  • /etc/odbcinst.ini
  • /etc/odbc.ini

We'll edit these files with information on our data source. Our PHP programs will then be able to talk to the MSSQL/Sybase database via the data source.

First off, we need to tell FreeTDS where our database server is located. We'll give the server the descriptive name "my_server".


    tds version = 8
    timeout = 30
    connect timeout = 30
    text size = 64512

    host =
    port = 2028
    client charset = UTF8

Note that the [global] config is applied to all servers. I have taken the opportunity to set a reasonable timeout value here. If this is not set, FreeTDS will never time out.

Next, we configure FreeTDS as the driver to use with unixODBC.


    Description = ODBC for Sybase and MSSQL
    Driver      = /usr/lib64/
    Setup       = /usr/lib64/
    Driver64    = /usr/lib64/
    Setup64     = /usr/lib64/
    FileUsage   = 1

Note: My machine is 64 bit. If you run a 32 bit machine, you probably need to point to the 32 bit drivers.

Finally, we configure the data source. The data source should use the server we configured in freetds.conf, and the FreeTDS driver we configured in odbcinst.ini.


    Driver      = FreeTDS
    Description = My one and only data source
    Servername  = my_server
    Database    = my_db

Verifying the installation

Before we move on to PHP, it's handy to check that the installation works properly. To test that FreeTDS can access the database, run the following command:

tsql -S my_server -U <your-username>

If that works fine, check that the data source is usable:

isql my_data_source <your-username> <your-password>

If you're unlucky, like me, unixODBC won't work as it should. To see what's up, you can enable logging. Add the following to /etc/odbcinst.ini:


In my installation, it turned out that unixODBC was looking for an older driver in /usr/lib64/ The solution was to make a symlink to the newer driver:

ln -s

Usage in PHP

Now that we have a working data source, let's try to access it from PHP. You have a couple of options here, but the best is probably via PHP PDO. However, in my installation, PDP PDO ends up with a segfault every time a fetch function is called. I was not able to fix this, so I ended up using the freestanding odbc functions instead. And to make it worse, I wasn't able to use prepared statements, as they also caused segfaults. Weirdly enough, everything works fine on my Windows installation, so this is probably something with the combination of unixODBC and PHP.

Anyway, here is an example of how to use the odbc functions with our data source:

class WinDB{
    private $odbc_conn;

    function __construct($data_source_name, $user, $password){
        $this->odbc_conn = odbc_connect($data_source_name, $user, $password);

        if($this->odbc_conn === false){
            throw new Exception("Couldn't connect odbc: " . odbc_errormsg($this->odbc_conn));

    function __destruct(){
        if($this->odbc_conn !== false){

    public function query($query){
        $resource = odbc_exec($this->odbc_conn, $query);

        if($resource === false){
            throw new Exception("Couldn't execute odbc query: " . odbc_errormsg($this->odbc_conn));

        // Hacky time: Need to get the entire result set to free the cursor.
        $res = array();
        while($row = odbc_fetch_array($resource)){
            $res[] = $row;

        return $res;

One thing to note here is that I advance the cursor all the way to the end before I return the result set. This is because you can only have one cursor open at a time with odbc, and it is not possible to fetch results from any other queries before the cursor is freed.

The second thing to note is that the example does not use prepared statements, nor does it escape any variables. As such, use with caution!

comments powered by Disqus