Thursday, October 21, 2010

Getting Rails 3 Up on Windows Connecting to SQL Server

Our web group is starting a few test projects on Ruby on Rails for some of our tools.  Our group works in the windows environment with both client/server and ASP.NET MVC apps.  This morning I went through the process of getting two machines set up to do Rails development and I thought I’d write up a quick post to pass along what I learned.  First off I want to say it wasn’t as painful as I thought.  Here is what I did to get rails 3 up and running against SQLServer 2005 and 2008.  My environment is windows 7 64 bit.  I also installed it on Windows 7 32 bit.  For the purposes of this post I will walk you through my install on the 64 bit OS.
I am assuming you have a clean (no ruby installed) machine and a new/clean database to work with.  If you already have ruby and some gems installed you can skip some of the steps.  However, you must have the RubyInstaller version of the interpreter installed for this to work.  With that, here are the steps to running rails 3 on Windows and SQL Server!

Step 0 – Setting up a DSN

For 32 bit Windows:  Go to Control Panel > System And Security > Administrative Tools > Data Sources (ODBC) and create a System DSN (I’m not sure if It has to be a System DSN but that’s what I did).  For the purposes of this blog post I choose the With Windows Integrated Authentication option for authentication.
For 64 bit Windows:  First and foremost, if you setup your DSN following the 32 bit approach you will not be able to connect to the database with rails.  You will receive an error like this: 
ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an
architecture mismatch between the Driver and Application
The above error is due to the fact that you have set up a DSN with the 64 bit version of the Data Sources applet.  After a little quick Google search I found this fix on Stack Overflow.  Basically, you have to run the version at c:\windows\sysWOW64\odbcad32.exe to setup the DSN. After the DSN configuration lets move on to install ruby and all the necessary gems.

Step 1 – Installing Ruby
The first thing you need to do is install ruby using the RubyInstaller which can be found at (download link for 1.9.2).  This method of installing ruby is required or you will not be able to do step 2. 

Step 2 – Install the RubyInstaller DevKit

After you have installed ruby the next step is to download the devkit from from the here.  Follow these instructions explicitly and you will be ready to start installing the database related gems.

Step 3 – Installing the Database Related Gems

Open a command prompt if you do not already have one open and run the following commands:
  1. gem install ruby-odbc 
  2. gem install activerecord-sqlserver-adapter

Step 4 – Install Rails and Create a Test Application

Once you have the DB gems installed the next step is to install rails itself:
  • gem install rails

When the install is complete change and/or create a directory where you want to create the test application.  Once you are in the directory where you want the application run the command:

  • rails new rails_on_win

The above command creates a new application I’m not going to get into that here you can find out more about the architecture of a rails app here.

Step 5 – Configuring the Database Connection (config\database.yml)

Next, change into the rails_on_win,directory or whatever name you used to create the application in step 4.  Open the config\database.yml file in your favorite editor.  Change the development section to be similar to this: image

Change the DSN setting to whatever you called your DSN in step 0.  If you set up the default database in DSN or the DB user has one set you can get rid of the database setting.  if we were using a SQL Server account for authentication we would need to add username and password settings to the connection information.  Since we chose the integrated authentication we do not need to worry about it.  When you are done save and close the file.

Step 6 – Configuring and Installing the Gems in our App

Now that we have our database configuration complete the last thing we need to do before firing up the app is to update our Gemfile.  It can be found in the root directory of the app.  Open up your editor and load the Gemfile.  We need to add two entries:

  • gem ‘ruby-odbc’
  • gem ‘activerecord-sqlserver-adapter’

I added them after the gem ‘rails’, ‘3.0.1’ entry I’m not sure if order matters I’m just working under the assumption that it does.  After adding the two gems if you do not have Sqlite3 installed you will need to comment out the line: gem ‘sqlite3-ruby’.  Save and close the file.  We are now ready to fire up our app!

Step 7 – Starting the Application and Checking the Settings

In the application’s root directory run the command:

rails server 

It takes a little bit for the development server to start up but when it does you should see output similar to this:


Now we are ready to hit the web site.  Start up a browser and enter http://localhost:3000 you should see a page like this  (I’m just showing the top portion of the page):

If you see this page you are halfway there.  Next, click on the ‘About your application’s environment’ link.  You should see information in a yellow box that lists the configuration settings for your application.  The setting I want to point out is the database section towards the bottom of the page.  You should see:
If there is a setting that is incorrect or some other problem instead of the detailed setting description you will see an error message.  I ran into the one I mentioned in step 0 when I used the 64 bit DSN configuration applet. I received this error message when I misspelled the name of my DSN:

IM002 (0) [Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified

Step 8 – Create the Database

If you saw the page with all the settings information then you ready to get started!  At the same command prompt run:

rake db:create

After the successful completion of that command you should see a table called schema_migrations in your SQL Server database.

That’s It!

Now you are ready to start building rails apps in a Microsoft environment.


The two sources below in addition to the site  made this process extremely easy.  I had heard many people say it was hard to get rails going on windows.  I may run into that when I move out of the development phase but for now, it isn’t that bad. 


  1. Awesome write up, worked perfectly. Kuddos for doing the footwork!

  2. How did you manage to install Development Kid ? I run ruby dk.rb init and it finds my install dir, then i run ruby dk.rb install and it looks it installed but when I try to install anything I get an error that required build tools are not installed

  3. I just followed these directions and it I was able to get it to work.

  4. I'm programming rails on linux, and trying to connect to sql server via a connection string...

    I found this code the the database.yml
    adapter: jdbc
    username: rubydev
    password: developer
    url: jdbc:sqlserver://;databaseName=dev_my_

    However I think this only work for jruby. What would I change the driver/adapter to if I wanted to connect directly?

    (I'm a .net developer converting to the darkside - so excuse me if I'm using wrong terminology)

  5. @mikeglanz - I too am a .NET dev during the day so I'm not exactly sure what you need to do but this blog post looks like it may help you: let me know how it goes.

  6. The devkit install is not working for me. I have to include the C:\devkit\bin;C:\devkit\mingw\bin into my PATH evn manually. Then things will start working out...

  7. Excellent instructions. worked like charm. Thank you!!

  8. This comment has been removed by the author.

  9. This comment has been removed by the author.

  10. This comment has been removed by the author.

  11. This comment has been removed by the author.