Can't connect to sql server 2008 named instance

Question

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?

Solution 1

Have you tried your.sql.server.fqdn\MC08.

The port specification in SSMS is totally braindead though. For whatever reason they decided to use comma(,) as host and port delimeter instead of colon(:). So if you want to use a port number use this connection string: your.sql.server.fqdn,1045.

Solution 2

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..

Comments

[Admin]

Connect with what? If you are using a non-standard port, you may have to specify that within your connection.

[Admin]

connect with sql server management studio. Should I turn dynamic ports back on? I thought the sql browser service would handle the static port.

[Remus Rusanu]

Firewall blocking 1045 perhaps?

[Admin]

Checked the firewall and its not the problem.

[Elva]

My server is not using/connected to a domain. I tried adding the ",1045" and that didn't either.