2

So, I have two servers (ServerA and ServerB) that I've set up with Windows Server 2008 R2 and SQL Server 2008 R2. My goal is to replicate between these two in a failover fashion (with ServerC as a witness instance, but that's for later).

Both servers are configured in the same way. Firewall rules added, SQL Server installed with the same settings. Network via TCP/IP and named pipes enabled. I can connect with SSMS to ServerA fine, but I get the following when connecting to ServerB:

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) (.Net SqlClient Data Provider)

Now, the instance name is correct and I can successfully connect to ServerA with the instance name (they're the same on both servers). If I leave out the instance name, I get another error saying it just timed out.

The only thing that works is running SSMS on the local machine via shared memory, but obviously that doesn't solve the problem. I feel like I'm missing something obvious. Help?

  • 3
    Are you sure the option "Allow remote connections to this server" is checked? Is the SQL Browser service enabled and running? (The Browser service helps map an instance name to the port it's running on.) If it is a named instance, why would you ever bother trying without the instance name? What specific firewall rules did you add? Is the instance using dynamic TCP ports? If so how do your firewall rules account for those? Did you try connecting when the firewall was turned off completely? – Aaron Bertrand Aug 13 '13 at 19:24
  • 1
    @AaronBertrand Yes, it's checked. The browser service is running. I tried without the instance name since I was trying anything I could think of. I added a rule for the sqlservr executable. It's not using dynamic ports ...although strangely enough, firewall off worked. I really don't want to leave it off though ...I'm missing some rule. – Nathan C Aug 13 '13 at 19:34
  • How do you know it's not using dynamic ports? Did you check this in SQL Server Configuration Manager? If it works with the firewall off then obviously there is some trouble either connecting to the IP or connecting to the port that SQL Server is running on... – Aaron Bertrand Aug 13 '13 at 19:37
  • 1
    @AaronBertrand The firewall was indeed the culprit. I unblocked the required UDP port for the browser service and it started working. Weird since I didn't have to do this on the first server. I checked the configuration manager and it was set to "No". I knew it was something simple. At my wits end with MSSQL for the day it seems. :) – Nathan C Aug 13 '13 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.