• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Unable to connect

Hi,
I don't know why it does to odbc_error, like
C:\Program Files (x86)\PHP\v5.3>php f:\Php3.php
<pre>
C:\Program Files (x86)\PHP\v5.3>

Here are the codes
<?php
$server = "WIN0364-CD34FB";
$database="SQLSERVER2008";
$user="Ite_login";
$password="Ite345678";
$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);

if (odbc_error()) {die("<pre>".print_r(odbc_errormsg($conn), true));}
...

Open in new window

but I can connect to the DB using the same login, via SSMS.
0
HuaMinChen
Asked:
HuaMinChen
  • 10
  • 9
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
You are probably using Windows Authentication in SSMS.  The only way I found to do that with PHP ODBC is to create a 'System DSN' that uses Windows Auth and use it.
$conn = odbc_connect("DSN-Name", '', '');

Open in new window

Otherwise, you will need to create an SQL User and use SQL Authentication in your code above.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks. but using the same account and password, I am able to connect to the DB using Sql authentication.
0
 
Dave BaldwinFixer of ProblemsCommented:
In what program using what methods?  In SSMS or the other program, verify that the server and database names are correct.  That's the only thing I see that might be wrong.  I have several PHP programs using the same 'odbc_connect' format as you show above with SQL Authentication and they work without problems.  

You can set up a System DSN to test the data you are using to see if it connects properly.  That's what I did with both Windows and SQL Auth to test the use of System DSN.  I did find out that thru a web server that 'User DSN' doesn't appear to work.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HuaMinChenBusiness AnalystAuthor Commented:
Yes, I can connect to DB using the login by SS authentication. I can try to set up system DSN on the server, right?
0
 
Dave BaldwinFixer of ProblemsCommented:
Yes, I can connect to DB using the login by SS authentication.
I asked what program and method you are using to do that.  

The System DSN would have to be set up on the client where it would be used.  But if the client is not on the server, then maybe Windows Auth wouldn't work anyway.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Sorry, I can connect to DB by Sql server management studio using Sql server authentication. I am using Windows 2003 server for verifying PHP codes.
0
 
Dave BaldwinFixer of ProblemsCommented:
If you run SSMS on the Windows 2003 server, can you connect from there with those credentials?
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Yes, using the same server name, database, user & password.
0
 
Dave BaldwinFixer of ProblemsCommented:
So your PHP code and your SSMS program are on the same computer?  Where is the SQL Server located?  Is it on a different computer?
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Sql server is in the same machine. thanks
0
 
Dave BaldwinFixer of ProblemsCommented:
Here is a very simple program that works on my machines that you can use to test on your machine.  Just put in your own info the database variables at the top of the program.
<?php 
// MS SQL server and php_odbc.dll driver
// DB configuration
$serverName = "server\SQLEXPRESS";     // Your database server
$dbuser = "Your db username";      // Your db username
$dbpass = "Your db password";      // Your db password
$dbname = "Your database name";      // Your database name
$dbtable = "Your table name";  // the table we're using
//
//$conn = odbc_connect($odbcName, $dbuser, $dbpass) or die ("Could not connect to server");
$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$serverName;Database=$dbname;", $dbuser, $dbpass);

echo "Old Website List - PHP/MS-SQL/ODBC";
echo "\r\n";

// --------- Get data from table ---------------
$dquery = "SELECT TOP 2 * FROM $dbtable";
echo $dquery;
echo "\r\n";
/* Execute the query. */
$result = odbc_exec($conn,$dquery);
if (!$result) {
   echo 'Could not run query: ' . mssql_error();
   exit;
	}
/* display the results */
while (odbc_fetch_row($result)) {
echo odbc_result($result,1);
echo ', '.odbc_result($result,2);
echo ', '.odbc_result($result,3);
echo ', '.odbc_result($result,4);
echo "\r\n";
}

?>

Open in new window

0
 
