Solved

Unable to connect

Posted on 2013-11-06
19
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

738 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