Solved

Unable to connect

Posted on 2013-11-06
19
225 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 82

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 82

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
 
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 82

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 82

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 82

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Author Comment

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

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 82

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 82

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 82

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 82

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
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.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now