On Thursday, June 28, 2012 11:50:50 AM UTC-6, "Martin J. Evans" wrote:
> On 28/06/2012 17:07, Shrenuj Bansal wrote:
> > I am using a perl script to try to connect to SQL Server on Windows. I
> > believe I have DBI v1.607 and DBD::ODBC v1.25. I am able to connect to the
> > server when I set up a data source using the ODBC Data Source Administrator
> > on Windows. However, when I try to set up the data source myself in the
> > perl script with the same credentials I get an error. I was hoping someone
> > could help me out with this.
> >
> > Here's my code:
> >
> >
> > use DBI;
> >
> > # DBD::ODBC
> >
> > my $dsn = 'DBI:ODBC:Driver={SQL Server}';
> > my $host =
> > my $database =
> > my $user =
> > my $auth =
> >
> > # Connect via DBD:ODBC by specifying the DSN dynamically
> > my $dbh = DBI->connect("$dsn;Server=$host;Database=$database",
> > $user,
> > $auth,
> > { RaiseError => 1, AutoCommit
> > => 1}
> > ) || die "Database connection
> > not made: $DBI:errstr";
> >
> > # Prepare a SQL statement
> >
> > my $sql = "SELECT BundleVersionLocation FROM valdb.dbo.VDB_BundleVersions
> > WHERE BundleVersionID = 20";
> > my $sth = $dbh->prepare( $sql );
> >
> > # Execute the statement
> > $sth->execute();
> >
> > my($BundleVersionID);
> >
> > # Bind the results to the local variables
> > $sth->bind_columns( undef, \$BundleVersionID );
> >
> > # Retrieve values from the result set
> > while( $sth->fetch() ) {
> > print "$BundleVersionID\n";
> > }
> >
> > # Close the connection
> > $sth->finish();
> > $dbh->disconnect();
> >
> > Here is the error I receive when I try to run the above script:
> >
> > DBI connect('Driver={SQL
> > Server};Server=ecmdrvdev-dbsql;Database=valdb','shrenuj
> > b',...) failed: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed
> > for
> > user 'shrenujb'. (SQL-28000) at test.pl line 13
> >
> You did not specify a UID/PWD in the above Perl and yet the error
> indicates you did specify a username!
> If you want to use a trusted connection you'll need to add that
> attribute to the connection string.
>
> You can use dbi:ODBC:DSN=mydsn to connect in Perl where mydsn is the
> data source you created in the ODBC administrator and then once you
> connect print out odbc_out_connect_string
> (http://search.cpan.org/~mjevans/DBD-ODBC-1.37/ODBC.pm#odbc_out_connect_string)
>
> and that will be the connection string you need to use to reconnect
> without a DSN once you replace DSN with DRIVER=xxx (you get this string
> from the ODBC administrator driver tab and enclose it in {} if it
> contains spaces).
>
> e.g. (on windows so quotes different),
>
> C:\Users\martin>perl -le "use DBI;my $h =
> DBI->connect('dbi:ODBC:DSN=asus2','xx'
> ,'yy'); print $h->{odbc_out_connect_string};"
> DSN=asus2;UID=xx;PWD=yy;WSID=ASUS2;DATABASE=master;Network=DBMSSOCN;Address=ASUS2\SQLEXPRESS,1433
>
> Replace DSN=asus2 with DRIVER={SQL Server} (or SQLNative Client or SQL
> Server Native Client 10.0 depending on what the Drivers tab says).
>
> NOTE: If you put the db username/password in the connection string via
> UID=xx;PWD=yy the username/password passed to DBI's connect method are
> ignored - I think (and I say I think because you are using a pretty old
> DBD::ODBC).
>
> Martin
Sorry I forgot to specify that the host, database, user, and auth fields are
assigned values. I just removed those since I did not want to display those in
a public forum.
