On 28/06/12 23:18, Shrenuj Bansal wrote:
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.
and here you were using SQL Server authentication i.e., you passed a username
and password which is given to SQL Server to verify and it said your
credentials are invalid.
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".
and here you used windows NT authentication so you don't need to pass in a
username and password - in other words you used a trusted connection.
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.
but your DSN said use a trusted connection and as you were already logged in
the username and password passed to connect is irrelevant.
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
It sounds like you want a DSN-less connection. How you do this depends on
whether a) you need to use a trusted connection or b) have a username/password
for SQL Server but in both cases your connection string starts with
dbi:ODBC:DRIVER={xxx} and does not contain DSN.
If (a) you need to add an attribute to the connection string to tell SQL Server
to use a trusted connection. Add Trusted_Connection=True or Integrated
Security=SSPI (kerberos or fallback NTLM) but it depends on which driver you
are using.
If (b) you need to pass valid SQL Server username/password to DBI's connect.
There are loads of sites with example connection strings for SQL Server. Just
make sure you use DRIVER= in uppercase as DBD::ODBC has a problem identifying
the DRIVER in the connection string on unicode builds if it is mixed or lower
case (basically the XS code does not case fold on unicode strings).
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
