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:

MixedModeAuthentication

Enable Remote Connections

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

AllowRemoteConnection

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:

UserMap

Enable Named Pipes and TCP/IP Protocols

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

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.

About these ads
This entry was posted in Journal, Programming, Ruby on Rails. Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s