Perl script works OK in command prompt, but fails on ODBC as a CGI script

Issue: Perl script works fine in command prompt, but fails on ODBC as a CGI script

Settings: Win XP Pro 64 bit version 2003 SP2 Apache 2.2.17 Win32 Active Perl 5.12.2 (not Perl64) an ODBC DSN is created for SQL SERVER

Details: from command prompt, Phone.pl runs OK - it gets query results as expected - which means DBI works fine

as a CGI script, Phone.pl prints HTML as expected before ODBC init code. (Apache settings work OK for non-ODBC script.)

here's the msg in error.log of Apache

[Tue Nov 23 13:27:57 2010] [error] [client 127.0.0.1] DBI connect('SQLSVR206','administrator',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at C:/CGI/Phone.pl line 32

Any idea why it behaves differently?

#!c:/perl/bin/perl.exe
use CGI qw(:standard);
use strict;
use warnings;
use DBI;

my ($sqlstr, $sql_UNITED, $dbh206);

# 206
# select * from getProvFINON('270762789')#TIN
my @colUNITED = qw/ADR_LN_1_TXT  PROV_SYS_ID  ZIP_CD  FULL_NM  SOURCE  PROV_TIN  BILL_TEL/;

sub Init(){
    $dbh206开发者_Go百科 = DBI->connect ("dbi:ODBC:SQLSVR206",'USER','PASSWORD',{RaiseError=>1})
        or die $DBI::errstr;
    $sqlstr = qq/ select * from getProvFINON(?) /; #parameters TIN
    $sql_UNITED = $dbh206->prepare($sqlstr)
        or die "prepare failed: " . $dbh206->errstr(); 
}

sub test1{
    my $tin = shift();
    $sql_UNITED->execute($tin) or die $sql_UNITED->errstr;
    my $row = $sql_UNITED->fetchrow_hashref;
    while(defined ($row)){
    foreach(@colUNITED){
        print $row->{$_}."~~~";
    }
    print "<p>";
    $row = $sql_UNITED->fetchrow_hashref;
    }
}

print header;
print "test text<p>";
Init();
test1('270762789');
print "999999999999<p>";

========================================================== UPDATE: part of the problem is the 32-bit VS 64-bit ODBC issue see http://support.microsoft.com/kb/942976

HOWEVER, after I set up ODBC, I still got following error in Apache log [Wed Nov 24 01:38:48 2010] [error] [client 127.0.0.1] DBI connect('SQLSVR206-32','',...) failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'MYDOMAIN\GARY$'. (SQL-28000) at C:/CGI/test.pl line 15

I logged in as MYDOMAIN\administrator and started Apache. ODBC was set up to use "With Windows NT authentication using the network login ID" Where does this "MYDOMAIN\GARY$" come from? Thanks!

========================================================== FINAL UPDATE: Apache services runs using "Local System account", and that's where the "MYDOMAIN\GARY$" comes from. The thing is GARY is not with the company and his ID has been dropped from SQL Server, but ODBC code in CGI inherited GARY from Apache, resulting in error 28000. So the problem was a convolution of 1) 32/64 bit ODBC DSN and 2) default account used by Apache

Maybe the ODBC datasource is listed under 'User DSN' and not 'System DSN'. I think that the identity that your web server runs as only has access to System DSN.

In case my post doesn't make sense, I'm editing it in order to add a link to information about the difference between User vs. System DSN's: http://www.truthsolutions.com/sql/odbc/creating_a_new_odbc_dsn.htm

Something is up with Windows 7 ODBC connections that I can't figure out either. I'd set up the ODBC and it would test fine but get a similar error as you show. Even connecting MS SQL Server Mgmt Studio to some SQL servers I have to explicitly put in extra connection settings. Since I only have the issue on some SQL servers (which I don't own) I think it's a server side problem. On PERL i finally gave up and just created the DSN file with said extra connection settings and pointed to it directly. Note how the server name is there twice.

use DBI;
use SQL::Abstract;
my $CONNECT = "FILEDSN=thefile.dsn";
my $dbh = DBI->connect("dbi:ODBC:$CONNECT", "myidsid", "passwordThatsNotUsed");

with the ODBC file looking something like:

[ODBC]
DRIVER=SQL Server Native Client 10.0
UID=myidsid
Address=servername,3180
Network=DBMSSOCN
APP=Microsoft® Windows® Operating System
Trusted_Connection=Yes
SERVER=servername
PWD=someTextInPlaceOfPasswordAsItsNotUsed