Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Unable to connect

Posted on 2013-11-06
19
Medium Priority
?
237 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 84

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 11

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 84

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

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 84

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 11

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 84

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 11

Author Comment

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

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 11

Author Comment

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

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 11

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 84

Accepted Solution

by:
Dave Baldwin earned 580 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 11

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 84

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 11

Author Comment

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

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 11

Author Comment

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

Expert Comment

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

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this tutorial viewers will learn how to style elements, such a divs, with a "drop shadow" effect using the CSS box-shadow property Start with a normal styled element, such as a div.: In the element's style, type the box shadow property: "box-shad…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

636 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