无法远程访问 SQL Server 2008 R2 的实例 [英] Unable to access an instance of SQL Server 2008 R2 remotely

问题描述

出现了一个非常神秘的问题:P

我有一台配置了静态 IP 的服务器.我已经安装了带有附加实例 (ITAPP) 的 SQL Server 2008 R2.现在，当我使用 IP 访问 SQL Server 时，会发生以下情况:

客户端机器:

sqlcmd -S XXX.XXX.XXX.XXX -U sa -P mypass

连接成功....

但是当我使用:

sqlcmd -S XXX.XXX.XXX.XXX\ITAPP -U sa -P mypass

<块引用>

HResult 0xFFFFFFFF，级别 16，状态 1 SQL 网络接口:错误定位服务器/实例指定 [xFFFFFFFF].

Sqlcmd:错误:Microsoft SQL Native Client:发生错误与服务器建立连接时.

连接到 SQL Server 2005 时，此故障可能是由于事实上，在默认设置下 SQL Server 不允许远程连接..

Sqlcmd:错误:Microsoft SQL Native Client:登录超时.

即使在使用 SQL Server Management Studio 的同一台机器上(安装了 SQL Server).我在两台机器上都关闭了防火墙，甚至允许 ITAPP 的所有协议(共享内存、命名管道、TCP/IP)，还将 Allow Remote connection 设置为 true.

当我使用 127.0.0.1\SQLITRAX 在服务器机器上连接时还有一件事，它立即连接.

请帮我摆脱这个烂摊子:)

解决方案

可能是因为它是远程服务器上的命名实例.
命名实例不使用 SQL Server 标准 Tcp 端口 1433，只有默认(未命名)实例使用 1433 端口.
任何其他命名"实例只是侦听另一个端口.
所以你应该检查 SQL-Server 配置器，在哪个 TCP 端口上侦听，然后告诉客户端连接到这个端口.
假设您的命名实例正在侦听 12345 端口，那么客户端应该使用以下命令进行连接

sqlcmd -S XXX.XXX.XXX.XXX,12345 -U sa -P mypass

当您指定 TCP 端口时，则不需要实例名称.
在我的理解中，实例的名称是 SQL Server 服务查找此命名实例正在侦听的 TCP 端口的一种方式.
但是为了让您的客户端工作，您的客户端需要能够访问解析 SQL Server 实例名称的其他服务(可能是 Sql 服务器代理/SQL Server 浏览器，但我不确定)

更新
下面的屏幕截图显示了在何处为 SQL Server 命名实例设置 TPC 端口.

那么，您的 SQL Server 命名实例在哪个 TCP 端口上侦听?

A very mysterious problem coming up :P

I have a server configured with a static IP. I have installed SQL Server 2008 R2 with additional instance (ITAPP). Now When I use the IP to access the SQL Server following things happens:

Client Machine:

sqlcmd -S XXX.XXX.XXX.XXX -U sa -P mypass

Connected Successfully....

But when I use:

sqlcmd -S XXX.XXX.XXX.XXX\ITAPP -U sa -P mypass

HResult 0xFFFFFFFF, Level 16, State 1 SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server.

When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Even on the same machine (where the SQL Server is installed) using SQL Server Management Studio. I have put off my firewall on both machines, even allow all the protocols for ITAPP (Shared Memory, Named Pipes, TCP/IP), also set Allow Remote connection to true.

One thing more when I use 127.0.0.1\SQLITRAX to connect on server machine it connects Immediately.

Please help me out from this mess :)

解决方案

maybe it's because it's a named instance on a remote server.
Named instance don't use the SQL Server standard Tcp-port 1433, only the default (unnamed) instance use the 1433 port.
Any other "named" instance simply listen on another port.
So you should check in the SQL-Server configurator, on which TCP port is listening, and then tell the client to connect to this port.
Say that your named instance is listening to 12345 port, then the client should connect using the following command

sqlcmd -S XXX.XXX.XXX.XXX,12345 -U sa -P mypass

when you specify the TCP port, then you don't need the name of the instance.
In my understanding the name of the instance is a way for SQL Server services to find the TCP port that this named instance is listening on.
But for this to work your client need to be able to access those other services that resolve the instance name of SQL Server (maybe it's the Sql server Agent / SQL Server browser, but I'm not sure)

Update
Here is a screenshot that show where to set the TPC port for a SQL Server named instance.

so, on which TCP port is your named instance of SQL Server listening?

这篇关于无法远程访问 SQL Server 2008 R2 的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！