Re: Connecting to SQL Server on Windows

On Thursday, June 28, 2012 3:26:57 PM UTC-6, "Martin J. Evans" wrote:
> 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

So I wrote the above script with the host, database, user and auth fields filled in correctly. When I ran the script, it gave me the error shown saying the login failed. I next went to the ODBC Data Source Administrator - System DSN tab and created a new DSN. I named it "sql_server_dsn" and for the question "Which SQL Server do you want to connect to" I entered the same name as in the $host field. For the question "How should SQL Server verify the authenticity of the login ID?" I chose "With Windows NT authentication using the network login ID". I let the rest of the options in the configuration remain as they are. I replaced the 
DBI->connect("$dsn;Server=$host;Database=$database", $user, $auth, { RaiseError => 1, AutoCommit => 1}  line in the script with

DBI->connect("dbi:ODBC:sql_server_dsn", $user, $auth, { RaiseError => 1, AutoCommit => 1}
and then ran the script. It worked and gave me the correct result to the query.
In the user and auth fields I had put my windows username and password for both cases. I need to have this working so that I can just create the DSN in the perl script and not have to create it using the ODBC Data Source Administrator. I am not sure if there is something wrong with my format for the DBI->connect line or there are login problems. Any help would be greatly appreciated. Thanks