How to connect to Microsoft SQL Server from Drupal

These are instructions on how to connect to Microsoft SQL Server 2014 (SQL Server Express) from Drupal. In my case the SQL server was on a different machine and I had to make the server visible within the local network.

My Windows machine is running on Windows 7 Pro and Microsoft SQL Server Express 2014 (free version).

My development machine is MacBook Pro running OS X Yosemite (the latest version).

Configuring Microsoft SQL Server

First if you would like to connect to your MSSQL Server from a remote machine you have to enable network access to it. To do so:

Enable a network protocol.

  1. On the Start menu, choose All Programs, point to Microsoft SQL Server and then click SQL Server Configuration Manager.
  2. Expand SQL Server Network Configuration, and then click Protocols for InstanceName.
  3. In the list of protocols, right-click the protocol you want to enable, and then click Enable. The icon for the protocol will change to show that the protocol is enabled.
  4. To disable the protocol, follow the same steps, but choose Disable in step 3.

Configure a network protocol

  1. On the Start menu, right-click My Computer, and then choose Manage.
  2. In Computer Management, expand Services and Applications, expand SQL Server Configuration Manager, expand Server Network Configuration, expand Protocols for InstanceName, and then click the protocol you want to configure.
  3. Right-click the protocol you want to configure, and then choose Properties.
  4. In Properties, you can set the protocol-specific options.

You may or may not be able to ping your server. If you can ping then you can try connect to your server via PHP. If not try next step (configure Windows Firewall).

Configuring Windows Firewall

In this instruction I am completley disabling Windows Firewall and other third-party firewalls. Please note this is not recommended if your server can be accessed from outside of your network. As long as it is accessible in you local network only you should be fine.

  1. Open Windows Firewall. Start > Control Panel > Windows Firewall.
  2. In the left pane click Turn Windows Firewall On/Off.
  3. Click Turn off Windows Firewall (not recommended) under each network location that you want to stop trying to protect, and then click OK.

You could keep it enabled (recommended) and a rule that would only allow access to Microsoft SQL Server's port which is defaults to 1433.

Installing and configuring Microsoft SQL libraries for PHP

Generic solution

Micorsoft SQL Server libraries used to be included with PHP and appearantly this no longer the case. If you are using an Microsoft IIS server, use the native SQL client called SqlSrv. For Mac OS you will need to install the FreeTDS library.

  1. Install the Sysbase library for PHP (FreeTDS is provided with this)

    sudo apt-get install php5-sybase

  2. Configure the freetds.conf file to use version 8.0 (this is necessary to work with MSSQL 2014 (works with 2008 too), if you do not do this you may encounter odd behavior with encodding)

    sudo vim /etc/freetds/freetds.conf

    [your-server-name]
      host = [your-mssql-server-ip]
      port = 1433
      tds version = 8.0
      # This might help to fix encoding issues.
      client charset = UTF-8
    
  3. Restart Apache

Alternative solution

This is what I ended up using Vagrant and Docker.

Portion of my Dockerfile:

RUN add-apt-repository ppa:ondrej/php5 && \
  apt-get update && \
  DEBIAN_FRONTEND="noninteractive" apt-get install --yes \
      freetds-bin \
      php5-sybase

I won't cover instructions about Vagrant and Docker in this blog post, but you can find more information on their websites. Watch out, you might decide never use your current webserver like MAMP, XAMPP (just kidding). I was really impressed with this solution and I would highly recommend Vagrant and Docker to all developers.

Configuring Drupal

This section is really depends on how you're going to use MSSQL in your project. In my case I was running content migration using Migrate module.

  1. I added the following to my settings.php:
    $conf['mssql'] = array(
      'database' => '[mssql-db-name]',
      'username' => '[mssql-user]',
      'password' => '[mssql-pass]',
      # /etc/freedts/freedts.cfg connection name.
      'servername' => '[your-server-name]',
    );
    
  2. Next try connect to MSSQL from PHP (this is old way of doing but it worked for what I needed):

    function query_mssql($query) {
      static $conn = NULL;
    
      if ($conn == NULL) {
        $mssql = variable_get('mssql', NULL);
        $host = $mssql['servername'];
    
        $conn = mssql_connect(
          $mssql['servername'],
          $mssql['username'],
          $mssql['password'],
          TRUE
        );
        if (isset($mssql['database'])) {
          mssql_select_db($mssql['database'], $conn);
        }
      }
    
      $result = mssql_query($query, $conn);
      return $result;
    }
    

The example above works great with the Migrate module but it is recommended to use PDO Driver for Microsoft SQL Server in case if you would like to pull data from MSSQL Server to display it in your website. Download DBLIB.

As I mentioned the solution provided in this blog post worked just fine for what I needed, however consider also using Drupal 7 driver for SQL Server and SQL Azure, instructions for the module can be found here.

Troubleshoot

  • Check Firewall to see if it's enabled, make sure you allow access to MSSQL's port (defaults to 1433).
  • Make sure SQL Browser is running.
    1. On the Start menu, right-click My Computer, and then click Manage.
    2. In Computer Management, expand Services and Applications, and then click Services.
    3. In the list of services, double-click SQL Server Browser.
    4. In the SQL Server Browser Properties window, click Start or Stop.
    5. When the service starts or stops, click OK.
  • If SQL Server Browser doesn't have start/stop option and if it is greyed out.
    1. Make sure the Server Browser service is enabled; it is set to “Start Mode – disabled” by default. It is a security best practice to not run the SQL Server Browser service by default, as it reduces the attack surface area by eliminating the need to listen up on an UDP port. But if you keep it disabled you won't be able to access your server from a remote computer.
  • Make sure you specified correct IP address.
  • Make sure to restart SQL services every time you make changes.
  • Make sure you're using correct port number (defaults to 1433).

Useful links

  1. How to configure SQL Express 2012 to accept remote connections
  2. Installing the MSSQL Libraries for PHP on Linux
  3. How to: Enable Network Access in SQL Server Configuration Manager (SQL Server Express)
  4. Turn Windows Firewall on or off
  5. How to: Start and Stop the SQL Server Browser Service (SQL Server Express)
  6. Connecting Drupal running in Windows to MS SQL Server