Link to home
Start Free TrialLog in
Avatar of lukeinjax
lukeinjax

asked on

Connecting to MS SQL Server via PEAR

I have a connection class that I am trying to use to connect to SQL server on the local network via PEAR. The web code has not changed; however, one of the drives in the SQL server machine died and SQL server had to be reinstalled. Apparently, there is some setting in SQL server that is missing from the new install that was present in the old. I have dbo privs set for the user that I am trying to connect with, but when I execute the web script, it will not connect. There is no record of the attempted connection in the SQL Server logs and there is nothing between the machines that would block the request (both the web server and database server are plugged into the same switch in the server rack).

Below is my PHP code:

File 1 (connection class - ABC.class.php)
<code>
<?
include("DB.php");

class ABC
{
      function db_connect($database)
            {
                  $dsn = array(
                        'phptype'  => 'mssql',
                        'username' => 'TestUser',
                        'password' => 'TestUser',
                        'hostspec' => '192.168.0.153:1433',
                        'database' => $database
                  );

                    $db =& DB::connect($dsn);
                  
                  if (PEAR::isError($db)) {
                        die('Standard Message: ' . $db->getMessage() . '<br>' .
                              'Standard Code: ' . $db->getCode() . '<br>' .
                              'DBMS/User Message: ' . $db->getUserInfo() . '<br>' .
                              'DBMS/Debug Message: ' . $db->getDebugInfo() . '<br>');
                  }
                        
                  return $db;            
            }
}
?>
</code>

File 2 (Script that uses the class)
<code>
<?
include("ABC.class.php");
$abc = new ABC;
?>

<html>
<body>

<?
     $abc->db_connect("TestDatabase");
?>

</body>
</html>
</code>

The output from the script is:

Standard Message: DB Error: connect failed
Standard Code: -24
DBMS/User Message: [DB Error: connect failed] ** Array
DBMS/Debug Message: [DB Error: connect failed] ** Array

Again, I believe this to be an issue with SQL server because the PHP code has not changed, but if there is anything else that I can do in my PHP code to get more descriptive error messages out of SQL Server, please make any suggestions.

Avatar of m1tk4
m1tk4
Flag of United States of America image
do a

die(print_r($db,true))

instead of

die('Standard Message: ' . $db->getMessage() . '<br>' .
                         'Standard Code: ' . $db->getCode() . '<br>' .
                         'DBMS/User Message: ' . $db->getUserInfo() . '<br>' .
                         'DBMS/Debug Message: ' . $db->getDebugInfo() . '<br>');

Avatar of lukeinjax
lukeinjax

ASKER

m1tk4,

I originally had the "die(print_r($db,true))" in there, but it didn't seem to give much useful info. Below is its output. Please let me know if you can get anything out of it...

DB_Error Object ( [error_message_prefix] => [mode] => 1 [level] => 1024 [code] => -24 [message] => DB Error: connect failed [userinfo] => [DB Error: connect failed] ** Array [backtrace] => Array ( [0] => Array ( [file] => C:\php5\PEAR\DB.php [line] => 888 [function] => PEAR_Error [class] => PEAR_Error [type] => ->  [args] => Array ( [0] => DB Error: connect failed [1] => -24 [2] => 1 [3] => 1024 [4] => [DB Error: connect failed] ) ) [1] => Array ( [file] => C:\php5\PEAR\PEAR.php [line] => 540 [function] => DB_Error [class] => DB_Error [type] => ->  [args] => Array ( [0] => -24 [1] => 1 [2] => 1024 [3] => [DB Error: connect failed] ) ) [2] => Array ( [file] => C:\php5\PEAR\DB\common.php [line] => 1849 [function] => raiseError [class] => PEAR [type] => ->  [args] => Array ( [0] => [1] => -24 [2] => [3] => [4] => [DB Error: connect failed] [5] => DB_Error [6] => 1 ) ) [3] => Array ( [file] => C:\php5\PEAR\DB\mssql.php [line] => 205 [function] => raiseError [class] => DB_common [type] => ->  [args] => Array ( [0] => -24 [1] => [2] => [3] => [4] => ) ) [4] => Array ( [file] => C:\php5\PEAR\DB.php [line] => 556 [function] => connect [class] => DB_mssql [type] => ->  [args] => Array ( [0] => Array ( [phptype] => mssql [dbsyntax] => mssql [username] => TestUser [password] => TestUser [protocol] => [hostspec] => 192.168.0.153:1433 [port] => [socket] => [database] => TestDatabase ) [1] => ) ) [5] => Array ( [file] => C:\www\html\Test\ABC.class.php [line] => 23 [function] => connect [class] => DB [type] => ->  [args] => Array ( [0] => Array ( [phptype] => mssql [username] => TestUser [password] => TestUser [hostspec] => 192.168.0.153:1433 [database] => TestDatabase ) ) ) [6] => Array ( [file] => C:\www\html\Test\test_db.php [line] => 26 [function] => db_connect [class] => ABC [type] => ->  [args] => Array ( [0] => TestDatabase ) ) ) [callback] => )
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image
Does the server use SQL Authentication or Windows Authentication of both?
Avatar of lukeinjax
lukeinjax

