Solved

Unable to connect

Posted on 2013-11-06
19
230 Views
Last Modified: 2013-11-08
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
Comment
Question by:HuaMinChen
  • 10
  • 9
19 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39629358
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39629393
Thanks. but using the same account and password, I am able to connect to the DB using Sql authentication.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39629408
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:HuaMinChen
ID: 39629458
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39629514
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39629608
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39629645
If you run SSMS on the Windows 2003 server, can you connect from there with those credentials?
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39629652
Yes, using the same server name, database, user & password.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39629664
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39629666
Sql server is in the same machine. thanks
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39631070
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39632740
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
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 145 total points
ID: 39632762
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39632804
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39632844
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39632872
Yes, using the same account, I can select from the table, using SSMS.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39632908
Can you run the exact same query in the PHP script above?

SELECT TOP 2 * FROM t5
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39632927
Thanks a lot
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39634146
You're welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question