May 23, 2016 at 6:21 am
Situation: 4 servers (2 per data center) clustered. Servers A & B are on DC1, Servers C & D on DC2. Servers A & C have a clustered instances with failovers to B & D respectively. Servers B & D have standalone instances in preparation for Availability Groups (not yet installed).
We can connect to Servers A & C remotely, no problem. We cannot connect to Servers B & D for some reason. The error we're getting is the typical error.
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 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) (Microsoft SQL Server)
We've verified that SQL Server has remote connections set up. Windows Firewall is not enabled on these servers. We can connect to the servers when we're logged into the servers, but not remotely. TCP/IP is enabled and Listen All is Yes.
I've searched the SQL logs and Event logs on the server itself for any indication of what the error could be, but the error isn't even registering on the server. Which tells me the connection isn't even getting to the server. I've tried all variations on ServerName\InstanceName, ServerName (by itself), ServerName\InstanceName,Port. Same error repeats itself.
My coworker and I are ready to throw our boxes into the lagoon. We've been banging our heads against this problem since last week.
Any thoughts on what we could be missing?
Like I said, we can connect to the clustered instances just fine without having needed to do anything. It's the standalone instances on the failover nodes that we're having issues with.
May 23, 2016 at 7:04 am
Hrrrmmmm. This is annoying. If I go into TCP/IP on the server, click over to the IP Addresses tab, scroll down to IPAll and enter 1433 into the TCP Port (leaving the TCP Dynamic Ports alone, it already has a number), then restart SQL Server Service, I can connect remotely by specifying ServerName\InstanceName,1433.
That confirms that I don't have firewall or network issues. But we don't want to use the port number in our connections. We'd like to just use ServerName\InstanceName until we get a CName record for the named instance up and running.
SQL Browser is up and running. I even restarted it. Tested connecting to the server both with TCP Port cleared and with 1433 entered. If I clear it again, I can't connect using any variation on the connection string.
Again, any thoughts on what we're missing?
May 23, 2016 at 9:12 am
Do a remote port scan is 1433 open?
May 23, 2016 at 9:17 am
May 23, 2016 at 9:21 am
gfoxxy93 (5/23/2016)
Do a remote port scan is 1433 open?
Yes.
EDIT: If you look at my second post, that pretty much proves 1433 is open. But the coworker is working on something with our server admin. We're thinking we may need to get a CNAME record.
May 23, 2016 at 9:28 am
And 1434?
You definitely do not need to specify a name. IP address is enough.
May 23, 2016 at 9:29 am
May 23, 2016 at 9:35 am
Brandie Tarvin (5/23/2016)
Hrrrmmmm. This is annoying. If I go into TCP/IP on the server, click over to the IP Addresses tab, scroll down to IPAll and enter 1433 into the TCP Port (leaving the TCP Dynamic Ports alone, it already has a number), then restart SQL Server Service, I can connect remotely by specifying ServerName\InstanceName,1433.That confirms that I don't have firewall or network issues. But we don't want to use the port number in our connections. We'd like to just use ServerName\InstanceName until we get a CName record for the named instance up and running.
SQL Browser is up and running. I even restarted it. Tested connecting to the server both with TCP Port cleared and with 1433 entered. If I clear it again, I can't connect using any variation on the connection string.
Again, any thoughts on what we're missing?
Have you ensured that the port for the SQL Server Browser is open, usually port 1434? I have also seen some networks with the dynamic ports blocked.
https://technet.microsoft.com/en-us/library/ms181087(v=sql.120).aspx
https://msdn.microsoft.com/en-us/library/cc646023(v=sql.120).aspx
May 23, 2016 at 9:35 am
Please reread my second post.
May 25, 2016 at 11:30 am
Okay. The solution was to get a CNAME record in the DNS server for the standalone instance after setting up the alias records in Configuration Manager to use port 1433 and forcing TCP Port on the protocol to use 1433 (the slot under the dynamic port).
This then caused us an unforeseen problem. We were unable to fail over the clustered instance (which was also using port 1433 in its aliases and also had the TCP Port as 1433) to the node with the standalone instance.
To fix the failover problem, I had to change all the Standalone aliases to use another port number. Which allows local server (when we're RDPed in) to use the standalone alias and for us to fail over the clustered instance to this server. And I can connect remotely to the standalone using SERVER\INSTANCE, but I can no longer use just the alias name.
So now I need to get with the server admin and see if a CNAME record depends on a specific port associated with an IP address or not.
May 26, 2016 at 2:50 am
Brandie Tarvin (5/25/2016)
So now I need to get with the server admin and see if a CNAME record depends on a specific port associated with an IP address or not.
A CNAME record neither knows nor cares what ports you're trying to connect to, it's purely at the IP address level.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply