Question

  • Creator
    Topic
  • #2271657

    Connect to MS SQL with PHP

    Locked

    by joelataylor ·

    Hey guys … I’ve been having major issues trying to connect to our ms sql database using PHP. (MS SQL 2005)

    Here’s the conditions:

    I CAN CONNECT TO OUR REMOTE DB with:
    PHP 5.2.3
    IIS 5.1
    on my local machine that is logged into the network.

    I CAN’T CONNECT TO OUR REMOTE DB with:
    PHP 5.2.3
    IIS 6
    when files are on our server.

    I think it has something to do with authentication. The server is set to accept both Sql Server users and Windows Authentication. I think the fact that I’m already logged in to my machine with my user/pass it acts as authentication to get to the db – but when I’m on the server it doesn’t have those login details – so it’s just failing.

    Can anyone help me out?
    Maybe I should use php’s PDO?

All Answers

  • Author
    Replies
    • #2593193

      Clarifications

      by joelataylor ·

      In reply to Connect to MS SQL with PHP

      Clarifications

    • #2589303

      Connect to MS SQL 2005 with PHP

      by pradeep ·

      In reply to Connect to MS SQL with PHP

      Hi Joe,

      There are few conditions after filling you will be able to connect to MSSQL 2005. There are various ways to connect to MSSQL 2005 through PHP but it is dependent on the bridge between PHP and MSSQL.

      Ok. I am starting from the scratch and from the simple way.

      ** Before going further, I am assuming that you had installed your PHP in C:\php with all steps mentioned in installation file (install.txt) and MSSQL 2005 on different machine.

      1. First of all, you need to replace the MSSQL Client Library file ?ntwdblib.dll?, which has shipped with PHP distribution. Actually, it is an old client library file and only work with SQL Server 7.0/2000. You need to download new version of this file from http://webzila.com/dll/1/ntwdblib.zip, then extract this file and replace the existing.

      2. In MSSQL 2005, the protocol support for TCP/IP and NAMED PIPES has disabled by Default. Therefore, if your application is using any of these protocols to connect to MSSQL 2005, it will never reach to MSSQL.

      To make the MSSQL available for your application you need to enable these protocols by as follows

      Go to Start  Settings  Control Panel  Administrative Panel  Computer Management  Services and Applications  SQL Server Configuration Manager  SQL Server 2005 Network Configuration  Protocols for SQL Server

      Now enable TCP/IP and Named Pipe

      Similarly do the same for SQL Native Client Configuration  Client Protocols.

      3. Now edit PHP.ini and remove the semi colon (;) before line extension=php_mssql.dll, extension=php_pdo_mssql.dll.

      4. Restart your server.

      5. Check the MSSQL server functionality with the following script.

      ** replace the dummy values from left side with your MSSQl server details.

      Save the above script in file with extension .php in document root of your web server (drive name:\Inetpub\wwwroot) and run it in your browser. If it shows, ??could not connect to SQL Server on? then you need to check the PHP installation or the MSSQL service status.

      At last, if you face any problem then email me

      Cheers

      • #2587223

        Reply here…please.

        by footlessrabbit ·

        In reply to Connect to MS SQL 2005 with PHP

        If it’s all the same, I would like to know the results.

        • #2596446

          I went with DSN

          by joelataylor ·

          In reply to Reply here…please.

          I eventually just created a DSN on our server and connected to the database through that.

          $dsn = “DSN-CON”;
          $username = “user”;
          $password = “pass”;

          $sqlconnect = odbc_connect($dsn,$username,$password);

        • #2596276

          Connect to MSSQL Server 2005 through DSN using PHP

          by pradeep ·

          In reply to I went with DSN

          Ok dear but in the same case, you need to enable support of TCP/IP of your SQL Server to reach the connection by PHP. Anyway, I am elaborating the ODBC connection from the scratch, so here it is

          1. As I stated you earlier, you need to open TCP/IP support of your SQL Server to connect from PHP. In addition, you need to install MSSQL Client on the client machine from which you want to connect to the Remote MSSQL Server.

          2. In my example, I had created a System DSN at client machine. Here are the dummy details of System DSN

          Go to Start  Settings  Control Panel  Administrative Tools  Data Sources (ODBC). Click on ?System DSN? tab  Click on ?Add? , In the available list box you will see there are two options, one is SQL Native Client and Other is SQL Server. Try any of these and provide your remote MSSQL Server details along with Username and password. Test your connection if it is working. If yes, then we had done almost.

          ** (Here I use SQL server user account, not the windows authentication. There is another way to use windows authentication but for the easiness, I am discarding that option here)

          3. Open any text editor. Write this test script, and save as this file .php into your web server?s document root.

          ;DATABASE=tempdb’;
          //$user = ‘sqltestclient’;
          //$pass = ‘paSSword’;

          $connection_string = ‘DRIVER={SQL Server};SERVER=;DATABASE=‘;
          $user = ‘sql server user name’;
          $pass = ‘password of above user’;
          $connection = odbc_connect( $connection_string, $user, $pass ) or die(“Unable to connect to server”);
          echo $connection.’ ‘.$user.’ ‘.$pass;
          ?>

          4. Now, run the above file from your browser. If it says ?Unable to connect to server? then you need to check PHP installation, So if you face the same error again then write me your PHP installation process.

          ** I am not a blogger but here I got an idea to write technical blog. I will let you know when I finish my initial posting of ?How to install PHP in Windows in Simple and Clean manner?.

      • #2825058

        regarding mssql connection using php

        by dipanchokshi ·

        In reply to Connect to MS SQL 2005 with PHP

        hi all
        i am stuck to connect mssql database using php.
        can you guide me on below error ?

        Warning: mssql_connect() [function.mssql-connect]: message: Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (severity 14) in C:\wamp\www\nimspl\conn\config.php on line 23

        Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: vbserver\mssql in C:\wamp\www\nimspl\conn\config.php on line 23
        Could not connect to SQL Server on vbserver\mssql Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

        i am getting this error only.

        below is my php code

        $mssqlHost = “vbserver\mssql”; #It is MSSQL Instance name
        $mssqlUser = ‘nimspl’;
        $mssqlPass = ’02admin#’;
        $mssqlDB = ‘naman’;
        $s = mssql_connect($mssqlHost,$mssqlUser,$mssqlPass) or die(‘Could not connect to SQL Server on ‘.$mssqlHost.’ ‘. mssql_get_last_message());
        echo $s;

        Avidly waiting for your answer.

        Thanks & Regards
        Dipan Chokshi

      • #2826575

        MSSQL PHP CONNECTION

        by easyphp ·

        In reply to Connect to MS SQL 2005 with PHP

        Am ruining this on a local network of about 15 computers of which one computer is the server with MSSQL SERVER 2005, Am writing this application on pc5. That Connection will it work even while connecting from another computer? (the network hostname is kalutech) while connecting should i use the network hostname? like

    • #2603590

      Connecting to SQL Server with PHP – tips

      by ian lewis ·

      In reply to Connect to MS SQL with PHP

      Hi,

      This is what I do here…

      1. Ensure that remote SQL Server has a login username and password defined
      2. that it is set up to receive remote conections via port 3306.
      3. Ensure that SQL Server dual mode is on

      4. Ensure that PHP has the correct version of ntwdblib.dll in the system32 folder on the PHP server.
      5. Connect directly to the server with the following standard code. Embed it in a class with some extra functions later if you like.

      Don’t see the point of going via an ODBC connection myself, I’m sure there is one.

      $host = ‘mars.co.uk’;
      $username = ‘mars_user1’;
      $password = ‘StrongPassword’;
      $database = ‘project1’;

      $conn = mssql_connect($host,$username,$password) or die (“Error occurred conecting to “.$host);

      mssql_select_db($database, $conn);

      $result = mssql_query( $my_query, $conn);
      $line = mssql_fetch_array($result);
      print_r( $line );
      etc, etc…

      If you can connect to the database then you are well on the way.

      It can take a bit of debugging though as there are a few subtleties to getting it all right. There are plenty of resources to help so keep at it.

      Cheers,
      Ian

Viewing 2 reply threads