HuaMinChenBusiness AnalystAuthor Commented:
Sorry I only get this

C:\Program Files (x86)\PHP\v5.3>php f:\Php4.php
Old Website List - PHP/MS-SQL/ODBC
SELECT TOP 2 * FROM t5

C:\Program Files (x86)\PHP\v5.3>

by running this
<?php
// MS SQL server and php_odbc.dll driver
// DB configuration
$serverName = "WIN0364-CD34FB";     // Your database server
$dbuser = "Ite_login";      // Your db username
$dbpass = "Ite345678";      // Your db password
$dbname = "SQLSERVER2008";      // Your database name
$dbtable = "t5";  // the table we're using
//
//$conn = odbc_connect($odbcName, $dbuser, $dbpass) or die ("Could not connect to server");
$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$serverName;Database=$dbname;", $dbuser, $dbpass);

echo "Old Website List - PHP/MS-SQL/ODBC";
echo "\r\n";

// --------- Get data from table ---------------
$dquery = "SELECT TOP 2 * FROM $dbtable";
echo $dquery;
echo "\r\n";
/* Execute the query. */
$result = odbc_exec($conn,$dquery);
if (!$result) {
   echo 'Could not run query: ' . mssql_error();
   exit;
	}
/* display the results */
while (odbc_fetch_row($result)) {
echo odbc_result($result,1);
echo ', '.odbc_result($result,2);
echo ', '.odbc_result($result,3);
echo ', '.odbc_result($result,4);
echo "\r\n";
}

?>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
Let's add some error reporting to it.  Try this.
<?php
error_reporting(E_ALL);
// MS SQL server and php_odbc.dll driver
// DB configuration
$serverName = "WIN0364-CD34FB";     // Your database server
$dbuser = "Ite_login";      // Your db username
$dbpass = "Ite345678";      // Your db password
$dbname = "SQLSERVER2008";      // Your database name
$dbtable = "t5";  // the table we're using
//
//$conn = odbc_connect($odbcName, $dbuser, $dbpass) or die ("Could not connect to server");
$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$serverName;Database=$dbname;", $dbuser, $dbpass);
$errmsg = odbc_errormsg($conn);
echo $errmsg;
echo "\r\n";

echo "PHP/MS-SQL/ODBC";
echo "\r\n";

// --------- Get data from table ---------------
$dquery = "SELECT TOP 2 * FROM $dbtable";
echo $dquery;
echo "\r\n";
/* Execute the query. */
$result = odbc_exec($conn,$dquery);
if (!$result) {
   echo 'Could not run query: ' . odbc_errormsg($conn);
   exit;
	}
/* display the results */
while (odbc_fetch_row($result)) {
echo odbc_result($result,1);
echo ', '.odbc_result($result,2);
echo ', '.odbc_result($result,3);
echo ', '.odbc_result($result,4);
echo "\r\n";
}

?>

Open in new window

0
 
HuaMinChenBusiness AnalystAuthor Commented:
Sorry, I get this

C:\Program Files (x86)\PHP\v5.3>php f:\Php4.php

PHP/MS-SQL/ODBC
SELECT TOP 2 * FROM t5
Could not run query:
C:\Program Files (x86)\PHP\v5.3>

after having run your current codes, while there're 2 records in the table.
0
 
Dave BaldwinFixer of ProblemsCommented:
That's progress.  That means your connection worked but there is something wrong with the query.  That query is so simple that the only possible error is that 't5' is not in that database or it is spelled wrong.  Or for some strange reason you don't have permissions on that table from this connection.

Log in to SSMS and run that query and see what you get.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Yes, using the same account, I can select from the table, using SSMS.
0
 
Dave BaldwinFixer of ProblemsCommented:
Can you run the exact same query in the PHP script above?

SELECT TOP 2 * FROM t5
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks a lot
0
 
Dave BaldwinFixer of ProblemsCommented:
You're welcome.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now