I have sql server 2005 and 2008 running on a server on my local (and very straightforward) network. Using sql management studio 2008 and visual studio 2008, I can connect over the network to the 2005 instance which is the default instance. I can't connect to the 08 instance (named MC08). I can connect to both when logged in to the server. Remote connections have been enabled for MC08 and dynamic ports is turned off (assigned to port 1045). The sql browser service is running as well. This used to work so I'm not sure what has changed. I can't connect using the IP address either. Nothing unusual in the event log either. Any ideas?
For completeness I will also point out if you have multiple NICs on the server this can happen because of the client firewall: the SQL Browser response will not necessarily come back from the same IP that the client sent it to (because it uses multicast), and thus that response will be dropped by the firewall. That was fun to troubleshoot..
Apparently this whole issue is stemming from a change in internet service providers from this past summer. Everything worked when I was on comcast. I switched to charter when I moved this summer and did not have any reason to debug/modify my applications that used sql 2008 databases until this past week. To resolve the issue, I put an entry in the hosts file on my client pc for the sql server box. That does not explain why I could connect to the default instance on that box and not the named instance, but either way the problem appears to be solved. Comcast just let you hook up the modem and then the router. Charter won't work with a router unless you use mac address cloning and clone the mac address of the pc used in the initial install. That's the only thing I can see that's different.
As you can connect to the default instance we will assume that your network and DNS (or some other form of name resolution is working).
Although you have made the 2008 named instance use a static port, the SQL Browser service will still be used to establish the port from the name you supply.
If no response is recieved from the browser service then the SQL client will try port 1433. I'm assuming that becuase you can connect to the default instance but not the named instance that the problem is probably with this step.
You've aready said the browser service is running so that leaves.
1) Check that UDP post 1434 (or the sql browser service) is allowed through the server firewall.
2) Check that UPD port 1434 is allowed through the firewall of your PC. (I never found this as an issue from XP but have found it an issue on Windows 7 Pro)
I'd suggest the first step would be to turn off the firewall of the PC you are connecting from.