Win32:ODBC fails as a CGI

Posted on 2004-11-22
Medium Priority
Last Modified: 2013-12-25
I'm at the end of my rope here -- I wrote a simple script using Win32:ODBC to pull some basic data out of an access DB.  The script runs fine but for a couple of nitpicky warnings from use strict.  Anyway, when I try to run the script through my browser (I'm running IIS 5.1 on an XP Pro workstation to develop the site), I get the error:

Can't load 'C:/Perl/site/lib/auto/Win32/ODBC/ODBC.dll' for module Win32::ODBC: load_file:The specified module could not be found at C:/Perl/lib/DynaLoader.pm line 229.
 at C:\CGI\test2.cgi line 9
Compilation failed in require at C:\CGI\test2.cgi line 9.
BEGIN failed--compilation aborted at C:\CGI\test2.cgi line 9.

I can't find anything that tells me why this is happening or what to do about it.  The account specified in IIS 5.1 for accessing the page and running scripts had read/execute permissions on my entire Perl directory, as well as the necessary dirs for the site.  As I said, the script runs fine on the command line, and the files mentioned in the error are exactly where the should be (I don't know how it could be telling me it can't find a file that's exactly where the error tells me it should be).

I'm still relatively new to this, so I'm trying to get to the bottom of every issue I encounter, but this has stopped me cold.

Here's the first part of the script (before the data processing, which isn't even getting a chance to run, apparently)

#!C:\perl\bin\perl.exe -w

use CGI qw(:standard);
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);

use strict;

use Win32::ODBC;

my $dbh = new Win32::ODBC("GRE");
if (! $dbh)
     {print "Still ****ed up, buddy";}
my @tables = $dbh->TableList();

my $q = CGI->new;
my %FORM;
foreach my $field (param()) {
    $FORM{$field} = param($field);
Question by:Zeek0
  • 5
  • 4

Author Comment

ID: 12648532
AHA!  Apparently when you use Win32::ODBC, you actually have to log in under the account that will be used by the server application to run the script and create the ODBC connection that way.  Unbelieveable.

Accepted Solution

inq123 earned 1500 total points
ID: 12655950
Hi Zeek0,

You found one solution, but the real problem is due to NTFS security.  So when you want to run a CGI program on NT machine with IIS, you need to set two permission settings: 1. IIS settings for script/execute, which you set.  2. NTFS setting for the files the script has to access.  This is a separate setting and can be set through right clicking on the files/folders, choose "Properties"->"Security" and add the user/permission that you need.  This way you don't need to always login as the same user to run your scripts.


Author Comment

ID: 12656441
I had done all of that, but to no avail - I could run the script from the command line just fine, but for whatever reason the ODBC connection failed when I ran it from the web sever.  

http://www.roth.net/perl/odbc/faq/ has something at the very bottom that mentions the need to created the system DSN while logged in as the account which will be accessing it.  All the NTFS permissions don't make a difference (I gave the accounts *more* access than they needed and it still didn't work).
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Expert Comment

ID: 12657321
You're right. Somehow I managed to overlook the ODBC part. :)

Author Comment

ID: 12657420
I'm still a little confused about the permissions, though.  I had the cgi running for awhile (even though it wasn't pulling records from the database as expected), but now I'm getting ACL errors when I try to run it - I created a user account to act as the anonymous login account, gave it read/exec permissions to the web and script directories, as well as the entire Perl directory, gave it rights to access my system from the network, log on as a batch job, and even log on as a service.  I'm not concerned about tight security for the time being because I'm developing everything on one workstation, but it seems to me that the internet user account shouldn't need so much access just to post some info from an HTML form to a CGI scipt . . .  I'm wondering if there's some other secret trick like there was for the System DSN.  What a pain.

Expert Comment

ID: 12658058
Hmm, I never had so much trouble using IIS/PWS on my machine (from NT3.5 to 2000/XP).  I always ran IIS under system adminstrator account, and just set execute/script permissions and everything from perl to C++ cgi all work just fine.

Author Comment

ID: 12658585
I do everything from my domain user account, which has local admin rights.  As I mentioned, I set up a local user to take on the anon user account, then assigned it all the permissions, but I still have trouble.  

I'm almost laughing about it, but now I can't even get my command-line script to access the database properly.  I suspect that my hangups are related more to ODBC issues and problems from the Win XP/IIS 5.1 side than my scripts -- I'm only trying to do very basic things, such as grab a row of data and spit it out.  

I don't have any problems when I comment out all of the Win32::ODBC components of my CGI (i.e. leave a script that reads the form data and spits it back out to the browser).  Unfortunately, everything I find on the net seems to have been designed on older systems and doesn't account for the errors I've encountered.  

When you had things working, how did you authenticate your site?  Did you have anon access with IIS controlling the account?  Is there a particular service/process I should configure to run as the administrator?  

Author Comment

ID: 12659481
I think I finally figured it out (fingers crossed).  First, I switched from the Win32::ODBC module to DBI::DBD::ODBC, and finally managed to get that to work.  First, I had to set up the workgroup admin stuff - all of the DB's I use are in a secured workgroup, so I took this one out and assigned the the rights to all aspects of the DB to the default Access account ('Admin').    That finally allowed DBI to get into the database, then I just had to switch up so that the internet user account had write permissions on the DB file and the directory it was in.  

At any rate, I finally have a CGI script that will read data from an HTML form, open an Access DB, and read from that.  Now I get to have fun linking the form data to the query I want to use for search the database.  

Expert Comment

ID: 12659688

Congratulations!  I only ran non-DB apps on IIS before, and I did not allow anonymous access.  All my DB accessing scripts were on linux side, guess it's a blessing compared to your experience. :)

You should post a question in community support and ask them to close the question and refund your points.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Recently I have been answering a lot of questions like this in IT forums that I frequent. The question posed is usually something along the lines of "We have software X installed and need to uninstall it for reason Y" or some other variant of the sa…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

578 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