尝试将Ruby连接到SQL Server时出现“Closed connection error”

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

这是我用来连接SQL Server 2012 express的代码。我的文件名是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'

在'execute'中：来自Connect.rb的关闭连接（TinyTds :: Error）：在'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

在'connect'中：无法连接：Adaptive Server不可用或不存在

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

是什么导致了这个错误以及如何修复它？

4 个解决方案

#1

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

看起来配置没问题。 dataserver是定义非默认实例的正确符号。

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).

确保启用了TCP / IP和命名管道协议（默认情况下在SQL Express上禁用）。还启用SQL Server Browser服务正在运行（默认情况下禁用）。

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.

您可以在Microsoft SQL Server /配置工具下的开始菜单中的Sql Server配置管理器中找到它们。请务必在“客户端协议”和每个列出的实例中启用它们。

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

此外，请确保您的防火墙允许SQL正在侦听的端口上的连接（默认为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.

无需指定端口，因为Tiny-TDS默认为1433.您的第二个代码段不包含实例。如果在实例上安装了SQL Express，则需要使用dataserver，而不是host，并指定实例名称。

#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).

进入Sql Server配置管理器（开始>> Microsoft SQL Server >>配置工具）并打开TCP / IP和命名管道。在网络配置中，右键单击TCP / IP，转到“属性”，然后转到“IP地址”。您需要启用所需的连接（我正在使用VM，因此我使用了IPv4地址），以及删除TCP动态端口并指定静态端口（我使用1433）。

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

然后，您需要通过防火墙允许传入流量到端口1433（或任何静态端口）。

I did this, and finally got in!

我这样做了，最后进来了！

#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:

尝试将端口号（即使它是默认值1433）添加到配置的dataserver值。我有一个设置，我通过流量管理器设备隧道到达远程网络上的SQL Server，并且除非我专门设置我的配置，否则TinyTDS将无法连接：

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.

设置端口：配置中的值在我的情况下没有做任何事情。奇怪的是，这个步骤是必需的，因为无论如何1433是默认的，并且我的其他SQL Server连接配置都不需要指定端口，但添加它在这种特殊情况下对我有用。

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.

您可以通过运行导出TDSDUMP = / tmp / freetds.log来查看您的FreeTDS日志文件，以便仔细查看连接失败的原因，然后启动irb以测试与TinyTDS的连接，同时拖尾该日志文件。

#4

On Windows, you need:

在Windows上，您需要：

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

首先，您应该在SQL Server上拥有所有权限，使用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)

其次，使用Sql Server配置管理器，转到SQL SERVER网络配置并启用INSTANCE的协议，即具有名称和TCP / IP的管道，这些是启用的，一旦这样做，您应该转到SQL SERVER服务并启动1。 SQL Server（实例）和2. Sql Server Browser explorer（重要）

on YAML: (example on windows)

在YAML :(在Windows上的例子）

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

#1