Ruby on Rails
HowtoConnectToMicrosoftSQLServerFromRailsOnLinux

This document was built in hopes to make getting SQL Server talking to Rails on Linux as painless as possible. It can be overwhelming if you dont know exactly what you need, so I’ve tried to lay it out. Feel free to add anything you feel was not explained in great enough detail.

New: added info for activerecord-odbc method.
New: LinuxServersThatSupportMSSQL

There are 4 variables i have captialized which should be self explanatory, but if not:

Replace these in the places they show up below.

Assumed:

Required:

NOTICE you need to install unixODBC BEFORE FreeTDS. The first clue you have to do this is in the ./configure step of FreeTDS: ./configure —with-unixodbc=/usr/local

First things first

Become root

Edit you profile

Use ~/.bashrc for your local profile or /etc/profile to set this for all users.


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

Without this entries your DSN drivers will not be seen.
Also if you’re loged in as user other then root make sure you can access odbc and freetds.conf.

install unixODBC:

Install this via apt-get


apt-get install unixodbc

and definitely do this: ( some headers needed for freeTDS to compile )


apt-get install unixodbc-dev

On Ubuntu make sure you also install tdsodbc as the library libtdsodbc.so is not included in the package unixODBC.

OR: you can install unixodbc manually:


# cd /usr/local/src
# wget <a href="http://www.unixodbc.org/unixODBC-2.2.11.tar.gz">http://www.unixodbc.org/unixODBC-2.2.11.tar.gz</a>
# gunzip unixODBC*.tar.gz
# tar xvf unixODBC*.tar
# cd unixODBC*
# ./configure
# make
# make install


# ./configure --prefix=/usr/local -sysconfdir=/etc --disable-gui # Seems to work better.
(Avoids QT header problems)

install FreeTDS:

You can also get FreeTDS from apt (athough you will not get tsql, but you can get “sqsh” from apt and it works the same way, why compile?)



    
	
  1. apt-get install freetds-dev

OR: do it manually:



    
	
  1. wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
    2. 
	
  2. tar zvxf freetds-stable.tgz
    3. 
	
  3. cd freetds*/
    4. 
	
  4. ./configure  —with-unixodbc=/usr/local
    5. 
	
  5. make
    6. 
	
  6. make install
    7. 
	
  7. vi /etc/freetds/freetds.conf

Red Hat Edit Line 4



    
	
  1. ./configure —with-odbc=/usr/local/lib

NOTE: (RHEL4 – possibly others) You need to have unixODBC-devel (up2date unixODBC-devel) installed in order to use —with-unixodbc. Otherwise libtdsodbc.so will be missing, the above “Red Hat Edit Line 4” did not work for me (it built, but no libtdsodbc.so)

configure FreeTDS:
Add a section to /etc/freetds/freetds.conf:


[YOUR_DB_DEFINITION_NAME]
        host = 192.168.1.101  (change this to the right one for you)
        port = 1433
        tds version = 8.0   #(might be different, check "here":http://www.freetds.org/userguide/choosingtdsprotocol.htm.)
                            #8.0 is the minimum for some important features of SQL server

test FreeTDS:

  1. tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD

or if you are using sqsh…



    
	
  1. sqsh -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD

You should see and do the following to make sure all went well:


locale is "en_US.ISO-8859-15"
locale charset is "ISO-8859-15"
1> use YOUR_ACTUAL_DB_NAME
2> go
1> select * from users   (do this for a table that exists in your db)
2> go
id      login
1       user1


3       user2   (make sure you get correct output)

1> quit

If everything went well, you can go back in to freetds.conf and clean it up if you wish, then move on…

Create DB Definition

Edit: /etc/odbc.ini:


[YOUR_DB_DEFINITION_NAME]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = YOUR_DB_DEFINITION_NAME
Database        = YOUR_ACTUAL_DB_NAME

Edit: /etc/odbcinst.ini:

sudo vim /etc/odbcinst.ini

To look like:

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

Depending on your OS/distro, your unixODBC configuration files may be in a different location. On Gentoo (and others?) they are located in /etc/unixODBC/.

Try it out:



    
	
  1. isql YOUR_DB_DEFINITION_NAME USERNAME PASSWORD
    
———————————————————-
    
| Connected!                            |
    
|                                       |
    
| sql-statement                         |
    
| help [tablename]                      |
    
