"Closed connection error" while trying to connect Ruby to SQL server

Question

This is the code I'm using to connect to SQL server 2012 express. My file's name is Connect.rb.

require "rubygems"
require "tiny_tds"
client = TinyTds::Client.new(
                    :username => 'sa',
                    :password => 'sapassword',
                    :dataserver => 'localhost\SQLEXPRESS',
                    :database => 'ContactsDB')
result = client.execute("SELECT * FROM [Contacts]") 

When I run the code, I'm getting the following error:

in 'execute' :closed connection (TinyTds::Error) from Connect.rb: in 'main'

when I replace the above code by the following,

client = TinyTds::Client.new(
                    :username => 'sa',
                    :password => 'sapassword',
                    :host => 'localhost',
                    :port => 1433,
                    :database => 'ContactsDB')

I get the following error:

in 'connect': Unable to connect: Adaptive server is unavailable or does not exist

What's causing this error and how to fix it?

Answer 1

Looks like the config is ok. dataserver is the correct symbol to define a non default instance.

Make sure that TCP/IP and Named pipes protocols is enabled (it's disabled by default on SQL Express). Also enable SQL Server Browser service is running (disabled by default).

You can find these in the Sql Server Configuration Manager in the start menu under Microsoft SQL Server/Configuration Tools. Be sure to enable them in both the 'Client Protocols' and on each of the listed instances.

Additionally, make sure that your firewall allows connections on the port SQL is listening on (default is 1433).

No need to specify the port since Tiny-TDS defaults to 1433. Your second code snippet doesn't include an instance. If you have SQL Express setup on an instance then you need to use dataserver, not host, and specify the instance name.

Answer 2

I had this exact problem and finally figured it out. I know this is old but I hope it might help people in the future.

Go into Sql Server Configuration Manager (Start >> Microsoft SQL Server >> Configuration Tools) and turn on TCP/IP and Named Pipes. In the network configuration, right click on TCP/IP, go to Properties, then IP Addresses. You need to enable the connection you want (I'm using a VM, so I used the IPv4 address one), as well as blank out TCP Dynamic Ports and specify a static port (I use 1433).

Then, you need to allow incoming traffic to port 1433 (or whatever your static port is) through your firewall.

I did this, and finally got in!

Answer 3

Try adding the port number (even if it's the default of 1433) to your config's dataserver value. I had a setup where I was tunneling through a traffic manager appliance to reach a SQL Server on a remote network, and TinyTDS would not connect unless I specifically set my config like:

dataserver: 192.168.1.1:1433\SQL1

Setting the port: value in the config did nothing in my case. It's strange that this step was needed since 1433 is the default anyway, and none of my other SQL Server connection configs needed the port to be specified, but adding it is what worked for me in this particular case.

You can watch your FreeTDS log file to get a closer look at why your connection is failing by running export TDSDUMP=/tmp/freetds.log then firing up irb to test your connection with TinyTDS while tailing that log file.