Cannot connect to SQL Server named instance from another SQL Server

Question

I would appreciate some help as I've been stuck for 2 days on this issue!

Scenario: I can connect to SERVER\INSTANCE from my development machine (and another colleagues), but cannot connect from another SQL Server. The error I get is the generic "...verify the instance name is correct..". Things I have done/verified:

1. I've disabled the firewall on the destination (and source) server to see if it is a firewall issue (this seems most likely since I can connect from my machine but this didn't help).

2. I've verified that SQL Browser is working (which it is since I can connect from development machine)

3. Since both SQL Servers have multiple instances and hard-coded ports I even made sure that they were different ports incase there was some conflict (this didn't help).

4. I've restarted the SQL Server and verified that browser / instance services are running

5. Checked event log - nothing of note

6. Interestingly enough if I don't connect over the instance name but connect via the dynamic port (i.e. SERVER,PORT) from the second server it works fine - which suggests to me SQL Browser is at fault, except that it works fine locally to the server and from my development machine.

Any ideas and suggestions? Thanks.

Edit: For comment clarification I'll refer to the data SQL Server as SQLA and the non-data SQLB.

Edit #2: Adding more test cases / info:

Info: The above tests were all done via the SSMS interface to establish a connection to the database, the databases involved are both 2012.

New Test Case: I tried running a script to setup a linked server instead and found that running the script on a SQL Server 2005 box worked fine, but running the same script on the SQL Server 2012 server (SQLB) failed to connect to SQLA with the error: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Edit #3: Narrowed down the potential issue:

Downloaded and ran PortQry and when run from my dev box I get all the instances returned with querying 1434 over UDP, running the same query from SQLB returns NO instances and it states 1434 as being FILTERED whereas on the dev box it returned as LISTING. I can only think this is firewall related except that I've disabled the firewall on both machines

Answer 1

Your test cases where you cannot connect with "ServerName\Instance" but ARE able to connect to the server via "ServerName,Port" is what happens when you VPN into a network with Microsoft VPN. (I had this issue). For my VPN Issue I simply use the static port numbers to get around it.

This is appearently due to VPN not forwarding UDP Packets, allowing only TCP Connections.

In your case your firewall or security settings or antivirus or whatever may be blocking UDP.

I would suggest you check your firewall setting to specifically allow for UDP.

Browser Artical

On startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all SQL Server instances 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 will return all ports enabled for SQL Server. SQL Server 2005 and SQL Server Browser support ipv6 and ipv4.

When SQL Server 2000 and SQL Server 2005 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.

Using a Firewall

To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434 in addition to the TCP port used by SQL Server (for example, 1433).