| quit                                  |
    
|                                       |
    
———————————————————-
    
SQL> select * from users;    (again, do this for a table that exists in your db)
    
—————————————————————-+
    
| id         | login                         |
    
—————————————————————-+
    
| 1          | user1                         |
    
| 3          | user2                         |
    
—————————————————————-+
    
2 rows affected
    
2 rows returned
    
SQL> quit

NOTE: If you have problems connecting read my notice at the end of this page

NOTE: For Ubuntu this tidbit helped me out. Basically, if you do the test above with isql and you get “[ISQL]ERROR: Could not SQLPrepare”, don’t worry about it. It will still work in Rails. — OpenOcean 4-29-2008

If all went well, move on…

Ruby ODBC:

Before building ruby-odbc, make sure that you have odbc-devel installed as this is a requirement to build ruby-odbc. I used yum to install odbc-devel.
If you are on ubuntu, and get an ‘sql.h’ file not found error, make sure you have installed ‘unixodbc-dev’.

Also, note that if you are running Ubuntu “Breezey Badger,” you probably don’t have make installed, and won’t be able to run the code below. Install make by running:

sudo apt-get install build-essential

[David L]

Get and build ruby odbc(updated to 9995 – David Lyons):

  1. cd /usr/local/src
  2. wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9995.tar.gz
  3. tar zvxf ruby-odbc-0.9995.tar.gz
  4. cd ruby-odbc-0.9995/
  5. ruby extconf.rb
  6. make
  7. make install

Ruby DBI ODBC driver:

As far as I can tell, the ruby-dbi-all package is depricated. It is still available, and so you might be able to get this step to work still. On rails 2.1 + ubuntu gutsy I was not able to get it to work. See immediatly below for the new method, which is tested working on rails 2.1.
Ruby-dbi has changed its package structure AFAICS, and now ruby-dbi and dbd-odbc need to be installed seperatly. However, I was not able to get dbi 0.4.0 and dbd-odbc 0.2.2 to work together with rails… kept getting a “Wrong number of arguments” error.
[David Lyons]

THE OLD WAY


# cd /usr/local/src
# wget <a href="http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz">http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz</a>
# tar zvxf ruby-dbi-all-0.0.23.tar.gz 
# cd ruby-dbi-all
# ruby setup.rb config --with=dbi,dbd_odbc
# ruby setup.rb setup
# ruby setup.rb install

Test everything – THE OLD WAY:

Heres a quick way to see if ruby can talk to SQL Server provided by Mark Imbriaco:


# irb1.8
irb(main):001:0> require "dbi"
=> true
irb(main):004:0> dbh = DBI.connect('dbi:ODBC:YOUR_DB_DEFINITION_NAME', 'USERNAME', 'PASSWORD')
=> #<DBI::DatabaseHandle:0xb7d28688 @trace_output=#<IO:0xb7d79064>, @trace_mode=2, 
    @handle=#<DBI::DBD::ODBC::Database:0xb7d28480 @attr={}, 
    @handle=#<ODBC::Database:0xb7d284a8>>>
irb(main):005:0> quit

If everything went well, go make it work in Rails:

Setup Rails – THE OLD WAY:

database.yml:


development:
  adapter: sqlserver
  mode: odbc
  dsn: YOUR_DB_DEFINITION_NAME
  username: USERNAME
  password: PASSWORD

Add other sections in the same manner.

ActiveRecord ODBC adaptor – THE NEW WAY:

Tim Haynes has written a nice gem that provide ODBC support for rails. To get it going, all you need to do is:

sudo gem install activerecord-odbc-adapter

Tah duh!

Now setup your database.yml like this:


development:
  adapter: odbc
  dsn: YOUR_DB_DEFINITION_NAME
  username: USERNAME
  password: PASSWORD

Add other sections in the same manner.

SQL Server working with Rails on Linux!

Great thanks to Mark Imbriaco for his work on adding ODBC support, and the others involved in the SQL Server updates.

Troubleshooting

If you get an error along the lines of ‘[iODBC] [Driver Manager]Specified driver could not be loaded’, it might help to set the Driver value in /etc/odbc.ini to the path of libtdsodbc.so.
—Thom Lawrence

