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
Best Answer
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's1433
, so when you pass IP only, or PC name only, and your instance is listening on1433
(even if it is named, but still configured to listen1433
), the connection will succeed unless the port is unreachable due to firewall.When
named instance
is used, you should pass it'sIP address
+port
orIP address
+instance name
, but not bothport
+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'sSQL Server Browser
that listens onUDP 1434
(that should be open), takesInstanceName
as the input and gives out instance'sport
.When you pass in both
InstanceName
and aport
,InstanceName
is ignored. So not only it serves to nothing, it cheats you in that you may think there is an attempt to resolveInstanceName
to the corresponding port, but it's no attempt at all: theport
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 executingexec 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 whileSQL Server error log
always gives you effective port values.