0

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

12
  • What is the account you're using to connect? A Windows account or a SQL Authentication account? Nov 1, 2017 at 14:07
  • im am using the sa account, but its not even getting to authentication as far as I can see Nov 1, 2017 at 14:12
  • TCP/IP is enabled under protocols, just checking?
    – DenisT
    Nov 1, 2017 at 14:14
  • @DenisT, yes it is Nov 1, 2017 at 14:16
  • @CodeWarrior so if you run netstat -a -o, you don't see 1433 in the LISTENING state mapped to your SQL's PID?
    – DenisT
    Nov 1, 2017 at 14:20

2 Answers 2

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.

5
  • although this hasnt sorted my issue it has helped. Nov 3, 2017 at 13:43
  • so you still cannot connect remotely? please show your actual connection string
    – sepupic
    Nov 3, 2017 at 13:48
  • I can connect now, but your answer although good didnt resolve my issue, plus i fixed it prior to seeing your response. Nov 3, 2017 at 13:53
  • Why don't you descibe here what was your issue?
    – sepupic
    Nov 3, 2017 at 14:01
  • when i have time i will Nov 3, 2017 at 14:38
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.