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

Robed ones,
I have been using Win32::ODBC just fine on WinNT4 w/MSSQL 7. My department just got another web server with 2000 (both NT & MSSQL) and suddenly I have to set the Username and Password in my DSN call. I'd much rather just call the DSN connection and have it use the defaults. Here's what I used to do:
use Win32::ODBC
my $DSN = "myDSN";
my $db = new Win32::ODBC($DSN);

[download]
If I try that on the 2k box, I get the following error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Is there a monk who has solved this in any way other than writing:
my $db = new Win32::ODBC("DSN=$DSN;UID=$DSNuserName;PWD=$DSNpassword")
+;

[download]
Note that this is a System DSN and since the second one works great I'm fairly sure my SQL user, etc. are correct. I've been looking towards DBI, but would rather not re-write all these apps right now.

Thanks,
Ardenstone

Replies are listed 'Best First'.
Re: Win32::ODBC vs. Win/MSSQL 2000
by growlf (Pilgrim) on Nov 10, 2001 at 17:17 UTC
    I don't know if this helps much, but I have found that on my Win2K boxes with MSSQL2K I can use DBI with just a DSN quite nicely. You are right though, that might mean some rewriting of code.

    *G*
[reply]
      Thanks growlf,
      That is good to know. Out of curiosity, do you have to pass the username and password, or can you just run it with the UID/PWD set up in the DSN connection? I spent a while bashing my head against this, and if I do have the time to rewrite it, I'd like to do it right (or at least more right :).
      Thanks again,
      Ardenstone
[reply]
        I have done both DSN only (UID and PASSWD in a DSN) and "DSNless" with everything in the connection string. DBI also provides for sepparation of the connection string and the UID/PASSWORD - but i have had troubles using both at the same time.

        Honestly, I think that DBI is a better way to go to begin with - it is more portable for one (and the clients are always changing platforms on me - so that is a plus for me at least!) and as I delve deeper into all the modules that use/support it (like my Fetchall-Answer concerning HTML::Template) I really like some of the things it simplifies becuase of that support from the other mods.

        Update: I use ActiveState Perl heavily in my dev process, and so am tempted to use alot of the Win32 stuff, but every time that I do - it bites me in the butt. Either due to client changes in spec, or inability of the Win32 to work as well period. Hence the fondness of a more portable solution like DBI/DBD.

        *G*
[reply]
Re: Win32::ODBC vs. Win/MSSQL 2000
by 2501 (Pilgrim) on Nov 10, 2001 at 03:50 UTC
    I don't think it is possible. I had the same problem you did and the only conclusion I could come to was that it needs to know how to set permission for individual users.
[reply]

      By individual users do you mean the NT login or the DSN login (which in this case is SQL Authentication). Bah, either way it seems like Win2k has decided always to prompt. I wonder what would happen if I set it to NT Authentication and gave IUS_ServerName read access to database it needs?

      Anyhow, thanks for the feedback.
      Ardenstone

[reply]

Back to Seekers of Perl Wisdom