Unable to connect to SQL Server 2016 remotely

Question

I am trying to access SQL Server 2016 from outside of my network but each time I try I get this 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) (.Net SqlClient Data Provider)

The ports on my managed firewall are open, and I have also turned off Windows Firewall too in the event that this was the blocker but I am still getting the issue.

I have checked the Configuration Manager and its listening on port 1433, however, when I look at netstat -an I dont see this port is being listened on so suspect this may be where the issue exists.

A question I have is if configuration manager says its listening on this port, why is it not showing in netstat?

Also, what else can I try in order to gain access.

Thanks

Answer 1

should i construct the server name as tcp:ipaddress\instance, 1433

Absolutely no.

All that the network library needs to connect to SQL Server instance is it's IP + port.

When you are using default instance, IP address is sufficient as the default port is known, it's 1433, so when you pass IP only, or PC name only, and your instance is listening on 1433 (even if it is named, but still configured to listen 1433), the connection will succeed unless the port is unreachable due to firewall.

When named instance is used, you should pass it's IP address + port or IP address + instance name, but not both port + instance name.

In case you pass in IP + port, the network library just uses them.

In case tou pass IP\instanceName, SQL Server Browser should run. Is's SQL Server Browser that listens on UDP 1434 (that should be open), takes InstanceName as the input and gives out instance's port.

When you pass in both InstanceName and a port, InstanceName is ignored. So not only it serves to nothing, it cheats you in that you may think there is an attempt to resolve InstanceName to the corresponding port, but it's no attempt at all: the port you are passing in is used.

In your particular case if 49689 port is used, all you need to connect to your instance is 192.168.1.182,49689 in SSMS + 49689 shoul be open.

If you are not sure what port is in use, just open SQL Server error log (you can do it using SSMS, notepad or any other text editor, or executing

exec xp_readerrorlog 0,1,N'Server is listening on'

Configuration Manager reflects the last configured value, but if the server was not restarted after configuring, the value for the port is not currently in use while SQL Server error log always gives you effective port values.

Answer 2

It sounds like you can access the SQL instance from the network with no problem. Check the edge managed firewall logs for port 1433 or the ports you are using for the SQL server. Also, check for any Deny policies on the managed firewall. Deny policies take precedence over Allow policies. It might be helpful to run a continuous ping from outside the network to the server by IP and also by name. if those are successful, try it by IP or name and port.