ASKER

TestUser uses SQL authentication only.
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image
You can be quick and dirty and add this line of code to the mssql.php file in the PEAR/DB directory.

Look for ...

        if (!$this->connection) {
            return $this->raiseError(DB_ERROR_CONNECT_FAILED,
                                     null, null, null,
                                     @mssql_get_last_message());
        }


and replace with ...

        if (!$this->connection) {

var_export(mssql_get_last_message());

            return $this->raiseError(DB_ERROR_CONNECT_FAILED,
                                     null, null, null,
                                     @mssql_get_last_message());
        }


Sure, its a nasty quick fix, but gets the REAL error a LOT quicker.

DON'T FORGET TO REMOVE THAT LINE ONCE YOU'VE GOT A RESPONSE!!!
Avatar of lukeinjax
lukeinjax

ASKER

RQuading,

I tried your suggestion, but all it gave me was a set of empty quotes ('') at the beginning of my previous output. What were you hoping that this would return?
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image
I was expecting the proper error. As you got nothing, I am not sure how/why the system things you got an error.
Avatar of lukeinjax
lukeinjax

ASKER

Any other suggestions? I really need to get this figured out...
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image
Ok. Another debug. Can you revert to the original file and remove the @ symbols in mssql.php

Does this produce the same output?

OOI. You ARE supplying a correct username and password? Case sensitive password?
Avatar of lukeinjax
lukeinjax

ASKER

RQuading,

Thanks for your suggestions.

I removed the @ symbols from the connect function and received:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: 192.168.0.153:1433 in C:\php5\PEAR\DB\mssql.php on line 201

Still a very vague message... I'm stumped...
Avatar of lukeinjax
lukeinjax

ASKER

Yes, the username and password are correct. I can connect via odbc with no issue using the same username/password.
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image
In the ODBC setup, what connection mechanism is being used?

TCPIP
Named Pipes
Multiprotocol
...


Is the server named or IP'd?

Is the "Dynamically determine port" checked or not?

Avatar of lukeinjax
lukeinjax

ASKER

I figured out the issue with the server that I originally inquired about... kind of. I ended up creating a domain account for Apache to start with that had the necessary access to SQL Server and set the mssql.secure_connection = on in my php.ini. This way I can connect via NT authentication. I also removed the reference to the port since there is only one (un-named) instance on this machine.

My problem now is connecting to another server that uses a named SQL instance. I have tried specifying a port number in the DSN array as the port key value, by putting in in the hostspec with a comma (192.168.0.125,1433), a colon (192.168.0.125:1433), and as an instance name (192.168.0.125\MYSERVER), but none of these work. Does anyone have any experience with connecting to a named SQL Server instance?
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image
Try

192.168.0.125\\MYSERVER (most likely to work as \ has to be escaped in windows).

or

192.168.0.125/MYSERVER (Maybe PHP is clever enough to realise what is meant here).
Avatar of lukeinjax
lukeinjax

ASKER

RE: above comment - I am experiencing the exact same issue as I was before:

Standard Message: DB Error: connect failed
Standard Code: -24
DBMS/User Message: [DB Error: connect failed] ** Array
DBMS/Debug Message: [DB Error: connect failed] ** Array

I have tried all of the same suggestions from above, but cannot connect.
ASKER CERTIFIED SOLUTION
Avatar of Richard Quadling
Richard Quadling
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lukeinjax
lukeinjax

ASKER

I ended up using ODBC instead of PEAR. Got tired of fighting it. Points go to RQuading for all the suggestions. Thanks.