Connecting drupal to MS SQL Server

Introduction

While working for a client I needed to make a connection to Microsoft SQL Server. After investigating I found out there are two options: connecting with a PDO driver (only possible on the windows platform for now) or via an ODBC connection. Today I would like to explain the first option, as I needed some time to find out how to do it and combine all the small pieces.

Windows and PDO

I started from a clean WAMP install on windows. The first thing you need to add is an extension on PHP, a PDO driver for SQL Server.
Go here to download the package.

Then install the package

For WAMP the extension directory of PHP is here:

c:\wamp\bin\php\php5.3.10\ext

Enable the PDO driver

Once installed, you should see this in your c:\wamp\bin\php\php5.3.10\ext

It installs 12 dll files. This looks a bit chaotic, but you need to know how this works. Those dll files work by pair and you get versions for PHP 5.3 and PHP 5.4, compiled with Visual C++ 6 (vc6) or Visual C++ 9 (vc9) and come in thread safe (ts) and non-thread safe (nts) versions.

Choose the right version. I had PHP 5.3.10 so I needed the dll files for PHP 5.3. My PHP was compiled vc9, so the vc9 version was the one to go with. And I did go with the thread safe version. When choosing the wrong versions, wamp will issue an error when restarting.

Finally these were the two dll files to enable in my case:

• php_pdo_sqlsrv_53_ts_vc9.dll
• php_sqlsrv_53_ts_vc9.dll

To enable those dll files, you need to change the php.ini. In wamp, click left on the wamp icon on the outer right lower corner of your screen, choose PHP then php.ini. Paste these lines below the extension list:

extension=php_pdo_sqlsrv_53_ts_vc9.dll  
extension=php_sqlsrv_53_ts_vc9.dll

This will be the result

Restart WAMP (you need to restart the webserver to load the new php.ini). Then browse to http://localhost This will give you the possibility to load phpinfo(). Follow the link, then use ctrl+f to look for “PDO”.

Microsoft SQL Server Native Client

Don’t forget this part, as you might see this error in your drupal recent log messages when not installed:

So let’s install the native client, by accessing this link

Choose the appropriate version for your windows OS (32-bit or 64-bit).

Configuration in drupal

Now comes the drupal part. We first need to install the sqlsrv module

I installed this module in sites/all/modules/contrib.
There is one part of the installation you have to make sure you don’t forget. Inside the sqlsrv module (sites/all/modules/contrib/sqlsrv) there’s another sqlsrv folder. Copy this folder to /includes/database.

Configure the MS SQL Server database in settings.php:

$databases = array (
'default' =>
array (
  'default' =>
    array (
      'database' => 'database1',
      'username' => 'db1',
      'password' => 'pass1',
      'host' => 'localhost',
      'port' => '3306',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
'custom' =>
array (
  'default' =>
  array (
    'database' => 'test2',
    'username' => 'db2',
    'password' => 'pass2',
    'host' => 'localhost',
    'driver' => 'sqlsrv',
  ),
),
);

In this config sample I made two connection: a mysql connection, as drupal was working on mysql and a sql server connection to retrieve data. If you want to run drupal on Microsoft SQL Server, configure the default connection as the "sqlsrv" one.

Now you’re set to try to make a connection to MS SQL Server from a drupal module. Change the db connection in your module by using the API:

db_set_active('custom');