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

Getting an error trying to connect PHP to MSSQL Server 2000 - "Data source name not found"

Hello,

I am trying to setup PHP to connect to MSSQL Server 2000 on Windows Server 2000 IIS, but we're having a few problems.

Firstly, we get this error :

Warning: odbc_connect() [function.odbc-connect]: SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect in C:\Inetpub\wwwroot\db2.php on line 4
Connection Failed:

This is using this test code :

<html>
<body>
<?php
$conn=odbc_connect('northwind','user','password');
if (!$conn)
{
exit("Connection Failed: " . $conn);
}
$sql="SELECT * FROM customers";
$rs=odbc_exec($conn,$sql);
if (!$rs)
{
exit("Error in SQL");
}
echo "<table><tr>";
echo "<th>Companyname</th>";
echo "<th>Contactname</th></tr>";
while (odbc_fetch_row($rs))
{
$compname=odbc_result($rs,"CompanyName");
$conname=odbc_result($rs,"ContactName");
echo "<tr><td>$compname</td>";
echo "<td>$conname</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>
</body>
</html>


The other problem is that sometimes PHP takes a break, and I'm unable to access any PHP pages.  After I go to the root website(no PHP) and try again, it works fine.  What could this be?
0
civic86
Asked:
civic86
1 Solution
 
peyoxCommented:
I would suggest you to use dedicated functions for MS SQL Server:

http://us4.php.net/manual/en/ref.mssql.php

They works fine (for sure - faster than odbc)
0
 
peyoxCommented:
When using ODBC, make sure that the ODBC-link is a System DSN (not a User DSN).
0
 
MichaelSFullerCommented:
Did you create the dsn on the web server? For this connection? $conn=odbc_connect('northwind','user','password'); If not that is part of the problem also are the sql drivers installed
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.

 
peyoxCommented:
Your DSN should be called "northwind" (if not yet created)
0
 
civic86Author Commented:
Interesting

I removed the User DSN, and now I need a password to access the root level of my website.  I then renamed the Server DSN to Northwind, and im getting this error :

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'customers'., SQL state S0002 in SQLExecDirect in C:\Inetpub\wwwroot\db2.php on line 10
Error in SQL

Any ideas?
0
 
civic86Author Commented:
I now get this on the root website :


Please try the following:

Click the Refresh button to try again with different credentials.
If you believe you should be able to view this directory or page, please contact the Web site administrator by using the e-mail address or phone number listed on the www.home page.
HTTP 401.3 - Access denied by ACL on resource
Internet Information Services

I didnt know ODBC Linked in with IIS?
0
 
peyoxCommented:
SQL Server is case sensitive by default - maybe u should use "Customers" (?)
0
 
civic86Author Commented:
Good idea - unfortunately didn't work :|

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Customers'., SQL state S0002 in SQLExecDirect in C:\Inetpub\wwwroot\db2.php on line 10
Error in SQL
0
 
peyoxCommented:
Your script works fine on my computer:

I created system DSN (on SQL Server Driver):
Datasource name: Northwind
Description - blank
Servername - localhost
Database name - Northwind

I'm running SQL Server 2000 locally.

Connection string used:
$conn=odbc_connect('Northwind','...user...','...pass...');

I didn't change the rest of your script and... it worked!
0
 
civic86Author Commented:
Damn.

Do you have anything on the User DSN Section?

You're running PHP and IIS?

:(
0
 
peyoxCommented:
PHP on Apache (win xp)
I added DSN in System section only (and it should be there).

Also keep in mind:
$conn=odbc_connect('northwind','user','password');

the first parameter is DSN name, not a DB name! You configured DB name in ODBC source.
In ODBC configuration you will find something like "Test connection". Does it connect successfully?
0
 
peyoxCommented:
Also try mssql function which I mentioned before. They work great.
http://us4.php.net/manual/en/ref.mssql.php
0
 
civic86Author Commented:
peyox, I'm sorry I dont understand how to.  I have tested this string of code :

<html>
<body>
<?
$db = new COM("ADODB.Connection");
$dsn = "DRIVER={SQL Server}; SERVER={wjpsrv1};UID={Windows NT User Name with Access};PWD={password}; DATABASE={Northwind}";
$db->Open($dsn);
$rs = $db->Execute("SELECT * FROM table");

while (!$rs->EOF)
{
   echo $rs->Fields['column']->Value."<BR>";
   $rs->MoveNext();
}
?>

</html>
</body>




I am very new at this, so more detailed instructions would be really appreciated.

Also peyox, upon doing "Test Connection", it says it connects successfully.  Argh.  See results below :


Microsoft SQL Server ODBC Driver Version 03.81.9042

Data Source Name: Northwind
Data Source Description:
Server: (local)
Database: Northwind
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: Yes
Use Regional Settings: No
Prepared Statements Option: Drop temporary procedures on disconnect
Use Failover Server: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Data Encryption: No
0
 
peyoxCommented:
try this code (use your credentials), you should see first record from Customers.

<html>
<body>
<?php

   $mssql_server= "127.0.0.1";
   $mssql_user = "<username>";
   $mssql_password = "<password>";
   $mssql_db = "Northwind";

   if ($mssql_link = mssql_connect($mssql_server, $mssql_user, $mssql_password))
      echo "Connected<br>";

   if (mssql_select_db ($mssql_db))
      echo "Database selected<br>";

   if ($answer = mssql_query("select * from Customers",$mssql_link))
      echo "Query ok<br>";

   $arr = mssql_fetch_array($answer);

   echo "Query Result: <B>".$arr['ContactName']."</B><br>";

   if (mssql_close($mssql_link))
        echo "Connection closed<br><br>";
?>

</body>
</html>
0
 
civic86Author Commented:
Brilliant ! I now have it working - but I am not quite sure how to best set security.  I am now getting this error :

I think I need to add permissions for the directory/MSSQL/IIS, but I'm not quite sure how.  Is there a best practice for this?

The error now is :

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'MSSQLSVR\IUSR_MYSRV'., SQL state 28000 in SQLConnect in C:\Inetpub\wwwroot\db2.php on line 9

This is the last piece of the puzzle!
0
 
peyoxCommented:
I found this document. It describes - how to set password for 'sa' user. You can use these instruction, to create a new user ie. "dbuser" and then set the password.
Then update these lines:

   $mssql_user = "<username>";
   $mssql_password = "<password>";

good luck!
0
 
peyoxCommented:
0
 
nmcdermaidCommented:
Your PHP code is being run under the this windows user: MSSQLSVR\IUSR_MYSRV

It looks like you are trying to connect to the SQL Server as a windows user. This could happen for two reasons:

1. SQL Server is set up for Windows Login only
2. Your connection method has asked for a windows login only.



I wouldn't recommend using the sa user as your web login. It makes it far too easy for any web monkey to compromise your server. (ie delete databases, delete data, steal data)
0
 
civic86Author Commented:
Resolved, working beautifully.  Thank you very much !
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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