November 7, 2008 at 8:58 am
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.
TCP/IP, Shared Memory, and Named Pipes are all enabled. VIA is disabled.
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
------------------------------
November 7, 2008 at 9:48 am
Hi Nathan,
As a matter of interest is UDP port 1434 open on the SQL server? If it is can you telnet to it?
I've seen plenty of issues with named instances where UDP port 1434 has been the culprit. Although, admittedly this has been where applications/3rd parties can't connect remotely to a SQL named instance. Not sure about this though as you can initially login.
It seems strange that you are experiencing these issues on a LAN. What do your network guys say about it?
Which SQL Version/Service Pack/Hotfixes have you installed/applied.
Mark
November 7, 2008 at 10:17 am
Are you connecting via named pipes? Check your client config.
You want to have a specific port set for each instance and then use that in your registration on the client. The reg is name,port, so sql.sqlservercentral.com,1433
November 7, 2008 at 10:35 am
Hi guys, thanks for the responses.
SSC-Enthusiastic: My understanding is that telnet only works for TCP connections, not UDP connections. However, netstat does show that 1434 is bound on the server, and the SQLBrowser service is running. There are currently no firewalls between my machine and the sql box (though eventually other machines will need to connect through the firewall, and I will need to make sure 1434 can pass through the firewall at that time)
Each instance is configured with a specific port.
Is it possible that the client is using Named pipes for the initial connection, but then switching to TCP/IP for the other operations? When I explicitly use the port number in the connection string as suggested by Steve Jones, everything works.
But I thought the whole point of the SQLBrowser service was so that you didn't have to specify ports in the connection string?
November 7, 2008 at 2:46 pm
Ok, I found how to fix my problem, though I'm still not entirely sure why it was a problem in the first place. Under the TCP/IP protocol settings, I had listen all selected. On the IP Addresses tab, I had an explicit address set for IP1 (the same ip as the host server) that was Active, but not enabled. I had assumed that since it was not enabled, I didn't need to worry about it. So IP1 was set to use dynamic ports, but IPAll was set to use a static port. Apparently this caused some kind of weird conflict.
Once I set IP1 to use the same static port as IPAll, and enabled IP1 (and restarted the server), things worked without having to specify a port number in the connection string.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply