SQL Server Connectivity Issues - Ideas

[Marie_Mittendorf]

I have to admit that have no knowledge about connectivity and setup of remote SQL servers. I usually have everything set up by knowledgeable IT support.

In my current case the best I can say about the support is that they are either not willing or able - choose either. Therefore, I have to check myself what I can do.

I had to do the remote settings myself; in context of me doing it myself the first time, this might be useful information.

The remote access works fully for out data integration tool (Jitterbit) and usually (:-)) on my own devise using SSMS 18.11.1. "Usually" means that I get error messages about network connectivity too often too work fluently.

Product used on the remote server: 

SSMS 18.11.1 with SQL Express 2019

Error Message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to connect to server 45.155.172.169. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-53-database-engine-err...

------------------------------

The network path was not found

------------------------------
BUTTONS:

OK
------------------------------

SQL Server Configuration:

TCP Port enabled for Standard Port 1433

Windows Defender Firewall:

Inbound Rules set for Standard Port 1433

Connectivity errors appear either with WLAN or LAN, in our office or in my home office.

Any checks or recommendations to improve the accessibility that I can do myself are welcome.

If it's server related I have to check with our support partner.

[#2]

@Marie_Mittendorf 

Hi, Marie.

Error 53 indicates the remote host running SQL Server cannot be found, which in turn usually relates to not being able to resolve the DNS name of that host.

Using my example below, the remote SQL Server host name is rpsql02.robertsonpayne.com:

Checking this name in PowerShell confirms that the DNS name does not exist, hence the SSMS error 53:

I'd recommend using Resolve-DnsName as depicted above (using your own server's host name, of course) to confirm if the remote SQL Server host name can be resolved.

I doubt you'd be in a position to fix this, but the output from Resolve-DnsName should help your service provider remedy the issue.

There are hacks for getting around this kind of issue but that's not a good path to pursue, so I'll avoid talking about those for now.

Cheers,

Lain

[#3]

@LainRobertson 

Thanks for the explanation so far. Anything around that topic will help me to address what needs to be done.

Seems like one information was missing from my side:
I'm not using the server name, but the IP for remote access. Actually, because the server name didn't work, but I didn't think of it as an issue if I had the IP, because that happened in the past as well with better support than now.
Therefore I checked PowerShell first with the name which gave the result: DNS Server Failure

and with name and IP which resulted in a time out

Any further info I might need?

[#4]

@Marie_Mittendorf 

Fair enough.

If you're using an IPv4 address instead of a name then you can use Test-NetConnection to see if the server is reachable, as shown in the example below. I've used a host name in my example, which you'd change to the IPv4 address of the server you're trying to reach.

In my case, the response was successful (shown bordered in green):

If "TcpTestSucceeded" = False in your test, then the most likely issues are:

1. A firewall blocking the connection from your client;
2. A routing issue is preventing your client from knowing how to reach the SQL Server (I don't think this one is likely, but it is possible.)

You'd need to follow these up with your service provider.

You did mention Wi-Fi in your original post, which piques my curiosity as it's not uncommon for wireless networks to be heavily restricted. Next time you're in the office (if you work from an office), try plugging into a wired connection, disable your Wi-Fi connection and see if you can connect.

Cheers,

Lain