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

Hi,

I've been trying to connect to a database on an MS SQL server from WinNT running ActivePerl. I've added a system datasource that connects to the DB successfully from ODBC Data Source Admin in the control panel (although it doesn't seem to remember my password).

This is the code I've been using :
use strict;
use Win32::OLE;

my $uid = 'myUID';
my $pwd = 'myPwd';
my $dsn = 'myDSN';

my $conn = new Win32::OLE ('ADODB.Connection');

$conn->Open("DSN=$dsn;UID=$uid;PWD=$pwd;") 
    or die "Cannot connect : " . Win32::OLE::LastError();

$conn->Close;

[download]

And all I get is : 
Cannot connect : 0 at adodb.pl line 12

[download]
Anyone got any ideas?
Andrew

Replies are listed 'Best First'.
Re: Connecting to MS SQL using ADODB and ActivePerl
by poj (Abbot) on Feb 04, 2003 at 18:36 UTC
    Try this
    my $conn = new Win32::OLE ('ADODB.Connection')
    or die "Cannot connect : " . Win32::OLE::LastError();

$conn->Open("DSN=$dsn;UID=$uid;PWD=$pwd;");

    [download]
    poj
[reply]
[d/l]
      Hi,

      I tried that before but it doesn't change anything. Interestingly if I exclude the UID and PWD and just use 
      $conn->Open("DSN=$dsn");

      [download]
      I get 
      Cannot connect to MyDSN : OLE exception from "Microsoft OLE DB Provide
+r for ODBC Drivers" :

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '
+(null)'. Reason: Not associated with a trusted SQL Server connection.

Win32::OLE(0.1502) error 0x80004005: "Unspecified error"
    in METHOD/PROPERTYGET "Open" at adodb.pl line 12.

      [download]
      What am I supposed to have when calling the open routine? Are there other parameters that I should/can use?

      I've set up the System DSN with the correct username/password - the ODBC control panel can connect successfully.

      Maybe this is more of a Windows question than a Perl one. Any ideas?

      Andrew

      UPDATE : apparently this is something to do with the server being set up for Windows authentication only, and not Mixed Mode authentication. But why can my ODBC control panel connect OK, but not my Perl script?
[reply]
[d/l]
[select]
        Well, is it necessary to use a DSN connection? With ADO it's possible to use DSN-less connections. Here's a connection string I use regularly (needs MDAC 2.6+).

        my $ConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Se
+curity Info=False;Data Source=hostname;Initial Catalog=Database;";

my $AdoConn = new Win32::OLE("ADODB.Connection");
   $AdoConn->Open($ConnStr);

        [download]

        If you need to specify a user, here is the syntax:

        my $ConnStr = "Provider=SQLOLEDB.1;User Id=user; Password=password; Da
+ta Source=hostname; Initial Catalog=Database;";

        [download]
        --
        The ability to destroy planets is insignificant next to the power of the Deacon Effect.
[reply]
[d/l]
[select]
        What error do you get using the code I suggested ?
        poj
[reply]

Back to Seekers of Perl Wisdom