Saturday, 23 June 2007

Connect to Microsoft SQL Server from Debian lenny GNU/Linux

« Virtual machines | Main | Photos »

Life is unjust and one can't always use open source technologies ;-) .

There are many guides about how to do that but they usually require to compile the drivers from source. Considering I'm using Debian lenny, I'd prefer a solution compatible with its package system, Debian has more than 18000 packages, there should be those I need! In fact it's so, first step is to install these packages:

# apt-get install tdsodbc libiodbc2 unixodbc odbcinst1debian1
[...]
#
  • tdsodbcs is the package with the precompiled FreeTDS drivers,
  • odbcinst1debian1 contains the utility we'll use to configure the drivers,

Second step is to inform the system that now we have the FreeTDS drivers:

# odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini
[...]

This adds in /etc/odbcinst.ini the following section:

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

Third step is to configure your ODBC Data Source Names:

$ odbcinst -i -s -r
[YourDsn]
Description     = Your description
Driver          = FreeTDS
Database        = CatalogName
Server          = 192.160.1.10
Port            = 1433
odbcinst: Sections and Entries from stdin have been added to ODBC.INI
$

Note that if you execute the last command as a normal user the section is added to ~/odbc.ini.

That's all, try it:

$ isql YourDsn UID PWD
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from table_name ;
[...]
SQLRowCount returns 4
4 rows fetched
SQL> 
Now, if you want to use the drivers with Ruby and Rails, there are some additional steps.
First I have to install the libdbd-odbc-ruby package too (you system may need more packages ...), then in database.yml:
development:
  adapter: sqlserver
  username: your_user_name
  password: your_password
  dsn: YourDsn
  mode: ODBC

It's all fine except for an annoying problem with table creation: most columns take an unspecified NOT NULL constraint and that causes a lot of troubles. Maybe somebody could suggest a remedy, it would be very well appreciated :-) .

Posted by Nicola Piccinini at 11:21 PM CEST in devel/

Comments on this entry:

Left by Claudio at 24 Jun 3:07 PM

WoW!
Thank you for this useful tips! :)
I really need it :)