IIS Tutorial Series (Part 2): Talking to SQL Server with PHP on IIS

August 7th, 2010 | Tags: , ,

Note: This article is Part 2 of the IIS Tutorial Series.

Starting with a little history, before PHP 5.3, the options of getting PHP to talk to a Microsoft SQL Database Server was either using a generic database connector, such as ODBC, or PHP’s included MSSQL connector extension. The MSSQL extension was nice compared to ODBC as you didn’t have to set up a separate object (the ODBC connection object) to talk to the SQL Server. The MSSQL extension is very similar to PHP’s popular MySQL Extension as there are functions solely for that database type. Disappointingly, beginning from PHP 5.3, that MSSQL has been deprecated and no longer included with the PHP package, as it was stated to be unstable and unofficially supported (as well as maintenance stopped a long time ago). Initially, people started re-compiling PHP 5.3 on their own with the old MSSQL to get the functionality back, but like for the most of us, re-compiling PHP is not something we would want to step into.

Fortunately, Microsoft stepped up their game and finally started to support more open-source projects such as PHP, and created an official driver to talk to their SQL Server with PHP. In this tutorial, I will go over how to install the SQL Server Driver, and connect to an SQL Server with PHP. This tutorial is applicable for SQL Server 2000 or later, using PHP versions 5.2.4 or later.

Note: You’ll need to have PHP installed on the IIS Server. If not, read Part 1 of the tutorial series on how to do so.

First, visit Microsoft’s page to download the SQL Server Driver for PHP. As of this writing, the driver is currently version 2.0. If your SQL Server is installed remotely (not on the same server as the IIS server), you’ll need to download the Microsoft SQL Server 2008 Native Client at Microsoft’s SQL Server 2008 Feature Pack site. If the SQL Server is on the same server, then you’ll already have the necessary Native Client library installed, and you can skip the following step. There are a lot of packages on that page. To quickly locate the right one, scroll down until you find a section “Microsoft SQL Server 2008 Native Client”, or you can use your web browser’s page search functionality to jump to it immediately. Download MSI package appropriate for your server.

If you had to download the SQL Server Native Client, run that executable to install it first. Then, execute the downloaded file for the SQL Server Driver. This will just do a self-extract to a folder. Save the extracted folder anywhere you want. In the folder will be a set of DLLs. Choose the DLL that is appropriate for the PHP package you are using. For more information on which DLL to use, look at the readme that’s also included in the folder. Copy that DLL and paste it into the “ext” directory of your PHP folder (e.g. C:\php\ext\). This adds the extension for PHP to use, and contains the SQL Server API functions you use in your code. This extension talks to the Native Client, which is the interface to the SQL Server.

Next, open up the php.ini in your PHP installation folder, and enable that extension. There should be a section with many lines of:
extension=xxxxxxxxx.dll

Add a new line that is specific for the DLL you are using. e.g. extension=php_sqlsrv_53_nts_vc9.dll

Restart the IIS Server for all the settings to take effect. To check that the extensions is working, create a PHP script on your website with phpinfo and the section for the “sqlsrv” extension should be in there somewhere.

That should be it for the installation. In the folder for the SQL Server driver should also be a file named SQLSRV20_Help.chm, which is a help file that includes the API documentation of the driver. There is an online version of the documentation as well.

For example, to connect to a SQL Server database with PHP, you can use something similar to the following code:

$connectionInfo = array('UID'=>'Username', 'PWD'=>'password', 'Database'=>'Northwind');
$conn = sqlsrv_connect('MY-DBSERVER', $connectionInfo);
if ($conn === false) die( print_r( sqlsrv_errors() ) );

Note: This code applies to authenticating as a SQL Server user, and not a Windows Authentication user. I have not gotten successful connection as a Windows user. If anyone knows how, please leave a comment.

  1. Dethix
    January 21st, 2011 at 00:15
    Reply | Quote | #1

    Hello!
    I considered myself lucky to finally find a tutorial to set that thing up… just to find out that i did exactly the same, but when i invoke the site with phpinfo() it says “–without-mssql” and “–without-pdo-mssql” in “Configure Command”.
    The only difference that catched my eye was that i use the thread safe version of PHP. Could this be the reason ?

    Additional info: I want to set this configuration up to run Jinzora Media Server (see http://en.jinzora.com/ )

    Thx in advance!

  2. Dethix
    January 21st, 2011 at 00:43
    Reply | Quote | #2

    Sorry for repost…
    Installed the NTS version now and reviewed the php.ini, but still no success… this is slowly driving me mad, already used hours to solve this…
    Forgot some info:
    – IIS 6.0, Windows Server 2003 Standard SP2 x86 with FastCGI
    – MSSQL 2005 (same machine as webserver)
    – PHP 5.3.5 MSVC9 NTS

  3. January 21st, 2011 at 09:33
    Reply | Quote | #3

    Dethix,

    I think the “–without-mssql” and “–without-pdo-mssql” should be okay. I think this is indicating that the old mssql extension is no longer being used. (My installation with 5.3.3 has this -without-mssql command listed too.)

    What you should be looking for is “sqlsrv” in phpinfo. That’ll indicate that Microsoft’s SQL Driver is working in PHP.

    Edit: Fixed some typos…

  4. Dethix
    January 22nd, 2011 at 01:43
    Reply | Quote | #4

    Ah, ok… this is kinda confusing though.
    Jinzora still said that PHP had no MSSQL support. In fact it was the Native Client 2008(!) that was missing, the 2005 Native client is not sufficient
    I should have tried to connect to the MSSQL server manually, e.g. by using your script above, that would have saved me some hours
    Anyway, Jinzora still says there is no PHP MSSQL support… i’m going nuts

    Thanks a lot!

  5. January 22nd, 2011 at 08:48
    Reply | Quote | #5

    Dethix,

    That’s great you got it working! Yep, getting the latest version of Native Client will have backward support for MS SQL Server 2005 and 2000, and maybe earlier too.

    I’m not sure where Jinzora says that, but here’s the fact that I know:

    Before PHP 5.3, PHP had an unofficial MSSQL Extension, bundled with PHP. It also has a generic ODBC Extension to talk to any type of database that supports ODBC (this includes MSSQL).
    (Though certain functionality of the database might be limited if you use ODBC.)

    From PHP 5.3+, the unofficial MSSQL Extension has been removed. ODBC support and similar generic extensions still remains that you can talk to SQL Server with.

    Eventually, Microsoft came out with their SQL Server Driver for PHP. This is an official driver (obviously, since Microsoft created SQL Server, they can make official drivers that talk to it.) PHP 5.3 and 5.2 can use this driver, so this technically -should- be the driver to use these day regardless of which version of PHP you have.

  6. Name
    July 24th, 2012 at 01:36
    Reply | Quote | #6

    Thanks a lot