Use PHP and PDO to connect to MS SQL Server

I had some "fun" today trying to get PDO connecting to Microsoft SQL Server. This turned out to be more of an effort than one would think, while at the same time it proved to be rather simple once I learned the secret. The hard part is finding the right information online - there is a LOT of outdated information out there. Here's the secret...

I was using a Ubuntu 9.04 installation, but the following *should* be enough to get you moving in the rigt direction for other distributions.

First, make sure you have MS SQL support installed. This is kinda counter intuitive as you need the "php5-sybase" package. There is a "php5-mssql" package, but this is a virtual package that installs php5-sybase and the supporting dependencies. So, assuming you already have a LAMP server installed, we start with

  sudo apt-get install php5-sybase
  sudo /etc/init.d/apache2 restart

- install the php5-sybase package, then restart the web server.

Now a lot of the docs you find via Google suggest that you install "freetds", "unixodbc", and more. Ignore that. With 9.04 this is no longer directly needed. Freetds WILL be installed as a dependency of php5-sybase, but we don't need to worry about this directly.

Now, create a PHP page with "phpInfo();" - something like this:

<?php phpInfo(); ?>

In the output, you are looking for the following:

(It will look a little different though.)

You won't see "mssql" listed there. What we want is the "dblib" - this should be there if the php5-sybase package is installed properly.

Now comes the hard part. It is the PDO adapter incantation that needs to change - not the core OS. We need to tell PDO to make use of "dblib".

To figure out the right way to set up PDO, go to http://ca2.php.net/manual/en/ref.pdo-dblib.connection.php. You'll see some comments under the DSN. In our case, we know we need "dblib" because that is what phpInfo() told us. With all that in mind, we might make use of this info like this:

<?php
    $dsn = 'dblib:dbname=testdb;host=127.0.0.1';
    $user = 'dbuser';
    $password = 'dbpass';
    $dbh = new PDO($dsn, $user, $password);
  ?>

OR, if we are using the Zend Framework:

 
<?php
    $db = Zend_Db::factory('Pdo_Mssql', array(
      'host'     => '192.168.35.143',
      'username' => 'dbuser',
      'password' => 'dbpass',
      'dbname'   => 'myDatabase',
      'pdoType'  => 'dblib'
    ));
    $result = $db->query("select * from someTable");
  ?>

With that, you *should* have a valid connection to your MS SQL server. I'll leave it up to you to decide what to do with that connection...