Tuesday, November 29, 2011

Compiling PHP with MSSQL Server's Native ODBC Driver for Linux as a PDO Driver


Last month, MS announced the preview release of SQL Server ODBC Driver for Linux, a 64-bit binary driver for Red Hat Enterprise Linux 5. This is good news for companies using heterogenous platforms e.g. LAMP stack running a PHP application that connects to both MySQL and MSSQL Servers. Meaning, these companies no longer have to use third-party drivers such as FreeTDS that MS doesn't support. Then a few days ago, MS released version 1 of the driver. I immediately downloaded the driver and recompiled PHP with it as a PDO  (PHP Data Object) ODBC driver :-) 


Below are the steps on how to build PHP using the SQL Server ODBC Driver as a PDO driver:

Install the SQL Server ODBC Driver for Linux
* follow the instructions here

Recompile PHP
$> ./configure --with-pdo-odbc=unixODBC,/usr/local --with-unixODBC=/usr/local ...
$> make
$> make install


Configure ODBC
/usr/local/etc/odbcsys.ini
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
UsageCount=1


/usr/local/etc/odbc.ini
[MSSQLServer]
Driver = SQL Server Native Client 11.0
Description = Sample Database
Trace   = Yes
Server  =
Port    = 1433
Database=

Test MSSQL Connection


mssqltest.php
<?php
    putenv('ODBCSYSINI=/usr/local/etc');
    putenv('ODBCINI=/usr/local/etc/odbc.ini');
    $username = "";
    $password = "";
    //ODBC
    $connection = odbc_connect("MSSQLServer", 
                               $username, 
                               $password
                              );
    $connection = odbc_connect("MSSQLServer", $username, $password);
    echo var_dump($connection);
   //PDO
   try {
     $dbh = new PDO("odbc:MSSQLServer",
                    "$username",
                    "$password"
                   );
   } catch (PDOException $exception) {
     echo $exception->getMessage();
     exit;
   }
   echo var_dump($dbh);
   unset($dbh);
?>


$> php ./mssqltest.php


Gotchas and Performance
Failed to get DB handle: SQLSTATE[IM002] SQLConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified
Solution: Review your ODBC and ENV Settings

Failed to get DB handle: SQLSTATE[28000] SQLConnect: 18456 [unixODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ''
Solution: Review your MSSQL Credentials

Failed to get DB handle: SQLSTATE[HYT00] SQLConnect: 0 [unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout expired
Solution: Enable TCP/IP on your MSSQL Server:
  1. Run the command netstat -an to verify if there's a listener on TCP:1433
  2. If there's none, run your SQL Server Configuration Manager, then go to SQL Server Network Configuration > Protocols, then set TCP/IP to Enabled
  3. Restart your SQL Server

I'll keep you guys posted (better yet, keep me posted) about the performance improvement or if there are any gotchas on the driver's version 1.