Connecting to SQL Server Express from Ruby

Something I’ve been interested in is creating a dynamic data viewer for SQL Server in Ruby on Rails.  Here’s the steps I had to take to establishing a connection with SQL Server.  I’m using the AdventureWorks2008 database as the test database, and I should also point out that I’m running Ruby in Windows 7.

SQL Server Configuration

Some initial configuration of SQL Server Express is necessary, depending on how you configured SQL Server Express in your initial installation options.

Enable Mixed Mode Authentication

From SQL Server Management Studio, sign in with Windows Authentication and then right-click on the SQLEXPRESS top-level node, then select properties.  Click on Security    and Enable Mixed Mode Authentication:


Enable Remote Connections

Click on the “Connections” page and check “Allow remote connections to this server”:


Create a User Account

Create a user account under Security -> Logins.  I named my account “ruby” with the password “rubyist”.  On connecting the first with SQL Server Authentication, you will be prompted to change your password, so I changed mine to “rubyist1”.

Set the Server Roles

On the same dialog box, click on the Server Roles page and enable the desired roles.

Associate the User with the Desired Databases

On the same page, click on the Server Roles page and map the user to the desired databases.  For example:


Enable Named Pipes and TCP/IP Protocols

Then, in the Sql Server Configuration Manager, enable the Named Pipes and TCP/IP protocols:


Reboot or Restart the Services

After making all these changes, I found it simpler to just reboot my machine.

Test Your Connection in SQL Server Management Studio

After rebooting / restarting the services, open SQL Server Management Studio and try logging in with Sql Server Authentication:

ConnectIf all the configuration changes went well, you should see the object explorer.  If you continue to have problems, check out this excellent article on dealing with connection issues, which includes steps that I have omitted, such as configuring the firewall, as I’m testing everything locally.

Configure Ruby

Install the tiny_tds gem by opening a command line and typing in:

install gem tiny_tds

Test It!

I use RubyMine for Ruby / Rails development, so we’ll run a few things from the Interactive Ruby Console directly in the RubyMine IDE:

RubyPay particular attention to the client connection and the dataserver value.

For more information on how tiny_tds works and the options that it supports, refer to the tiny_tds GitHub page.


2 thoughts on “Connecting to SQL Server Express from Ruby

  1. Hi Mark:

    Your instructions work perfectly regarding the Ruby MS SQL connection when I set up under Windows 10. But when I attempt the same under Windows 7, I am getting an error message – “Unable to connect: Adaptive Server is unavailable or does not exist”. In your instructions, you are using Win 7 and I do not see any reference to ‘Adaptive Server’. On the Windows 10 platform, I am using Ruby 2.2.4p230 (2015-12-16 revision 53155) [i386-mingw32]. On Win 7, I am using Ruby ruby 2.0.0p643 (2015-02-25) [i386-mingw32]. Would this be the problem?

    1. Hi David,

      I’m not sure what the problem is — particularly since I wrote that example on a W7 box! I was however using Ruby 1.9, so that might be the problem. Sorry I couldn’t be of more help.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s