August 30, 2019 at 12:00 pm
We have the weirdest connection issue that we can't seem to troubleshoot. I recently set up a 2 node clustered sandbox of SQL 2017 with high availability. Node 2 is the reporting node. We have one user who cannot connect to Node 2. It keeps giving her the below 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)
She can connect to every other instance / node of SQL that she has permissions on. Every other member of her team can connect to Node 2. We've verified she's a member of the AD group that has permissions on Node 2. When we log into her workstation, even we cannot connect to Node 2 and we're sysadmin in the system.
It obviously a workstation-related problem because everyone else can connect remotely to Node 2, just not when logged into her machine. And it's not a SQL driver issue or SQL Browser (it is running, we checked) because she can connect to Node 1 and other instances of SQL just fine. It can't be a permissions thing because we get the same issue logged on her box as she does (so there's no hidden deny unless it's a group policy we missed). We're very confused and running out of things to troubleshoot. Does anyone have any thoughts?
August 30, 2019 at 2:04 pm
Firewall?
Ping IP? Ping network name?
I'd think networking here.
August 30, 2019 at 2:10 pm
I'll have our help desk check it, but she's sitting in the same office as us, on the same network as us. So I would think if it were a firewall issue, it would affect all of us.
Ping works from her PC to the server (it sees it), but the RDP client is disabled on her box so we can't tested a direct RDP connection. We can map via Windows Explorer to see the SAN shares, but ODBC isn't working to Node 2 even though all the ODBC clients are the same versions as ours.
August 30, 2019 at 2:22 pm
I was thinking more local firewall here. Certainly could be a routing issue or some naming. Are you connecting with
a-server/host name
b - ip
c-fqdn
Any difference in connecting in any way? Make sure no alias or DSN is set up that might be using the wrong protocol. For that matter, double check protocols and versions of libraries connecting. Protocol might be an issue if she uses one enabled on node 1 but not node2
August 30, 2019 at 2:32 pm
Following on from Steve's comments - maybe there is an entry in the local hosts file so if you ping/try to connect by name you aren't actually connecting to the same node2 as on other machines?
August 30, 2019 at 2:58 pm
We're trying it by ServerName\InstanceName. It's a named instance (there are no other instances on the server) and this instance name is new / doesn't exist elsewhere on our system.
So... Color me confused. How do I connect to a SQL instance in SSMS via ip and fqdn? Or are you just referring to non-SQL server connections?
I'll check the protocols and everything else. Thanks for the suggestions.
August 30, 2019 at 6:03 pm
IP would be: 1.1.1.1,33333, where 3333 is the port. IF you have dynamic ports, I suspect that the browser doesn't help here. Could be wrong, but you can get the port from the error log.
For FQDN, I'm assuming you have some DNS, so mynode2.mycompany.com/secondinstance
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply