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

Question

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 = 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

It would be very useful, if you could post the script in question, or at least the relevant parts. — cyber-guard

regex regex · Accepted Answer · 2010-11-23T22:24:13

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

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

2 Answers