Wednesday, January 25, 2012

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 = " serverName\instanceName ";
$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.











31 comments:

  1. 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.

    ReplyDelete
  2. THANK YOU VERY MUCH FOR YOUR POST!:)))))
    We've been searching since yesterday and this was the only one that saved us!:))))))

    ReplyDelete
  3. thanks... ur post is really useful to me.

    ReplyDelete
  4. many thanks, very helpful ;)

    ReplyDelete
  5. You are a life savior!!!

    ReplyDelete
  6. 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???

    ReplyDelete
  7. getting as undefined sqlsrv_connect() function...
    please help me in solving this...

    ReplyDelete
  8. 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,,

    ReplyDelete
    Replies
    1. it will be in php folder of xampp installation. You must load new extensions by specifying names in php.ini file.

      Delete
  9. 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 AUTHORITY\ANONYMOUS LOGON'.
    [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS 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 AUTHORITY\ANONYMOUS LOGON'.
    [message] => [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    )

    )
    "

    ReplyDelete
  10. Thanks Hiren; very very much. It works. :)

    ReplyDelete
  11. 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.

    ReplyDelete
  12. 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.

    ReplyDelete
  13. @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."

    ReplyDelete
    Replies
    1. Download SQL Server 2008 R2 Native Client from

      http://www.microsoft.com/en-in/download/confirmation.aspx?id=16978

      Delete
  14. thanks dude!!!!!!!

    ReplyDelete
  15. hi....i need sql server connection with android eclipse...Kindly give me the code so that i can learn it

    ReplyDelete
  16. 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!!!!!!!

    ReplyDelete
  17. Hi I did the procedure give above but in my XAMPP i.e, localhost/info.php .... I dont find the SQLSRV instead it shows no value now how to rectify the error am using MS SQLSERVER 2012

    ReplyDelete
  18. 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

    ReplyDelete
  19. 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 Files\MySQL\MySQL Server 5.5\bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my.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

    ReplyDelete
  20. 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

    ReplyDelete
  21. thanks dear, got connected in one go.

    ReplyDelete
  22. I got the same error as AnonymousSeptember 3, 2013 at 3:34 PM. Can you please help me?

    ReplyDelete
  23. i have been trying for 3 days !!
    and you save my life :-)
    thank you very much.

    i am not sure about my the problem, but i used WAMP instead of XAMPP. After reading your article, I use XAMPP.... and it works !!

    ReplyDelete
  24. Hi, can i use mssql_connect() with this sqlsrv.

    ReplyDelete
  25. PHP Startup:Unable to load dynamic library
    'C:\xampp\php\ext\php_sqlsvr_53_nts_vc9.dll' - The specific module could not be found. This message shown. My xampp version xampp 1.8.1. Please give me a solution

    ReplyDelete