"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?

Vu

Did you finally fixed your bug? I'm getting the exact same result. – Pinxit

Try to replace the SQL Server's instance name (localhost\SQLEXPRESS) with (local)\SQLEXPRESS OR .\SQLEXPRESS. Steps to troubleshoot SQL connectivity issues – Vehicle

I know this is old but is there any fix for this? I am trying to connect to a local instans of SQLEXPRESS and I can't. I could connect to a instance (not EXPRESS) in a remote machine, but I couldn't make it work on my local instance. Any tips? – Pedersen

Anyone please respond, did this get fixed..? always getting client.active? is false. – Cubital

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.

Rockery

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!

Thorn

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.

Veneaux

Answer 4

On Windows, you need:

First, you should be have all permision on sql server, do this with Microsoft SQL Server manager studio.

Second, with Sql Server Configuration Manager, go to SQL SERVER network configuration and enable protocols of your INSTANCE, thats are Pipeline with name and TCP/IP, those be enabled, once do that, you should go to SQL SERVER Services and start 1. SQL Server (instance) and 2. Sql Server Browser explorer (important)

on YAML: (example on windows)

development:
  adapter: sqlserver
  database: GESTIONESDIVERSASDESARROLLO
  username: Admin1\Admin
  password: passw0rd
  dataserver: ADMIN1\SQLDEVELOPER
  timeout: 10

Keim