1

I want to connect ms sql database through perl language. The following steps are I made.

  • I've created a database table in the name "SampleDb"
  • Configured ODBC and I've set name for sql server driver as "SampleDb"
  • Now I've tried to connect as follows,

,

my $dbs = "dbi:ODBC:DRIVER={SQL Server};SERVER={SampleDb}";
my $dbh = DBI->connect($dbs, "username", "password");

But Now I got the following error

DBI connect('DRIVER={SQL Server};SERVER={SampleDB}','username',...) failed: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (SQL-08001) [state was 08001 now 01000]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01000)

How to solve this ? or How to Connect ms sql using perl through ODBC ?

Improve this question
0

2 Answers 2

Reset to default
4

This question has already been answered (and accepted !!). Just providing detailed steps for connecting MSSQL server from perl running on Linux using ODBC.

Before you get into perl stuff, you need to install and configure odbc environment on the Linux box.

Install below packages:

Fedora:
    unixODBC-devel.i686 
    unixODBC.i686
    AND
    freetds.i686 
    freetds-devel.i686 
    freetds-doc.i686
Ubuntu:
    unixodbc 
    unixodbc-dev
    AND
    freetds-bin 
    freetds-common  
    freetds-dev
    tdsodbc

After these packages are installed we MUST be able to use freetds to test authentication network authentication with the MS SQL server.

root@ubuntu:/home# tsql -S <db_host_name> -p 1433 -U perluser -P password
locale is "en_IN"
locale charset is "UTF-8"
1>

If above fails, then check the MSSQL db permission for this user. Also check MSSQL logs. Do not proceed until you run above command successfully. Also, failure of above command has nothing to do with the unixodbc OR freetds configuration.

Configure the odbc.ini and odbcinst.ini and freetds.conf files.

/etc/odbc.ini will contain the DSN information:

root@ubuntu:/home# cat /etc/odbc.ini 
[odbc-test]
Description     = test
Driver          = ms-sql
Servername      = ms-sql
Database        = <db_name>
UID             = perluser
Port            = 1433
  • Please note that, the Driver field refers to /etc/odbcinst.ini context named [ms-sql].
  • The Servername field refers to the /etc/freetds.conf context that I also named [ms-sql].
  • Database field is optional but I am using it as I want to connect to a particular Database.

/etc/odbcinst.ini file contains the Driver information:

root@ubuntu:/home# cat /etc/odbcinst.ini 
[ms-sql]
Description     = TDS Conection
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
UsageCount      = 1
FileUsage       = 1
  • The odbcinst.ini file simply directs the odbc.ini file to the appropriate driver.
  • The Driver and Setup entries for Fedora is /usr/lib/libtdsodbc.so and /usr/lib/libtdsS.so respectively.
  • Also, for 64-bit Linux box, it would be Driver64 and Setup64 instead of Driver and Setup respectively.

Configure /etc/freetds/freetds.conf ( /etc/freetds.conf for Fedora). It contains TDS information. Here is mine:

root@ubuntu:/home# tail  /etc/freetds/freetds.conf 
[ms-sql]
        host = <db_host_name>
        port = 1433
        tds version = 7.0
        dump file = /var/log/freetds.log
  • Please note that, above context name [ms-sql] is the value of Servername in odbc.ini.

Test the configuration by connecting the MSSQL server using isql command:

root@ubuntu:/home# isql -v odbc-test perluser password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
  • The odbc-test portion of the isql command was defined in the odbc.ini file.
  • You HAVE to receive above output which says Connected!. If you don’t, then there is some problem in your configuration and go through all the above steps again.

Now,

Then use the ODBC in your perl code as below:

my $dbh = DBI->connect ('dbi:ODBC:odbc-test', 'perluser', 'password');

Hope This Helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@slayebylucifer I tried but it show [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
2

This is for connecting MSSQL using perl in Windows.

Instead of ODBC, try OLEDB. The following code will help you.

my $host = 'sample\sql';
my $database = 'SampleDB';
my $user = 'sa';
my $auth = 'password';

$dsn  = "Provider=sqloledb;Trusted Connection=yes;";
$dsn .= "Server=$host;Database=$database";
my $dbh = DBI->connect("dbi:ADO:$dsn",$user,$auth,{ RaiseError => 1, AutoCommit => 1}) || die "Database connection not made: $DBI::errstr";

Reference How to connect Ms Sql using Perl

Improve this answer

1 Comment

I cannot understand why changing to use DBD::ADO fixes the issue. There is nothing wrong about using DBD::ODBC and the reasons for the error shown can be found at support.microsoft.com/kb/328306

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.