"Closed Connection Error" when trying to connect a Ruby server to SQL

Question

This is the code that I use to connect to SQL Server 2012 Express. My file 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 get the following error:

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

when I replace the above code as follows:

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

I get the following error:

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

What causes this error and how to fix it?

Answer 1

It looks like the configuration is ok. dataserver is the correct character to identify an instance that is not used by default.

Make sure TCP / IP and Named pipe are enabled (by default, it is disabled in SQL Express). Also enable the SQL Server Browser service (disabled by default).

They can be found in Sql Server Configuration Manager under the Start menu in Microsoft SQL Server / Configuration Tools. Be sure to include them both in the "Client Protocols" and in each of the listed instances.

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

You do not need to specify a port, because Tiny-TDS defaults to 1433. The second piece of code does not include the instance. If you have SQL Express installed on the instance, 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 can help people in the future.

Go to Sql Server Configuration Manager (Start → Microsoft SQL Server → Configuration Tools) and enable TCP / IP and Named Pipes. In the network configuration, right-click on TCP / IP, select "Properties", then "IP Addresses". You need to enable the desired connection (I use a virtual machine, so I used the IPv4 address alone), as well as empty TCP ports and specify a static port (I use 1433).

Then you need to allow incoming traffic to port 1433 (or any other of your static ports) through your firewall.

I did it and finally got it!

Answer 3

Try adding the port number (even if it defaults to 1433) in the value of your dataserver parameter. I had a setting in which I tunneled through the traffic manager device to access SQL Server on a remote network, and TinyTDS did not connect unless I specifically installed my configurator, for example:

 dataserver: 192.168.1.1:1433\SQL1 

Setting the port: value to config did nothing in my case. It is strange that this step was necessary, since 1433 is the same anyway by default, and none of my other SQL Server connection configurations required a port to be specified, but adding this was for me in this particular case.

You can view the FreeTDS log file to learn more about why your connection fails by running export TDSDUMP=/tmp/freetds.log , then enabling irb to test your connection to TinyTDS when you disconnect this log file.

Answer 4

On Windows, you need to:

First , you must have all permissions on the sql server, do this using the Microsoft SQL Server studio.

Second , with the Sql Server Configuration Manager, go to the SQL SERVER network configuration and enable the protocols of your INSTANCE, it is Pipeline with the name and TCP / IP, which will be enabled, if you do this, you need to access SQL SERVER services and start 1. SQL Server (instance) and 2. Sql Server Browser explorer (important)

in YAML: (example in windows)

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