Connecting to SQL Server is one task I have to do regularly when I work on Rails applications for clients that use Microsoft technologies. I usually set my machine up and don’t really worry too much about how it all comes together.
Recently I had to do the installation completely from scratch. I followed Ken Collins’ comprehensive walkthrough, but I ran into a problem – I wanted to use RVM. Ken’s tutorial uses MacPorts to install the Ruby ODBC bindings, and I wasn’t using Ruby via MacPorts.
Everything out there wasn’t working for me even when I followed Ken’s article on RVM.
Here’s what ended up working for me.
First, I used MacPorts. Then I installed the ports I needed:
sudo port install unixodbc sudo port install freetds +odbc
/opt/local/etc/freetds/freetds.conf to look like this:
[my_dev_server] host = 192.168.1.58 port = 1433 tds version = 8.0
Then I modified
/opt/local/odbcinst.ini to point to my FreeTDS configuration:
[FreeTDS] Decscription = FreeTDS driver for SQLServer Driver = /opt/local/lib/libtdsodbc.so Setup = /opt/local/lib/libtdsodbc.so FileUsage = 1
Finally I modified
/opt/local/odbc.ini and created my ODBC DSN.
[my_dev_server_dsn] Driver=FreeTDS Description=My Server Connection Servername=my_dev_server Server=my_dev_server Port=1433 Database=killer_app
Note that the servername matches the servername defined in the FreeTDS configuration file.
Then I went and installed the gems I needed for my project
gem install ruby-odbc gem install dbi -v=0.4.1 gem install dbd-odbc -v=0.2.4 gem install activerecord-sqlserver-adapter -v=2.3.9
But when I ran my Rails application, attempts to connect to models failed.
ODBC::Error: IM002 (0) [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded
It’s using iODBC which comes with OSX. It wasn’t using UnixODBC at all!
To fix that, I had to reinstall the ruby-odbc gem and instruct it to use the unixodbc path. This is what ultimately fixed things for me:
gem install ruby-odbc -- --with-odbc-dir=/opt/local
After that, everything worked again!