SQL Server 2005 Named Instance port problem

Question

Hi,

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.

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.

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.

I also have SQLBrowser service running, and all instances are configured to allow remote connections.

One instance is set to the default port (1433), and it works fine.

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:

TITLE: Microsoft SQL Server Management Studio

---

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

---

ADDITIONAL INFORMATION:

Failed to connect to server . (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)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

---

Answer 1

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.

Answer 2

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.

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.

Answer 3

From this source

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:

> sqlcmd -S
> tcp:NameOfTheServer\sqlexpress,1433

Answer 4

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.

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.