I have 4 different named instances of SQL Server 2005 on a single server (for testing purposes). There is no default instance on the server.
我在一个服务器上有4个不同的SQL Server 2005命名实例(用于测试目的)。服务器上没有默认实例。
Because I will eventually need to allow communication to these instances across the firewall, I have set the ports of each instance statically listening on all IPs for the server.
因为我最终需要允许通过防火墙与这些实例进行通信，所以我为服务器设置了每个实例的端口静态监听所有ip。
Edit: TCP/IP, Shared Memory, and Named Pipes are all enabled. VIA is disabled. The ports are statically set for All IPs on the TCP/IP protocol, and each named instance is using a separate port.
编辑:TCP/IP、共享内存和命名管道都已启用。通过是禁用的。端口是为TCP/IP协议上的所有IP静态设置的，每个命名实例都使用一个单独的端口。
I also have SQLBrowser service running, and all instances are configured to allow remote connections.
我还运行SQLBrowser服务，所有实例都配置为允许远程连接。
One instance is set to the default port (1433), and it works fine.
一个实例被设置为默认端口(1433)，它运行良好。
The other instances, however, exhibit very strange behavior. When I connect to them using the Sql Server Management Studio within the network (so I'm not even crossing the firewall yet), the studio connects without complaining. However, as soon as I try to expand the Database list for the instance, or refresh the instance, or pretty much anything else, I get the following error:
然而，其他的例子显示出非常奇怪的行为。当我使用网络中的Sql Server Management Studio连接到它们时(因此我甚至还没有越过防火墙)，Studio毫无怨言地进行连接。但是，一旦我尝试扩展实例的数据库列表，或者刷新实例，或者其他任何东西，我就会得到以下错误:
TITLE: Microsoft SQL Server Management Studio
标题:Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
未能检索此请求的数据。(Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
寻求帮助,请点击:http://go.microsoft.com/fwlink?ProdName=Microsoft + SQL + Server&LinkId = 20476
ADDITIONAL INFORMATION:
附加信息:
Failed to connect to server . (Microsoft.SqlServer.ConnectionInfo)
连接服务器失败。(Microsoft.SqlServer.ConnectionInfo)
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
成功地与服务器建立了连接，但是在登录过程中出现了错误。(提供程序:命名管道提供程序，错误:0 -管道的另一端没有进程)(Microsoft SQL Server，错误:233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
寻求帮助,请点击:http://go.microsoft.com/fwlink?ProdName=Microsoft + SQL + Server&EvtSrc = MSSQLServer&EvtID = 233 &linkid = 20476
4 个解决方案
#1
2
The first thing to do would be to try adding the prefix np: or tcp: (for either Named Pipes or TCP/IP) before the name of the server. For tcp/ip, you can also try adding the port number (,1433) after the name of the server. If this is not the default instance, you must add the name of the instance after the name of the server; for example:
首先要做的是尝试在服务器名称之前添加前缀np:或tcp:(用于命名管道或tcp /IP)。对于tcp/ip，您还可以尝试在服务器名称之后添加端口号(，1433)。如果这不是默认实例，则必须在服务器名称之后添加实例的名称;例如:
> sqlcmd -S
> tcp:NameOfTheServer\sqlexpress,1433
EDIT: removed source link, as it is now a dead link
编辑:删除源链接，因为它现在是一个死链接。
#2
2
Bofe's answer worked, so that gave an indication that something was askew with the ports.
博夫的回答奏效了，这表明港口出了问题。
It turns out that in the TCP/IP settings for the named instances, I had listen All set on the protocol, and then had a static port set for IPAll, but dynamic port set for IP1. I had assumed that since IP1 was disabled, I didn't need to worry about it, but apparently if you have Listen All set, then the enabled property for IP1 is ignored. So, having only one ip address on the server, and configuring IP1 dynamic ports, and IPAll static ports caused some sort of weird conflict.
事实证明，在命名实例的TCP/IP设置中，我监听了协议上的所有设置，然后为IPAll设置了静态端口集，但为IP1设置了动态端口集。我假设由于IP1被禁用，所以我不需要担心它，但是显然如果您已经监听了所有的set，那么IP1的enabled属性将被忽略。因此，在服务器上只有一个ip地址，并配置IP1动态端口，并且IPAll静态端口导致了某种奇怪的冲突。
To fix the problem, I just set IP1 to use the same static port as IPAll, enabled IP1, rebooted the server, and things worked like they were supposed to, without having to explicitly set the port in the connection string.
为了解决这个问题，我将IP1设置为使用与IPAll相同的静态端口，启用IP1，重新启动服务器，并按照预期的方式工作，而不必显式地在连接字符串中设置端口。
#3
1
Try using different TCP/IP ports for each instance on the server. You will need to go into the SQL Server Configuration Manager to change these settings. Under TCP/IP you can change the port numbers and then use these in your connection string or in SQL Management Studio when connecting.
尝试为服务器上的每个实例使用不同的TCP/IP端口。您需要进入SQL Server配置管理器以更改这些设置。在TCP/IP下，您可以更改端口号，然后在连接字符串或SQL Management Studio中使用它们。
Out of curiosity, why do you have for instances o the server? Can you use one instance and have multiple databases instead? If you are using other instances for development or testing you might want to consider moving those to another box.
出于好奇，为什么在服务器上有实例?您可以使用一个实例并拥有多个数据库吗?如果您正在使用其他实例进行开发或测试，您可能需要考虑将它们移动到另一个框中。
#4
0
Try enabling TCP/IP communication to the SQL server instances. If you are eventually going to be traversing a firewall, you'll probably want to use TCP/IP instead of named pipes anyway.
尝试对SQL服务器实例启用TCP/IP通信。如果您最终要遍历防火墙，您可能希望使用TCP/IP而不是命名管道。
#1
2
The first thing to do would be to try adding the prefix np: or tcp: (for either Named Pipes or TCP/IP) before the name of the server. For tcp/ip, you can also try adding the port number (,1433) after the name of the server. If this is not the default instance, you must add the name of the instance after the name of the server; for example:
首先要做的是尝试在服务器名称之前添加前缀np:或tcp:(用于命名管道或tcp /IP)。对于tcp/ip，您还可以尝试在服务器名称之后添加端口号(，1433)。如果这不是默认实例，则必须在服务器名称之后添加实例的名称;例如:
> sqlcmd -S
> tcp:NameOfTheServer\sqlexpress,1433
EDIT: removed source link, as it is now a dead link
编辑:删除源链接，因为它现在是一个死链接。
#2
2
Bofe's answer worked, so that gave an indication that something was askew with the ports.
博夫的回答奏效了，这表明港口出了问题。
It turns out that in the TCP/IP settings for the named instances, I had listen All set on the protocol, and then had a static port set for IPAll, but dynamic port set for IP1. I had assumed that since IP1 was disabled, I didn't need to worry about it, but apparently if you have Listen All set, then the enabled property for IP1 is ignored. So, having only one ip address on the server, and configuring IP1 dynamic ports, and IPAll static ports caused some sort of weird conflict.
事实证明，在命名实例的TCP/IP设置中，我监听了协议上的所有设置，然后为IPAll设置了静态端口集，但为IP1设置了动态端口集。我假设由于IP1被禁用，所以我不需要担心它，但是显然如果您已经监听了所有的set，那么IP1的enabled属性将被忽略。因此，在服务器上只有一个ip地址，并配置IP1动态端口，并且IPAll静态端口导致了某种奇怪的冲突。
To fix the problem, I just set IP1 to use the same static port as IPAll, enabled IP1, rebooted the server, and things worked like they were supposed to, without having to explicitly set the port in the connection string.
为了解决这个问题，我将IP1设置为使用与IPAll相同的静态端口，启用IP1，重新启动服务器，并按照预期的方式工作，而不必显式地在连接字符串中设置端口。
#3
1
Try using different TCP/IP ports for each instance on the server. You will need to go into the SQL Server Configuration Manager to change these settings. Under TCP/IP you can change the port numbers and then use these in your connection string or in SQL Management Studio when connecting.
尝试为服务器上的每个实例使用不同的TCP/IP端口。您需要进入SQL Server配置管理器以更改这些设置。在TCP/IP下，您可以更改端口号，然后在连接字符串或SQL Management Studio中使用它们。
Out of curiosity, why do you have for instances o the server? Can you use one instance and have multiple databases instead? If you are using other instances for development or testing you might want to consider moving those to another box.
出于好奇，为什么在服务器上有实例?您可以使用一个实例并拥有多个数据库吗?如果您正在使用其他实例进行开发或测试，您可能需要考虑将它们移动到另一个框中。
#4
0
Try enabling TCP/IP communication to the SQL server instances. If you are eventually going to be traversing a firewall, you'll probably want to use TCP/IP instead of named pipes anyway.
尝试对SQL服务器实例启用TCP/IP通信。如果您最终要遍历防火墙，您可能希望使用TCP/IP而不是命名管道。