Re: Connecting to SQL Server on Windows

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