Problems connecting to MSSQL (ODBC?)

[unosoft]

I made a site using PHP, on my development server. But on the live server, I have problems getting a database connection.

 

Error:

Connection could not be established.

Array ( [0] => Array ( [0] => IM002

[sqlSTATE] => IM002 [1] => 0

 => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) )

I have set up ODBC, and testet the connection sucessfully!

My ODBC is configured like this:
Microsoft SQL Server ODBC Driver Version 03.86.3959

Data Source Name: Zeus
Data Source Description: 
Server: Zeus
Database: (Default)
Language: (Default)
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Integrated Security: No
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


I'm using this PHP code:
<?php
function DB_Conn_Zeus ($dbname, $tsql)
    {
        $serverName        = "10.0.0.10\Zeus,1001"; 
        $uid             = "xxx"; 
        $pwd             = "xxxxxx";
        
        $connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>$dbname);
        
        $conn = sqlsrv_connect( $serverName, $connectionInfo);
        
        if( $conn )
        {
            //echo "Connection established.<br />";
        }
......

Can anyone see where the problem is?

I can ping the MSSQL server, and classic ASP seems to work just fine.

[Brian Swan]

I think you have an old version of the ODBC driver installed (the SQLSRV extension needs a newer version). You can download x86 or x86 versions from this page in the SQLSRV documentation: http://msdn.microsoft.com/en-us/library/cc296170(SQL.90).aspx (Look where it says Microsoft SQL Server 2008 R2 Native Client.)

[unosoft]

Ok, i downloaded the package, but ODBC is still the same version. Do i need top upgrade it seperatly somehow?

[unosoft]

Hmm just checked the ODBC version on the other server.. It is actuallyu older! The dev server is using ODBC driver 03.86.1830

[Doug G]

Have you checked the sql server logs to verify your server is listening on port 1001?  And I'm not sure you should put the sql server name after the IP

 

And since port 1001 is a non-standard sql server port you may have some firewall problems, or there may be something you need to configure in the odbc drivers.

 

[unosoft]

But the access is fine, when testing the ODBC connection, and furthermore, i can access the database using ASP.

 

I have tried both "10.0.0.10", "10.0.0.10:1001" and "Zeus" - same error over and over again.

[Brian Swan]

I don't know if the port number is the problem. The error you are showing in your first post tells me that the correct version of ODBC is not installed on the same machine that is running PHP. Have you installed the latest ODBC on that machine?

 

If the port number is the issue, try this:

 

$serverName        = "Zeus,1001";

 

The way you were doing it, it looked like Zeus was an instance name (not sure that's your set up).

 

More info here: http://msdn.microsoft.com/en-us/library/cc761422(SQL.90).aspx

 

-Brian

[unosoft]

I installed  (MDAC) 2.8 - does that include the latest ODBC?

The problem persists, after installing MDAC.

[unosoft]

I can connect to the database in classic ASP, using this connection (same server, different name):

I tried this info in PHP, didn't work either - same error.

 

<%

Set oConNews = Server.CreateObject("ADODB.Connection")

oConNews.ConnectionString = "Provider=SQLOLEDB;Server=WebSQL;UID=xxx;PWD=xxx;Database=dbname"

oConNews.Open

%>

[unosoft]

I solved this problem, by using odbc_connect() instead of sqlsrv_connect()..

Have no idea why sqlsrv failed, but as long as I have a connection, I’m happy!

odbc_connect() has problems returning results from some of my stored procedures, but i can live with that for now.

 

- Tank you for your time!