Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine

Lexicon has asked for the wisdom of the Perl Monks concerning the following question:

This one seems to be a doozy. Yet, so basic. I try to connect from my ActivePerl using Win32::ODBC to the SQL Server 7 Box. Problem Number 1 is that I'm an American in Japan, so all the documentation is in Japanese, so I can't just play with the server to make it work. I receive:

Couldn't open database: [1326] [] "[Microsoft][ODBC SQL Server Driver]
+クライアントの接続が確立できません。" stopped at E:\homepage0828\cgi-
+bin\DBCheck.cgi line 7.
[download]

This should translate to:

[State =01000][Error=1326][Microsoft][ODBC SQL Server Driver]
[SQL Server][dbnmpntw] ConnectionOpen (CreateFile()) 

Error 1326 means "Logon failure: unknown user name or bad password."
[download]

I've had the Japanese Sysadmin look at the database 3 times now and he says all the user permission are correct on SQL Svr. The ODBC is set up on WebSvr. It is listed as a System Resource. The Sysadmin says the error I get is incorrect. I have to wonder...

So, what am I missing? Anyone else have problems like this? Where do I go wave my magic wand and hang the dead chickens? ;)

Comment on Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine Select or Download Code

Replies are listed 'Best First'.
Re: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by Fastolfe (Vicar) on Oct 26, 2000 at 06:31 UTC
If you're unsure about the error message ODBC is giving you (or, rather, the message SQL Server is sending via ODBC to DBI), have your DBA examine SQL Server's error logs (or whatever equivalent service it offers) to determine the nature of the failure. In addition, if you're getting error messages in Unicode, perhaps SQL Server is expecting data in Unicode format? That certainly seems odd, since I'd expect something like ODBC to be independent of such things, but it's worth looking into I guess.	[reply]
RE: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by PsychoSpunk (Hermit) on Oct 26, 2000 at 10:47 UTC
This made me think of something. What's your connection string look like? I've had an experience where the default database was not the same one that I was attempting to connect to and I had to specify the database name. In DBI speak that looked like: `DBI->connect('dbi:ODBC:DSN;server=DBNAME', 'user', 'pass', {RaiseError => 1, AutoCommit => 1, LongTruncOk => 1});` ALL HAIL BRAK!!!	[reply] [d/l]
RE: RE: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by Lexicon (Chaplain) on Oct 26, 2000 at 12:43 UTC
Here's the connection string: `$dbh = new Win32::ODBC("DSN=spinfire_markup;UID=spinfire_markup2;PWD=d +dc;") or die "<p>Couldn't open database: " . Win32::ODBC::Error() . " stoppe +d";` [download] It seems to be a lost cause. I could figure it out if the server were in English and I could tinker with it, but as it is, oh well. I'm gonna look into doing it in ASP now, which means <:sniffsniff> my short tenure in the Perl community may be comming to a close for now. Or more likely I'm just going to go find an excuse to keep playing with perl. "Aw mom! I'm about to gain a level! I can't quit now!"	[reply] [d/l]
RE: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by JanneVee (Friar) on Oct 26, 2000 at 10:36 UTC
I experienced a similar problem with a Win32 application on a Win 2000. The solution included no Magicwands and hanging of the chickens. I just tried to configure it and when I was supposed to select a Database I selected another in the selectionbox and selected the correct one after. Oddly enough it worked. Can't explain why(maybe some magic)... But I hope it does the same for you! But notice I did on a Win2K! /JanneVee	[reply]
Re: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by gregorovius (Friar) on Oct 27, 2000 at 02:10 UTC
I have a very similar setting but I use DBD::ODBC instead. Do you define an ODBC source in the control panel of your web server box? This is my connect line: `$dbh = DBI->connect('DBI:ODBC:odbcSourceName', 'username', 'password') + \|\| die $dbh->errstr;` [download] I would recommend that you switch to DBI. Installing DBI and DBD::ODBC is a snap from ppm. Using DBI instead of Win32::ODBC has the advantage of being more portable.	[reply] [d/l]
RE: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by little (Curate) on Oct 26, 2000 at 17:18 UTC
Could it be possible thatv your user accountr is not valid from a remote host? you might wan't to consider usage of DBI::Win32ODBC and DBI::Proxy Have a nice day All decision is left to your taste	[reply]
Re: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by wardk (Deacon) on Oct 26, 2000 at 20:09 UTC
I would strongly suggest you not use DSN's and instead form a complete connect string for the database. the connect string would be just like one used in ASP. So if you feel more comfortable in ASP, you can get it connected there (sans DSN) and then use the connect string in Perl. Don't give up! Perl, ODBC and SQL Server CAN play together. and it beats the hell out of ASP.	[reply]
RE: Re: Win32::ODBC / IIS -- M$ SQL Server 7 on separate machine by Lexicon (Chaplain) on Oct 27, 2000 at 05:56 UTC
Well, the project deadline was today, so we actually just decided to leave it as the text database it was originally. Depending on management, I may get to come back to it later (hopefully!). I tried originally to do it with DBI::ODBC, but it failed as well. Which is why I moved over to the Win32::ODBC. I still suspect the problems to be on the database level, meaning that it will fail with ASP too. If I get the opportunity to work on it some more, I'll go sit in front of the database myself for a while and translate everything myself! ;) PsychoSpunk just installed HTML::Mason on our server, so I can get better Perl practice with a more cooperative MySQL database.	[reply]