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

  • What is the account you're using to connect? A Windows account or a SQL Authentication account? – John Eisbrener Nov 1 '17 at 14:07
  • im am using the sa account, but its not even getting to authentication as far as I can see – Simon Price Nov 1 '17 at 14:12
  • TCP/IP is enabled under protocols, just checking? – DenisT Nov 1 '17 at 14:14
  • @DenisT, yes it is – Simon Price Nov 1 '17 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 '17 at 14:20
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.

  • although this hasnt sorted my issue it has helped. – Simon Price Nov 3 '17 at 13:43
  • so you still cannot connect remotely? please show your actual connection string – sepupic Nov 3 '17 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. – Simon Price Nov 3 '17 at 13:53
  • Why don't you descibe here what was your issue? – sepupic Nov 3 '17 at 14:01
  • when i have time i will – Simon Price Nov 3 '17 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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