One application only cannot connect to a given SQL Server, all others can

Question

I am encountering a very strange issue with SQL Server on a customer's machines and can't find any resources online regarding my exact issue.

We publish an application that uses a SQL Server database running on a server at the customer's site, and they consume the database from many client applications on various machines within their own network. We store the location of their server in a specific registry key and use that to connect to their database

Our main application is able to connect to the SQL Server and working perfectly at the site. However (and only for this particular customer), a secondary application that we deploy which reads out the same registry key cannot connect, from the same machine, to the same server, using the same user account. It appears to be timing out attempting to hit the server as it takes a long time to write the errors to log.

It is receiving the following error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I have done the following steps to try to track down the issue:

• Connected successfully to the same database instance using the same username and password in SQL Server Management Studio as well as our other application
• Logged the Instance Name, User ID and Password to confirm it's using the same details
• Tried using both IP address and Server Name - both work perfectly in our main app and SSMS but not in the other application
• Turned off the firewall on the client machine (though it was set to allow all outbound connections by default anyway)
• Confirmed there are no firewall rules on the server that should stop us connecting (and our other application works anyway so it shouldn't be a server side firewall)
• Rebooted both machines

Is there anything I'm missing that could cause this type of issue? Noting that the particular application works at plenty of our other customers.

Further Notes: I have now logged the full connection string and confirmed that the server, instance name, username and password are correct. I'm at a complete loss for how this is happening..

• Thomas: Hey @J.D. yep, absolutely fine connecting via SSMS on the same machine the app is failing to connect from
• Thomas: I have now logged the full connection string and confirmed that the server, instance name, username and password are correct. I'm at a complete loss for how this is happening..
• TimmyTheDBA: Are you connecting to a named instance, and is it the same for both applications?

Answer 1

The error message suggests that you attempt to connect to a named instance.

I can repro that error by trying to connect to a machine that doesn't exist (and enter any instance name), forcing either TCP or Named Pipes Netlib. If I force Shared Memory Netlib, I get a different error.

The client tries shared memory first, then TCP and finally Named Pipes netlib. If all three fails, you get the error from the last one attempted (Named Pipes).

If I try to connect to a machine that does exist, but forcing a newlib that it doesn't listen to, I get a different error (error code 28).

I.e., IMO it is likely that the machine name you try to connect to doesn't exist, or is unreachable to the client. I'd print out the connection string so you know for sure what you try to connect to.

• Thomas: I have now logged the full connection string and confirmed that the server, instance name, username and password are correct. I'm at a complete loss for how this is happening..
• Tibor Karaszi: So on the same client machine: One application can connect but not the other application. They both use the same connection string. If that is the case, I can only imagine some type of firewall prohibiting the application. The error message is clear: it cannot find SQL Server.
• Thomas: How could a firewall external to the client block one application but not another? We have looked on the machine for local firewalls and found none...
• Tibor Karaszi: I was thinking about a local firewall. If you know that isn't the case, then I'm out of ideas, I'm afraid.

Answer 2

After looking your all efforts there is one possibility left, Any 3rd party firewall(Network firewall)/ Domain level firewall OR a VPN restriction on client end.

• We need to add exception of inbound and outbound rule same as we do for firewall.
• Source
• I mean use the same ports and add inbound rules (1433 TCP and 1434 UDP) and outbound (same ports as inbound) rules in the network/domain firewall rules. (Note this is different from local firewall.)

Note: As you said server is accessible from the other clients fine I am ignoring that. But do check regional restriction rule in server also. Check server event logs just for double check.

I hope this helps