Why can I connect to a SQL Server 2005 instance using sqlcmd but not SSMS after IP change?

Question

We have a couple of SQL Server instances at our main office, and one on our colocated web server. There are a few replications that handle data exchange between the web server and the main office servers.

We switched ISPs today at our main office. We did our homework and were ready for the switch (ips in hosts files changed, etc...) As soon as we switched over to the new connection, all replications completely broke. I tried to connect to our main office servers using SSMS; no cookie. We can ping and ftp to our home office servers just fine using server names from our web server. I saw that ports 1433 and 1434 were blocked by the new ISP, and notified the appropriate people; they should be unblocked now. Still no dice from SMSS.

I then tried connecting with sqlcmd, which amazingly worked. SMSS won't connect to the server, but sqlcmd will. Why is this? Everything worked like a charm before the ISP switch.

Example

ping homeserver
    success!
ftp homeserver
    success!
sqlcmd -S homeserver\instance -U user -P pass
    success!
- Trying to connect using SSMS, using the same homeserver\instance and user/pass combo
    Provider: SQL Network Interfaces, error: 26 - Error locating server/instance specified) 

Answer 1

The instance should be: homeserver\instance (backslash rather than forward slash)

Answer 2

A bit of a guess, but SQL have different options for network protocols, including Shared Memory, TCP/IP, Named Pipes and VIA. You can set the configuration for both server and clients (on the appropriate machines) using SQL Server Configuration Manager. The native client will often use Named Pipes, which often will not work over a WAN as (I think) it uses broadcasts for name resolution (and may need more ports opened). So, if SSMS tries to connect via Named Pipes it may not be able to resolve the server name, whereas if sqlcmd uses TCP/IP it will be able to.

In other words, the first place I would look would be in SQL Server Configuration Manager on both the server and the client. Try disabling everything other than TCP/IP or change the provider order so TCP/IP is at the top. You can leave Shared Memory right at the top and enabled if you like, it is handy for local machine connections.

Answer 3

You could also enable the SQL browser service on your server, thereby you can actually see the name of the server instance.

Answer 4

SQL Doesn't listen on TCP 1434, it listens on UDP 1434. You can't test UDP ports using telnet. Have your ISP open UDP 1434.

Check the client connection order on the server, and I'll get that Named Pipes is above TCP/IP, but SSMS isn't use Named Pipes for some reason.

If you are using named instances make sure that the SQL Server has what ever TCP ports you are using open between the colo and the office.

You might want to look into setting up a VPN between the office and the colo so that you don't have to worry about firewalls between the two.