1

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?

4
  • 2
    Connect with what? If you are using a non-standard port, you may have to specify that within your connection.
    – Russell Steen
    Nov 11, 2009 at 19:22
  • connect with sql server management studio. Should I turn dynamic ports back on? I thought the sql browser service would handle the static port.
    – Anonymous
    Nov 11, 2009 at 19:27
  • Firewall blocking 1045 perhaps? Nov 11, 2009 at 19:29
  • Checked the firewall and its not the problem.
    – Anonymous
    Nov 11, 2009 at 21:48

6 Answers 6

2

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.

1
  • My server is not using/connected to a domain. I tried adding the ",1045" and that didn't either.
    – Anonymous
    Nov 11, 2009 at 21:51
1

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

0

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.

0

Verify that the TCP/IP interface for the IP you want is enabled.

Additionally, try testing with ODBC DSNs and test with TCP/IP and Pipes.

Or use

telnet your.sql.server.fqdn 1045

And see if you get a blinking cursor, which confirms there's no firewall problems.

0

If you have UAC turned on, you may need to start management studio as administrator to connect to the 2008 SQL instance.

Right click -> run as Administrator

0

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.

You must log in to answer this question.