I’m currently using Ruby on Rails 3.1 and am developing on a Mac. A requirement was to use MS SQL Server as the database. There are a couple hoops to jump through to get RoR to work with SQL Server. Fortunately, the Tiny TDS gem made this a breeze to setup without having to go through the headache of unixodbc/iodbc/rubyodbc configuration.
Here’s the following steps to accomplish:
1. Install FreeTDS using brew
brew install freetds
2. Add the following gems to the Gemfile
gem 'activerecord-sqlserver-adapter'
gem 'tiny_tds'
3. Run bundle install to update the gems
4. Modify the database.yml file to the following:
development:
adapter: sqlserver
mode: dblib
dataserver: <IP Address>\<Instance Name>
database: database_name
username: sql_user
password: sql_password
Things of note:
1. Additional ways to configure the connection string can be found here
2. If you’re using a named instance of SQL, it’s not going to use the default 1433 port. Through SQL Management Studio, you can run the following command to find out what port your instance is running on:
use master
go
xp_readerrorlog
Troubleshooting
If this doesn’t initially work, there are a couple potential points of failure. Here are some of the debugging techniques I used to verify I could connect to SQL Server without rails:
1. Verify that you can make a connection to SQL Server. From terminal prompt, do the following:
telnet <IP Address> <port>
If you connect, you'll receive the following:
Trying 192.168.31.128...
Connected to 192.168.31.128.
Escape character is '^]'.
2. Once you’ve verified that you can connect using telnet, second step is to verify that you can connect via FreeTDS. From the terminal prompt, do the following:
tSQL –H <IP Address> –p <Port> –U sa -P password
If connected properly, you should see the following prompt:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
At this point, this is a SQL editor so you could use SQL syntax to extract queries. For our purposes, we just want to verify that we can connect this way.
Type exit to exit out of application
3. I also ran into an issue when connecting using Rails I received the following error “Adaptive Server timeout”. I resolved this by doing another bundle install and it has worked after that.
Happy Developing!
