ssms cannot connect to default sql server instance without specifying port number

Question

I have multiple SQL Server 2005 instances on a box. From SSMS on my desktop I can connect to that box's named instances with no problem. After some recent network configuration changes, when I want to connect to the default instance from SSMS on my desktop, I have to specify the port number. Before the network changes, I did not have to specify the port number of the default instance.

If I remote to any other box (including the one in question), and use that box's SSMS to connect to that default instance, success. From my desktop, and only from my desktop, I have to specify the port number.

Is it a SQL Server configuration that I've missed? Is it possible something in my PC's configuration is getting in the way? Where would I look, or what could I pass on to the network folks to help them resolve this? Any help is appreciated.

Answer 1

Port 1434 is blocked somewhere along the line, which is used to resolve instance name to port number.

If you specifiy a port number, instance name is ignored...

SQL Server Browser service listens on port 1434

Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. SQL Server Browser support [sic] ipv6 and ipv4.

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

Edit, after comment:

What is the local client setting default? It should be 1433 of course, but if not then just servername will not work.

Launch "SQL Server Configuration Manager".. "SQL native client configuration".. "client protocols".. double click tcp/ip, the default port used by your local PC is there.

Answer 2

I've seen this happen on Windows 2008/7/Vista boxes that have the firewall turned on. Using the named instance does not work but specifying the port does. The fix for me was to set up a firewall rules for SSMS and DTExec in order to allow for normal connections. The script to do so is below but you may have to change the patch to SSMS and DTExec.

rem SSMS
netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow program="D:\Apps\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" enable=yes profile=domain
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program="D:\Apps\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" enable=yes profile=domain

rem DTExec
netsh advfirewall firewall add rule name=SQL-DTExec dir=in action=allow program="D:\Apps\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" enable=yes profile=domain
netsh advfirewall firewall add rule name=SQL-DTExec dir=out action=allow program="D:\Apps\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" enable=yes profile=domain

rem DTExec32
netsh advfirewall firewall add rule name=SQL-DTExec32 dir=in action=allow program="D:\Apps\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" enable=yes profile=domain
netsh advfirewall firewall add rule name=SQL-DTExec32 dir=out action=allow program="D:\Apps\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" enable=yes profile=domain