Re: Connecting to SQL Server on Windows

[Martin J. Evans]

On 28/06/2012 21:03, Shrenuj Bansal wrote:
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.

So given the information you've provided, the username or password are 
wrong, that is what the error message says. You'll have to give me more 
information about what works compared with what does not work if you 
want more help.

Martin