Connect with Microsoft Sql server from PHP in xampp

Hello,

Recently I was working on a project in which requirement was to get data from Microsoft Sql Server from a PHP script. So this blog is about accessing MS Sql server from PHP using PDO object.

First thing is to add necessary extensions. Go to following site and download drivers for MS SQL server.

http://msdn.microsoft.com/en-us/sqlserver/ff657782.aspx

Once you extract it it will have number of dll files Now you have to identify correct DLL files for your system. This drivers are compatible with PHP version 5.3 . Now go to your XAMPP installation and search for php.dll
It will display correct PHP dll you have.

1) If you have php.dll then move following files to xampp/php/ext directory.

php_sqlsrv_53_nts_vc9.dll
php_pdo_sqlsrv_53_nts_vc9.dll

2) If you have php5ts.dll then move following files to xampp/php/ext directory.

php_sqlsrv_53_ts_vc9.dll
php_pdo_sqlsrv_53_ts_vc9.dll

above files should be used if your PHP version is compiled with Visual C++ 9.0 . Else following files should be used.

1) If you have php.dll then move following files to xampp/php/ext directory.

php_sqlsrv_53_nts_vc6.dll
php_pdo_sqlsrv_53_nts_vc6.dll

2) If you have php5ts.dll then move following files to xampp/php/ext directory.

php_sqlsrv_53_ts_vc6.dll
php_pdo_sqlsrv_53_ts_vc6.dll

Normally it should be VC 9 files.

Now we have to load files that we added recently. Open the php ini file and add entry in the area of dynamic extensions as follow.

 extension=php_sqlsrv_53_nts_vc9.dll
 extension= php_pdo_sqlsrv_53_nts_vc9 .dll

Save the ini files and restart you Apache from either XAMPP control panel or from Windows Services Console.

After that you can check php info as follow.

<?php

phpinfo();

?>

Check the pdo support. It should display sqlsrv now. That means modules are loaded successfully and drivers are available now. Use following code to connect to MS SQL server using sqlsrv

<?php
$serverName = ” serverNameinstanceName “;
$connection = array( “Database”=>”dbName”);
$conn = sqlsrv_connect( $serverName, $connection);

if( $conn ) {
     echo “Connection established.
“;
}else{
     echo “Connection could not be established.
“;
     die( print_r( sqlsrv_errors(), true));
}
?>

That’s it and you should be able to query Microsoft Sql Server now. Hope this helps you. This works with Microsoft Sql Server 2005, 2008 and 2008 R2.

This entry was posted in Driver, MS SQL, PHP on by .

About Hiren Dave

Hiren Dave is a software engineer, blogger and consultant with over 9 years of professional experience. He writes code in number of languages. Hiren has a passion for developing web apps, mobile apps and writing books and blog articles about programming. He has a solid experience of startups as he manages products for few startups. He is also passionate about researching and learning new technologies and trends. He has also authored a book Instant Sencha Touch, published by Packt and creator of Video Course "Beginning Laravel" for Packt

22 thoughts on “Connect with Microsoft Sql server from PHP in xampp”

  1. Jorge Zapata

    Nice article, I read the documentation from microsoft and couldt'n find exactly which dlls to load if I just want pdo support. Thanks to your article I did what I needed.

    Reply ↓
  2. Anonymous

    i got an error This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86 ….

    any idea???

    Reply ↓
  3. Anonymous

    sorry….but i can't find "php.ini" file ?
    and i don't understand what's this paragraph mean:
    "Now we have to load files that we added recently. Open the php ini file and add entry in the area of dynamic extensions as follow." ?
    thanks in advance,,

    Reply ↓
  4. Anonymous

    i get an error when i want to connect server…this is an error :

    "Unable to connect.

    Array
    (
    [0] => Array
    (
    [0] => 28000
    [SQLSTATE] => 28000
    [1] => 18456
    [code] => 18456
    [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
    [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
    )

    [1] => Array
    (
    [0] => 28000
    [SQLSTATE] => 28000
    [1] => 18456
    [code] => 18456
    [2] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
    [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
    )

    )
    "

    Reply ↓
  5. Anonymous

    I have installed SQLSRV20.EXE for my Win XP and MS SQL 2008 R2 in the ext directory. I can not find php.dll file anywhere. Modified the php.ini file as instructed. When I restart Apache it gives error saying php.ini file not found (something of this sort).

    Can anybody please help me.

    Reply ↓
  6. Anonymous

    Thanks Hiren for this page, it's helped me get SQL Server 2005 working with XAMPP.

    I'm using:
    Win XP w/ SP3
    XAMPP 1.80
    PHP Version 5.4.4
    SQL Server 2005 w/SP4

    For me, the Microsoft drivers listed in Hiren's post above didn't work.

    (Excerpt from my php.ini)…

    ; Drivers that work are here: http://downloads.php.net/pierre
    ;
    ; http://downloads.php.net/pierre/sqlsrv-5.4-ts-snap.zip
    ; sqlsrv-5.4-ts-snap.zip (2011-11-27 07:20 -0800)
    ; MD5 (sqlsrv-5.4-ts-snap.zip) = 3f885a897171d3a230ed16bf82fec5b9
    ;
    extension=php_sqlsrv.dll
    extension=php_pdo_sqlsrv.dl

    Setup is working now with these drivers.

    Reply ↓
  7. Anonymous

    @Anonymous: i got this message
    "This extension requires either the Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver to communicate with SQL Server."

    Reply ↓
  8. websalga

    Great my friend.
    I find for many days for solutions on compatibility drivers to work with PHP 5.4, apache, SQLITE integrated with MSSQL or SQLSRV.
    At this page was unique place where i found the solution, simple, clean, and exact. Very very thanks!!!!!!!

    Reply ↓
  9. Anonymous

    Hi, thank you for this tutorial.

    I've to add some things. If you are using PHP 5.4, the files are different.

    You have to download the SQLSRV30.EXE, uncompress it and copy to the ext/ folder the following files:

    php_pdo_sqlsrv_54_ts.dll
    php_sqlsrv_54_ts.dll

    Then, add the following lines to the php.ini in the extensions section:

    extension=php_sqlsrv_54_ts.dll
    extension=php_pdo_sqlsrv_54_ts.dll

    Reply ↓
  10. Yanzi

    hi i have just installed xampp and started the apache and its running but the mysql service is not running it doesnt start and this is what i see in the details:

    Attempting to start MySQL service…
    18:06:29 [mysql] Problem detected!
    18:06:29 [mysql] Port 3306 in use by ""C:Program FilesMySQLMySQL Server 5.5binmysqld" –defaults-file="C:Program FilesMySQLMySQL Server 5.5my.ini" MySQL"!
    18:06:29 [mysql] MySQL WILL NOT start without the configured ports free!
    18:06:29 [mysql] You need to uninstall/disable/reconfigure the blocking application
    18:06:29 [mysql] or reconfigure MySQL and the Control Panel to listen on a different port

    Reply ↓
  11. Anonymous

    Hi,

    I followed all the steps and getting following error.

    Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -49 [code] => -49 [2] => This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712 [message] => This extension requires the Microsoft SQL Server 2012 Native Client. Access the following URL to download the Microsoft SQL Server 2012 Native Client ODBC driver for x86: http://go.microsoft.com/fwlink/?LinkId=163712 ) [1] => Array ( [0] => IM002 [SQLSTATE] => IM002 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) )

    Any help would be appreciated.

    Thanks,

    XYZ

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *