Drupal

Drupal on Windows: Using Windows Authentication with SQL Server

With the release of Drupal 7, it became easy to run the whole Drupal stack on Windows technologies. Instead of requiring a LAMP stack (Linux, Apache, MySQL, PHP), you now have the option of a WISP stack (Windows, IIS, SQL Server, PHP). The excellent Drupal 7 driver for SQL server makes using a SQL Server backend with Drupal possible. Even better, the underlying PDO (PHP Data Objects) driver for SQL Server supports Windows authentication! But wait… there are no instructions provided on using Windows authentication to SQL Server with Drupal! I knew that in theory it should be possible. After some tweaking, I finally figured it out. So how do you set it up? Here are the missing instructions…

Getting Windows Authentication working with SQL Server

Before you start, you will need either:

  • A domain user account, with both web server and SQL server joined to the domain
    -or-
  • Identical user accounts on both servers (same username and password); in this case the servers do not need to be joined to a domain

In my example below, I will use the identical user accounts method. Omit step 1 and substitute your domain account information wherever step 1 is referenced if you want to use the domain account method.

The process below makes a few assumptions:

  • You have a working Drupal instance that you’ve already set up with SQL authentication, and that you would now like to switch to use Windows authentication with the database
    • It’s easier to switch to Windows authentication after completing the initial setup
  • You’re using IIS 7.0 or later
  • You’re using PHP with FastCGI

Now that we’re up to speed, let’s get started…

1. Create a local user account on both servers, with the same username and password:

  • Computer Management -> Local Users and Groups -> Users -> New User
  • I used an account called DRUPAL_ANON

2. On the web server, add the user account created in step 1 to the IIS_IUSRS group:

  • Computer Management -> Local Users and Groups -> Groups -> IIS_IUSRS -> Add
  • This group helps grant correct permissions for a user account to run as an application pool

3. Change the IIS Application Pool of your Drupal website to use the user account created in step 1

  • IIS Manager->(Your Server)->Application Pools -> (Your Application Pool) -> Advanced Settings -> Identity -> Custom account

4. Change the properties of your Drupal website in IIS to use the application pool identity

  • IIS Manager->(Your Server)>Sites>(Your Website)->Authentication Use ‘Application pool identity’

5. Create a login on the SQL Server for the Windows user account you created in step 1

  • In SQL Server Management Studio, under Security -> Logins -> New Login

  • Make sure to grant the user access to the Drupal database (User Mappings)


6. Make sure fastcgi.impersonate is set to 0 in the php.ini file

  • Impersonation in this context is referring to taking the end user’s credentials (if Windows authentication were enabled in IIS) and having the PHP process impersonate that user
  • The PHP process will use the identity of the application pool by default, which in our case is what we want it to use in order to authenticate with the database

7. Edit your settings.php configuration file for Drupal sites\default\settings.php Change the username and password to NULL

  • Ensure that you remove the single quotations around the existing values, setting the new value to just NULL
  • If you pass NULL for username and password, the PDO driver for SQL Server will use Windows authentication when making a connection

 

8. Reset IIS for good measure

  • Issue the command line command ‘net stop was /y’, followed by ‘net start w3svc’
  • Some permissions changes require resetting IIS
  • Stopping the Windows Process Activation Service (WAS) will ensure the application pools are fully restarted

9. Access your drupal instance again, this time with Windows authentication to the database!

  • It works!

 

Background

A key for me realizing that this was even possible was the MSDN documentation. I was able to see that the parameters for username and password are simply set to NULL when using Windows authentication. At that point I knew that I should be able to change the settings and it should “just work” without making any changes to any code. Note the differences in the following code snippets:

SQL Server Authentication
$conn = new PDO( "sqlsrv:server=$serverName;Database = $database", $uid, $pwd);
Windows Authentication
$conn = new PDO( "sqlsrv:server=$serverName;Database = $database", NULL, NULL);

 

It was also not immediately obvious that you need to disable fastcgi.impersonate in PHP, since the comment in the php.ini file tells you that it should be enabled if you’re using IIS. Brian Swan’s blog post was tremendously helpful, even though the intent of the post was the opposite of what I was trying to accomplish.

References

Share the post "Drupal on Windows: Using Windows Authentication with SQL Server"

Email, RSS Follow
Email

Related posts: