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:
If 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:
Pay 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.
Marc Clifton 
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?
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.
Marc