lsxo has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks.. I have recently began using perl and I am having dificulty connecting to MS SQL server. My current code is: 

use strict;
use DBI;


$| = 1;

my $server_name = 'server name';
my $database_name = 'database name;
my $user = 'database user name';
my $password = 'database password';

my $DSN = "DRIVER={SQL Server};Database=$database_name;Server=$server_
+name;uid=$user;pwd=$password; Trusted_Connection=False"; 

my $dbh = DBI->connect("dbi:ODBC:$DSN", $user, $password) || die "Coul
+dn't open database: $DBI::errstr\n";

$dbh->disconnect();

[download]

I am getting a responce of: 

DBI connect(' ') failed: [Microsoft][ODBC SQL Server Driver][SQL Serve
+r]Login failed for user 'company\lsxo'. (SQL-28000) at c:/TEMP/connec
+t2.pl line 14

[download]

Ultimately, my question is how do I get SQL Server authentification using a login ID and password that I enter. Thanks! lsxo

Replies are listed 'Best First'.
Re: connection issues to MS SQL Server
by roboticus (Chancellor) on Aug 07, 2009 at 18:48 UTC
    lsxo:

    If your DB server supports database authentication, remove the "Trusted_Connection=False" bit from your connection string. If it's using windows authentication instead, then remove the "user=...;password..." bit, and pass in undef in the user and password parameters to the DBI->connect() function. That's what I use here ... successfully.

    If you're running on *NIX box, I don't know what you may have to do to make a windows authentication work. But the normal database authentication will work.

    ...roboticus
[reply]
      use strict;
use DBI;


$| = 1;

my $server_name = 'rb_intra_sqldev';
my $database_name = 'RMBSAnalytics';

my $DSN = "DRIVER={SQL Server};Database=$database_name;Server=$server_
+name;uid=datapull;pwd=datapull"; 

my $dbh = DBI->connect("dbi:ODBC:$DSN", undef, undef) || die "Couldn't
+ open database: $DBI::errstr\n";

$dbh->disconnect();

      [download]
      is this what you were refering to? I'm sorry I'm just feeling really lost right now!!
[reply]
[d/l]
        lsxo:

        More like this:

        my $server_name = 'rb_intra_sqldev';
my $database_name = 'RMBSAnalytics';
my $user_name = 'datapull';
my $password = 'datapull';

my $DSN = "DRIVER={SQL Server};Database=$database_name;Server=$server_
+name";

my $dbh = DBI->connect("dbi:ODBC:$DSN", $user_name, $password)
          or die "Couldn't open database: $DBI::errstr\n";

        [download]
        ...roboticus
[reply]
[d/l]
Re: connection issues to MS SQL Server
by bichonfrise74 (Vicar) on Aug 07, 2009 at 19:14 UTC
    I'm not sure if this will help you, but have you tried to do some 'tracing'? It will give you more insights on what is happening. 
    DBI->trace(2);

    [download]
[reply]
[d/l]
Re: connection issues to MS SQL Server
by FloydATC (Deacon) on Aug 07, 2009 at 17:16 UTC
    Since you're asking, I'm assuming your $user does not contain "company/lsxo" as shown in the error message. Are you passing "lsxo" as your user name and the server prepended the domain, or did you send something completely different?

    Do you know if the database instance is using domain-only, sql-only or mixed-mode authentication?

    -- Time flies when you don't know what you're doing
[reply]
      Yes.. sorry about not clarifying! $user is the database username and $password is the database password. I am not passing lsxo as my username but a different username. lsxo is my windows login username. I'm not sure which authentication the database instance is using..how do I find out? Thank you!
[reply]
        First, check the server configuration. You will most likely want to use mixed mode, unless there are special security concerns that dictate otherwise.

        Secondly, check the ODBC data source. I don't have much experience with using DBI against SQL Server, but I do know that incorrect settings in the ODBC data source will usually override whatever the application is trying to do.

        Hope this helps :-)

        -- Time flies when you don't know what you're doing
[reply]
Re: connection issues to MS SQL Server
by gmontema (Initiate) on Aug 07, 2009 at 17:49 UTC
    Maybe this example will help you, the MYSQL server is on the same machine that I am running perl on. 
    use strict;
   use DBI;
   my $db="test";
   my $host="gmontematorlt.sjcorp.focusframe.com";
   my $userid="guest";
   my $passwd="password";
   my $connectionInfo="dbi:mysql:$db;$host";

# make connection to database
   my $dbh = DBI->connect($connectionInfo,$userid,$passwd);
   my $sth = $dbh->prepare ("SELECT name, category FROM animals");
   $sth->execute ();
   print "<?xml version=\"1.0\"?>\n";
   print "<dataset>\n";
   while (my ($name, $category) = $sth->fetchrow_array ())
   {
       print " <row>\n";
       print "  <name>$name</name>\n";
       print "  <category>$category</category>\n";
       print " </row>\n";
   }
   $dbh->disconnect ();
   print "</dataset>\n";

    [download]
[reply]
[d/l]
      This may be a dumb question but what in my code would by similar to your $host. i.e am I missing crucial information?
[reply]

        Nothing necessarily because he is using mysql and you are using SQL Server. In an ODBC data source you select the host. i.e., when you create the ODBC data source via the data source administrator you select your ms sql server at that time. If you use DSN-less connections you'd use the attribute server=xxxx.

[reply]

Back to Seekers of Perl Wisdom