Installing Drupal on IIS with Microsoft SQL

Drupal 7 introduced the ability to use databases other than MySQL. This is a big step towards a server independent drupal. It has been possible to install drupal on Microsoft IIS for a long time, but now we can use a full Microsoft Server. For a quick development setup I suggest using the Microsoft Web Platform Installer and using either the Acquia or the Commerce Guys installers. This will create a nice development site with all the back end stuff installed automatically. If you need a more robust and configurable system for an intranet then I suggest installing the pieces on your own and using IIS. I found that IIS works well enough and the only gotcha is setting the file permissions. Windows has trouble figuring out and remembering what permissions have been set for files. Once the permissions are set then your intranet site will buzz along happily. Since my site will be behind a firewall and only accessed by mostly trusted people I haven't dug deep into securing IIS.

Install IIS

The first thing to do is install IIS. To do this on Windows 7 navigate to Start - Control Panel - Programs - Turn Windows features on and off. This will bring up the Windows Features list. For Windows Server 2008 you will find the add Windows Features in the Server Manager console.

Click on the '+' next to 'Internet Information Services' to expand the list. You don't need to install the FTP Server.

Under the 'Web Management Tools' check 'IIS Management Console', 'IIS Management Scripts and Tools', and 'IIS Management Services'.

Under the 'World Wide Web Services' check everything under 'Application Development Features' and also everything under 'Common HTTP Features'. The 'Health and Diagnostics' features are not important and can be installed later as well as the 'Performance Features'.

Under the 'Security' features I only check 'Request Filtering'.

Once all the checks are made click 'OK' and wait for the installers to complete. Look at the screen shot of my Windows Features for reference.

Open your web browser and go to http://localhost. You should see the IIS welcome screen. The root directory for IIS will now be located at C:\\inetpub\wwwroot\

After IIS finishes installing add the URL Rewrite feature by using the Microsoft Web Platform Installer. This will allow you to use clean urls in drupal. Later you will need to add the web.config file to the root directory of your drupal installation.

Install PHP

The easiest way to install PHP on Windows 7 is to use the Microsoft Web Platform Installer. Install the Web Platform Installer and then run it. Search for PHP 5.3 and install it. Then install the 'PHP Manager for IIS' from the Web Platform Installer. One important note is that FastCGI needed to be installed with IIS. In the steps above I made sure to click 'CGI' in 'Application Development Features' which is under 'World Wide Web Services'. In my case I also needed to register PHP with IIS. I registered PHP by running 'Internet Information Services Manager' then selected 'Default Web Site' in the left column and then double clicked the 'PHP Manager' on the right. Under the 'PHP Setup' I clicked on the blue text, "Register new PHP version" and followed the wizard. You may also need to enable the PDO SQL Server extension. Make sure it is located in the PHP/ext/ folder. Then edit the php.ini file by adding extension=php_pdo_sqlsrv_53_nts_vc9.dll and save the change.

To check if PHP installed correctly navigate Windows Explorer to C:\\inetpub\wwwroot\ and create a text file named check.php Copy and paste the following code into the file and save. You may need to run your text editor as admin. To run a program as admin go to the Start Menu and find your text editor, right click on it and select 'Run as administrator'.

<?php
phpinfo
();
?>
Now navigate your browser to http:\\localhost\check.php and you should see a list of all the php status information. Double check that the correct extensions are enabled including the PDO SQL extension.

Install SQL Server

I have an installation disk for SQL Server and simply fed it to my PC and ran the installer. Since I don't always trust Windows authentication to allow me to perform admin operations I activate the sa user account while going through the installation process. If this is the first installation of SQL Server on the machine I make it default, otherwise I give it a meaningful name like SQL2005, SQL2008r2, and SQL2012. Later I can access the servers that are not default by pointing to localhost\SQL2005 or localhost\SQL2012.

Create the drupal database in SQL by opening 'SQL Server Management Studio'. The name will vary a little based on the version of SQL you installed. Login using the 'sa' account or if you added yourself to the admin during the SQL installation then you can use your Windows account.

Once you are logged in right click on 'Databases' in the left hand column and select 'New Database ...' For the database name I like to call it something simple like 'drupal'. Leave all the options untouched and click 'OK'. That's it for SQL.

Install Drupal

Finally, getting to what we came for... well, almost. There is still a bit of customizing that needs to be done to the drupal files before we can install drupal. Since drupal defaults to using MySQL we need to add the Microsoft SQL drivers. We also need to add the web.config file to allow for clean urls.

Get started installing drupal by downloading the latest core. (At the time of my writing it is 7.12)

Then download the Microsoft SQL module. (I like to download the .zip version because Windows handles them well enough, otherwise I need a tool like 7-zip to handle the tars)

Delete all the files located in C:\\inetpub\wwwroot\ (these are just the demo IIS files that let you know it was working.)

Unzip the drupal core files and copy them to C:\\inetpub\wwwroot\ (Notice that we don't copy the parent folder 'drupal 7.12', just the files inside it.)

Now unzip the 'sqlsrv 7.x' file. Copy 'sqlsrv' to C:\\inetpub\wwwroot\site\all\modules and within the 'sqlsrv' folder there is another folder named 'sqlsrv'. This other folder needs to be copied to C:\\inetpub\wwwroot\includes\database\

One more file needs to be added. The web.config file. Navigate to C:\\inetpub\wwwroot\ and create a new file named 'web.config'. (Make sure that this file isn't renamed by windows to 'web.config.txt')

Open 'web.config' with a text editor and paste the following code. Then save it. (Again you may need to run your text editor as administrator by right clicking on your text editor in the start menu and selecting 'Run as administrator'.)

<?xml version="1.0" encoding="UTF-8"?>
    <configuration>
        <system.webServer>
            <rewrite>
                <rules>
                    <rule name="Drupal Clean URLs" stopProcessing="true">
                        <match url="^(.*)$" />
                        <conditions>
                            <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                            <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
                        </conditions>
                        <action type="Rewrite" url="index.php?q={R:1}" appendQueryString="true" />
                    </rule>
            </rules>
        </rewrite>
    </system.webServer>
</configuration>

Setting File Permissions

Now navigate your browser to http://localhost and begin the drupal installation. During the installation two problems may show up. The sites/default/files directory may not exist and is not writable and the settings.php file is not writable. These problems are fixed by creating the folder 'files' at C:\\inetpub\wwwroot\sites\default\files\

Right click on the 'files' folder and select 'properties'.

In the 'files Properties' pop-up window select the 'security' tab - Click the 'edit button' - Highlight 'Users' in the list and click the 'Allow' box next to 'Full Control' - Click 'OK' and then 'OK again

Change the security settings for the 'settings.php' file in the same way as was done for the 'files' folder.

Refresh your browser and you will be on your way with the normal drupal installation.

When you get to the database selection screen make sure to select 'Microsoft SQL Server' and fill in the SQL credentials. If you are using an SQL instance that is not default make sure you put the database path in the 'Database Host' field under the 'Advanced Options'. Click 'Save and Continue'.

I haven't had any core drupal problems beyond this point so finish up the installation and start developing your drupal site. Be aware that not all modules are fully compatible with Microsoft SQL and you may need to do some coding to work around the issues.

Happy Drupaling!

Note: I have used this method for both Windows Server 2008 r2 and Windows 7

Edit: Added the instructions for enabling the PHP extension php_pdo_sqlsrv_53_nts_vc9.dll

If you enjoyed this article, get email updates