NOTICE about unixODBC configuration: I downloaded the tarball and compiled on \SuSE 9.2. The odbc.ini and odbcinst.ini files in /etc/ got ignored. If the user had no ~/.odbc.ini and ~/.odbcinst.ini files they were created automatically (being empty they led to connection failure). The right way to do it (for me) was to put the ini files in /usr/local/etc, then link them from the home directory of the user. Please make sure that you use the leading dots for the file names in the home directory.
—Created 07-03-2005 by Joe Noon

The previous problem seems to be due to the fact the default configuration of the unixODBC source looks in /etc/unixODBC/ and not /etc/ for the unixODBC configuration files. If the note after the configuration section is enough to correct this problem, then these two notes should probably go away.
—llasram (2006-04-05)

My odbc and \FreeTd files were not accessable/readable by user in neither /etc nor /usr/local/etc After setting appropriate permsions everything started to work smoothly.
— GregoryBluvshteyn (2006-6-21)

In order to get this to work on a RHEL4 box, the ruby-odbc driver was looking for headers that did not exist. I installed unixODBC-2.2.8-2.3.0.2.i386.rpm and unixODBC-devel-2.2.8-2.3.0.2.i386.rpm in order to compile ruby-odbc. This configuration worked fine with SQL Server 2005. Note that mssql2k5 uses TDS 8.0, the same as sql2k.
— Jeremie Blais (2006-7-18)

My ActiveRecord objects where not being loaded with valid ids. Checking SELECT statements with tsql, I saw the row counts as correct, but some recordsets where blank. I re-compiled FreeTDS with:

# ./configure --with-unixodbc=/usr/local/lib --with-tdsver=8.0 --with-msdblib

.

Downloaded ruby-odbc 0.9991. Installed with (also ran the tests…):



    
	
  1. ruby extconf.rb —with-dlopen
    2. 
	
  2. make
    3. 
	
  3. ruby test.rb DSN [uid] [pwd]
    4. 
	
  4. make install

tsql and my Rails app started to show good data. RHEL4 (x86_64), Apache2, and MS SqlServer 2000.

—TerryLorber (2006-09-01)

NOTE:
In freetds-0.63 at least the freetds.conf needs to be in /usr/local/etc/freetds.conf NOT /etc/freetds/freetds.conf:

To compile freetds in UBUNTU (in order to have tsql) apart from unixodbc the package unixodbc-dev is also necessary.

— Eduardo Aldaz (2006-10-11)

When using Debian packages (unstable amd64) I was able to get nearly everything working without compiling up to where the ruby libs needed to be compiled, I did this and it worked fine. There are issues with the default packages where I was receiving this message when trying to use dbi: [RubyODBC]Cannot allocate SQLHENV
apt-get install libiodbc2

— Adam Meehan (2006-11-07)

On Ubuntu you need tdsodbc package as well.

Try installing ‘libiodbc2’ package. I got the same error and this fix it.
— Juan Matias Repetti (2007-05-16)

— John Tjanaka (2007-01-08) \m/(^o^)\m/
Note for Gentoo users:
Remember to USE mssql and odbc, because … that is the whole point ^_^;;;



    
	
  1. echo “dev-db/freetds mssql odbc” >> /etc/portage/package.use
    2. 
	
  2. echo “dev-ruby/ruby-dbi odbc” >> /etc/portage/package.use
    3. 
	
  3. echo “dev-ruby/ruby-odbc ~x86” >> /etc/portage/package.keywords
    4. 
	
  4. emerge -av freetds
    5. 
	
  5. emerge -av ruby-dbi
    6. 
	
  6. emerge -av ruby-odbc
I believe unixodbc comes with freetds already.
freetds.conf is in /etc.
The odbc.ini and odbcinst.ini is in /etc/unixODBC, so you must remember to:



   export ODBCINI=/etc/unixODBC/odbc.ini
   export ODBCSYSINI=/etc/unixODBC

While the libtdsodbc.so and libtdsS.so is in /usr/lib, so in your odbcinst.ini:

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/libtdsodbc.so
Setup           = /usr/lib/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

Tips, it’s possible to connect to SQL Server through SSH Tunneling (Port Forwarding):



    
	
  1. ssh -L localhost_port:sqlserver_ip_or_name:1433 username@gateway_ip_or_name

Need to add a line in /etc/ssh/ssh_config to keep connection alive:

ServerAliveInterval 300

Assuming your sqlserver port is using 1433.
In your freetds.conf:

