Re: Connecting to SQL Server on Windows

[Martin J. Evans]

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