Re: Connecting to SQL Server on Windows

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