[YOUR_CONNECTION_DEFINITION_NAME]
        host = localhost
        port = localhost_port    # port you used above.
        tds version = 8.0       # assuming you're using SQL Server 200x.
It’s kinda slow though …

SQL Server 2005 and Ubuntu
SQL Server 2005 requires FreeTDS 0.64 (see http://lists.ibiblio.org/pipermail/freetds/2006q4/020738.html)
Ubuntu comes with FreeTDS 0.63 so you can’t apt-get it, you have to install it manually

category: Howto

* Has anyone been able to use this with MSSQl Sevrer 2005 on x86_64 ? * Yes it works for me just fine, as long as you remember to tel Sqlserver to allow untrusted connections.

Writing UTF-8 to MS Sql Server
I have been able to write to the Sql Server with the freetds and unixodbc setup as above, but when I tried to write chinese words in UTF-8 it became a row of ??.

The same thing applies to data writes in php, but a solution exists in the form of adding a N before the data in the sql query, for example

insert into mytable values(N’unicodedata’)

The N prefix ensures that Sql Server treats the data as unicode – based on information on other forums.

Does any solution exist for ROR, since we are not working directly with SQL queries? Will using the win32ole setup prevent this problem? But my impression is that win32ole only exist on windows based systems.

Could not SQLConnect

If you get these errors when using the isql-command:
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

Try to change the server = YOUR_DB_DEFINITION_NAME
to:
server = IP_OF_SERVER
in odbc.ini. Then try to issue the isql-command again.

——

Had a lot of trouble connecting via ruby even though there was no problem with sqsh etc. Kept giving me this output:
DBI::DatabaseError: 01000 (0) [unixODBC][Driver Manager]Can't open lib '/usr/lib/libtdsodbc.so' : /usr/lib/libtdsodbc.so: cannot open shared object file: No such file or directory from /usr/local/lib/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in `connect' from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:584:in `connect' from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:384:in `connect' from (irb):2

Finally found out that the linked file in /usr/lib was missing. One can check by executing this:
ls --context /usr/lib/libtdsodbc.so

Found the solution here:
https://bugs.launchpad.net/ubuntu/source/freetds/bug/68239

Basically, install the tdsodbc package, delete the symbolic link /usr/lib/libtdsodbc.so, then re-link with /usr/lib/odbc/libtdsodbc.so and re-test.

—Red

——-

On my debian etch box ruby-odbc segfault with unixodbc, windows 2003 server and SQL Server 2005.
I have switched to iodbc:


aptitude install  libiodbc2-dev iodbc

compile freetds from sources (0.64)


./configure --with-tdsver=8.0 --with-iodbc
make
make install

freetds.conf is in /usr/local/etc/freetds.conf

now everything works like a charm

Furthermore, I write my database.yml like this:




development:
  adapter: sqlserver
  mode: odbc
  dsn: "ODBC;Driver={FreeTDS};Server=my_server;Database=my_base;Uid=my_login;Pwd=my_password;Language=English"

thus it’s not needed to bother with odbc.ini, and on MS-Windows I just need to put “SQL Native Client” instead of “FreeTDS” (I think we can also have something more dynamic using an environment variable for this)

“Language=English” resolves problems with datetime format on a French MS-Windows Server

—Laurent

——
I’ve noticed some funny behaviour of tsql during installation/test. If I try the following line in my ubuntu machine:




#tsql -H database_server_ip -p port -U user

I got an error message “unexpected EOF from server” but it works perfectly when using the same parameters in the freetds.config file, calling


#tsql -S YOUR_DB_DEFINITION_NAME -U user

don’t ask me Why.This has made me lose more than an hour, hope it helps anyone.

—Carlos
—-

I needed this line added to my freetds.conf in order to get Spanish characters to work:




client charset = UTF-8


I had to link the tdsodbc library to a different location on FreeBSD for this to work: 



ln -s /usr/local/lib/libtdsodbc.so /usr/lib/odbc/libtdsodbc.so

See also: http://www.freetds.org/userguide/localization.htm

I got the “unexpected EOF from server” until I linked a proper libiconv library. FreeTDS apparently had a problem converting back and forth between iso-8859-1 and my local character set (roman8)

Updated on November 18, 2008 13:07 by Henning (88.88.5.160)
Edit | Back in time (135 older versions)