Connecting to MSSQL with Ruby on Ubuntu

Posted February 28, 2008 * Comments(24)

I’m working on a problem for a client which involves connecting to a Microsoft SQL Server 2005 database from Linux using Ruby. Here’s what I did to get it working, based off some useful instructions that are tailored for Ruby on Rails:

Firstly, update your ~/.profile to include the following:

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf

Then reload your .profile, by logging out and in again.

Secondly, on Ubuntu 7.10 Server I needed to install some packages.

mlambie@ubuntu:~$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

With FreeTDS installed I could configure it like this:

mlambie@ubuntu:/etc/freetds$ cat freetds.conf
[ACUMENSERVER]
  host = 192.168.0.10
  port = 1433
  tds version = 7.0

The important thing here is ACUMENSERVER, which is the DSN that I’ll use when connecting to the database. The host, and port are self-explanatory, and it’s worth noting that I had to use 7.0 specifically as the tds version.

Testing FreeTDS is not too hard:

mlambie@ubuntu:~$ sqsh -S ACUMENSERVER -U username -P password
sqsh: Symbol `_XmStrings' has different size in shared object, consider re-linking
sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> use acumen
2> go
1> select top 1 firstname, lastname from tblClients
2> go

[record returned]

(1 row affected)
1> quit

Next up it’s necessary to configure ODBC:

mlambie@ubuntu:/etc$ cat odbcinst.ini
[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

mlambie@ubuntu:/etc$ cat odbc.ini
[ACUMENSERVER]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = ACUMENSERVER
Database        = ACUMEN

I then tested the connection with isql:

mlambie@ubuntu:~$ isql -v ACUMENSERVER username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> use ACUMEN
[][unixODBC][FreeTDS][SQL Server]Changed database context to 'Acumen'.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns -1
SQL> select top 1 firstname from tblClients;

[record returned]

SQLRowCount returns 1
1 rows fetched
SQL> quit

Â
OK, so we’ve got ODBC using FreeTDS to connect to a remote MSSQL server. All that’s left is to add Ruby into the mix.

mlambie@ubuntu:~$ sudo aptitude install libdbd-odbc-ruby

The last thing to test is that Ruby can use DBI and ODBC to hit the actual database, and that’s easy to test:

mlambie@ubuntu:~$ irb
irb(main):001:0> require "dbi"
=> true
irb(main):002:0> dbh = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
=> #<DBI::DatabaseHandle:0xb7ac57f8 @handle=#<DBI::DBD::ODBC::Database:0xb7ac5744
@handle=#<ODBC::Database:0xb7ac576c>, @attr={}>, @trace_output=#<IO:0xb7cbff54>,
@trace_mode=2>
irb(main):003:0> quit

And a more complete test (only with SQL SELECT, mind you):

#!/usr/bin/env ruby

require 'dbi'
db = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
select = db.prepare('SELECT TOP 10 firstname FROM tblClients')
select.execute
while rec = select.fetch do
  puts rec.to_s
end
db.disconnect

Â

Â

24 Comments so far

  1. Jason on August 11th, 2008

    I was struggling to get the freetds/odbc/odbcinst configs to work. This was very simple, and I got it done in 5 mins. Thanks!

  2. mlambie on August 11th, 2008

    I’m glad I was able to help out Jason.

  3. Jan Wikholm on August 20th, 2008

    Thanks for this Ubuntu specific version of the installation.
    I would like to point out this issue I had with unixODBC and libtdsodbc.so:

    [01000][unixODBC][Driver Manager]Can’t open lib ‘/usr/lib/odbc/libtdsodbc.so’ : /usr/lib/odbc/libtdsodbc.so: cannot open shared object file: No such file or directory
    [ISQL]ERROR: Could not SQLConnect

    $ ls -la /usr/lib/

    lrwxrwxrwx 1 root root 19 2008-08-20 17:18 libtdsodbc.so -> libtdsodbc.so.0.0.0

    And that was a broken symbolic link, since the referred file is not there.

    I found the solution in a thread of Ubuntu forums via Google:
    http://ubuntuforums.org/showthread.php?t=433435

  4. Sahand on October 21st, 2008

    Matthew, Thank you for this post. It saved me a lot of headache. The missing libtdsodbc caused a bit of headache, but I found this link:
    https://bugs.launchpad.net/ubuntu/ source/freetds/ bug/68239

    Keep up the good work.

  5. Greg Corrigan on December 9th, 2008

    Matthew,

    You should add package ‘tdsodbc’ to your initial apt-get to make sure the libtdsodbc entry does not haunt those following your excellent instructions:

    mlambie@ubuntu:~$ sudo aptitude install tdsodbc

  6. the_0ne on December 31st, 2008

    Wow, great directions. Worked perfectly. I added @Greg Corrigan’s instructions also, to include the tdsodbc library in the initial sudo aptitude install line.

    Thanks…

  7. Will Bellman on March 10th, 2009

    Just to echo, great reference! Just picked up Ruby and have been trying for the last couple days to connect to our MS SQL server. No problems here… Even got ActiveRecord working easily…. just added the:

    dsn: data_source_name
    database: myDatabaseName

    Great job, and thank you!

  8. mlambie on March 11th, 2009

    I’ve added the tdsodbc reference to the original article now.

    I’m glad that helps people out still :)

  9. sara on March 25th, 2009

    Great article.
    The isql -S TEST throws the below error
    CT-LIBRARY error:
    ct_connect(): network packet layer: internal net library error: Net-Library operation terminated due to disconnect

    Instead of DSN name if the the ip address is given, isql -S ip_address
    CT-LIBRARY error:
    ct_connect(): directory service layer: internal directory control layer error: Requested server name not found.

    But its working fine with tsql.

    Thanks…

  10. gabor on March 27th, 2009

    Hi!

    Does it work with WindowsNT authentication as well? I tried the configuration above with my username and password and I get this error message (Ubuntu Intrepid):

    [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
    [ISQL]ERROR: Could not SQLConnect

    Unfortunately based on this message I don’t know if it’s authentication related or other problem.

    Gabor

  11. Misi on April 7th, 2009

    It is a very good guide, I managed to connect to an MS SQL server in 10 mins, even I had to troubleshoot a bit.

    I had to do the following to get it work, in addition to the preparation step above:

    # gem install dbi
    # apt-get install libdbd-odbc-ruby

    Misi

  12. Mike on April 23rd, 2009

    Thanks for this guide. I’ve been banging my head for a week (says something about the thickness of my skull) with this. The bug with libtdsodbc was the cause, but it was rather difficult to diagnose. Following these directions was my salvation.

    Much gratitude!

    Mike

  13. cvielma on May 21st, 2009

    Really thanks man… i need it with python but you resolved my so doom problem about connecting to a mssql server from ubuntu…

    Really really thanks…

    Greetings,

  14. terix on June 9th, 2009

    Thanks a lot! Now I don’t have to load windows each time I need to work with mssql database in our institute.

  15. yetihehe on October 16th, 2009

    Thanks, your article really helped. But I needed other configuration before it started working:

    odbc.ini:
    [MYSERVER]
    Driver = /usr/lib/odbc/libtdsodbc.so
    Description = my server
    Trace = No
    Servername = MYSERVER
    Database = mydb

    [ODBC Data Sources]
    MYSERVER = TDS

    odbcinst.ini:
    [ODBC Drivers]
    TDS = Installed

    [TDS]
    Driver = /usr/lib/odbc/libtdsodbc.so
    Setup = /usr/lib/odbc/libtdsS.so
    FileUsage = 1

    [ODBC Connection Pooling]
    PerfMon = 0
    Retry Wait =

  16. pzol on December 15th, 2009

    I needed to compile libdbd-odbc-ruby from sources, the version included in ubuntu 9.10 karmic koala would give errors:

    wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz
    tar xzvf ruby-odbc-0.9997.tar.gz
    cd ruby-odbc-0.9997
    ruby extconf.rb –with-dlopen
    make
    sudo make install

  17. cyrus on May 4th, 2010

    ubuntu 9.04
    i have this error:
    DBI::DatabaseError: INTERN (0) [RubyODBC]Cannot allocate SQLHENV

  18. ad4m on June 29th, 2010

    Works! Nice job dude ;D

  19. Pavlo on November 19th, 2010

    I am really thankful to You for this post!
    It saved my project not once already!
    In ubutntu 10.10 I got mssql to talk to me with following gems: ruby-odbc, dbi and dbd-odbc

    All the best,
    Pavlo.

  20. Gofrm on March 28th, 2011

    There is a method; http://www.gofrm.com/forums/linux-desktop/51505-linux-uzerinden-ms-sql-servera-baglanmak.html

  21. Dhamodharan N on April 20th, 2011

    Thanks for Your article, its really useful.
    After configuration i tried to connect using Sequel.connect method connection was established but try to fetch data following error arise

    irb(main):035:1> DB.synchronize do |conn|
    irb(main):035:1> end

    Sequel::DatabaseConnectionError: ODBC::Error: IM002 (0) [unixODBC][Driver Manager]Data source name not found, and no default driver specified

    Please give suggestions to resolve this error.

  22. Kirill on April 24th, 2011

    ruby-1.9.2-p136 :002 > db = DBI.connect(‘dbi:ODBC:SQL_SERVER’, ‘uWeb’, ’14032011′)
    DBI::InterfaceError: Unable to load driver ‘ODBC’ (underlying error: uninitialized constant DBI::DBD::ODBC)

    It’s required to intall http://ch-werner.de/rubyodbc/

  23. Rich on June 2nd, 2011

    Thanks for this guide mate, approximately 2,000 hours and 1,000,000 steps quicker than all the other “guides” I found. Only this one got my connection working…!

  24. jrosell on September 28th, 2011

    It fails me in first step. The same as gabor
    [unixODBC][FreeTDS][SQL Server]Unable to connect to data source
    [ISQL]ERROR: Could not SQLConnect

Leave a Reply