How do I connect Drupal on UNIX and Linux to SQL Server?

Use the SQL Server ODBC driver to connect Drupal to Microsoft SQL Server. You can then access data stored in SQL Server from Drupal on Linux and UNIX systems.

Remote SQL Server data displaying in a Drupal site on Linux

The SQL Server ODBC driver is available for 32-bit and 64-bit Linux and UNIX (AIX, HP-UX, and Solaris) platforms.

Prerequisite software

  • Drupal
  • Drupal driver for ODBC databases
  • Apache
  • PHP and PHP ODBC
  • SQL Server ODBC driver

How to access SQL Server from Drupal on UNIX and Linux

These are the components that you need:

Apache / PHP/ Drupal -> Drupal driver for ODBC databases -> PDO / PDO_ODBC -> unixODBC -> SQL Server ODBC driver -> SQL Server

Step 1: Verify that you can retrieve SQL Server Data from PHP outside of Drupal

The Drupal driver for ODBC databases uses PHP's PDO_ODBC driver. Before attempting to retrieve your SQL Server data from Drupal, you need verify that you can retrieve your data from PHP by using PDO_ODBC.

  1. Important If you're using a 64-bit version of PHP, you need to download the 32-bit SQLLEN version of the SQL Server ODBC driver. This is available from the Easysoft FTP site. In the directory for your platform on the FTP site, choose the latest SQL Server distribution. Choose the driver without the -ul suffix. For example, for 64-bit Linux, you would download odbc-sqlserver-n.n.n-linux-x86-64.tar not odbc-sqlserver-n.n.n-linux-x86-64-ul64.tar.

    Refer to this Easysoft article for additional information.

  2. Follow the steps in the PDO_ODBC section of our PHP tutorial. This shows you how to retrieve SQL Server data from a PHP script by using PDO_ODBC. Note that you won't be able to retrieve SQL Server data from Drupal until you done this.

Step 2: Retrieve SQL Server data from Drupal

  1. On your Drupal machine, download and install the Drupal driver for ODBC databases. For example: 
    $ cd /tmp
$ git clone --branch master http://git.drupal.org/sandbox/pstewart/2010758.git drupal_odbc_driver

    Note that at the time of writing, the web page for this driver states that prepared statements are not working. Looking at the driver's bug report page, this is related to PDO_ODBC's behaviour when the underlying driver does not support SQLDescribeParam. The SQL Server ODBC driver does support this ODBC API and we did not find any issues with prepared statements during testing.

  2. Copy the odbc subdirectory to the Drupal database abstraction layer directory: 
    $ cd drupal_odbc_driver
$ mv odbc /var/www/html/includes/database
  3. In the Drupal settings file (settings.php), add a new database that uses this Drupal driver and specifies the SQL Server connection details: 
    $ cd /var/www/html/sites/default
$ vi settings.php
  4. Locate the databases array in settings.php. For example: 
    $databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'mydb',
      'username' => 'mydrupaladmin',
      'password' => 'password',
      'host' => 'mysite',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);
  5. Add a new database to the array: 
    $databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'mydb',
      'username' => 'mydrupaladmin',
      'password' => 'password',
      'host' => 'mysite',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
 'external' => 
  array (
    'default' => 
    array (
      'odbc_driver' => '{Easysoft ODBC-SQL Server}',
      'database' => 'mssqldatabase',
      'username' => 'mssqluser',
      'password' => 'password',
      'host' => 'mssqlhost:mssqlport',
      'port' => '',
      'driver' => 'odbc',
      'prefix' => '',
    ),
  ),
);

    Replace:

    • mssqldatabase with the SQL Server database that you want to connect to. The example later in this tutorial uses the AdventureWorks database.
    • mssqluser and password with a valid SQL Server login and the password for this login.
    • mssqlhost:mssqlport with the host name or IP address of the SQL Server machine and the port that the SQL Server instance is listening on.

Create a Drupal module that uses and retrieves data from the new database:

  1. In the Drupal directory for contributed modules, create a directory named mssql. cd into the new directory and create two files named mssql.info and mssql.module: 
    $ cd /var/www/html/sites/all/modules/contrib
$ mkdir mssql
$ cd mssql
$ touch mssql.info mssql.module
  2. Add these lines to mssql.info: 
    name = SQL Server Drupal ODBC Demo
description = "Retrieve remote SQL Server data from Drupal on Linux and UNIX."
core = 7.x
files[] = mssql.module
  3. Add these lines to mssql.module: 
    <?php

function mssql_menu(){
        $items['mssql'] = array(
                'page callback' => 'mssql_page',
                'access arguments' => array('access content'),
        );
        return $items;
}

function mssql_page(){

        // Switch to the SQL Server database
        db_set_active('external');

        // Retrieve and display AdventureWorks data
        $result = db_query('select Title, FirstName, LastName, EmailAddress, Phone from Person.Contact
                            where ContactID <= :id', array(':id' => 10))->fetchAll();
        $output = "<table>";
        foreach($result as $item) {
            $output = $output . "<tr><td>" . $item->Title . "</td><td>" . $item->FirstName . "</td><td>" .
                      $item->LastName . "</td><td>" . "</td><td>" . $item->EmailAddress . "</td><td>" . "</td><td>" .
                      $item->Phone . "</td></tr>" ;
        }
        $output= $output . "</table>";

        // Switch back to the default database. Otherwise Drupal will send SQL to SQL Server which the database
        // either will not understand or which relates to tables that will not exist in SQL Server
        db_set_active();
        return $output;
}
  4. In the Drupal Module administration pages, enable the mssql module (it will be called SQL Server Drupal ODBC Demo).
  5. In you Drupal site, visit the page that the mssql module creates. This will either be http://mydrupalsite/mssql or http://mydrupalsite/?q=mssql depending on how your site is configured.

Further information