Unable to connect to SQL Server 2014 remotely

1
votes

Unable to connect to SQL Server remotely

I have installed SQL Server 2014 Express on Windows 10. I can connect to server locally by entering: computername\SQLEXPRESS or 192.168.0.xxx\SQLEXPRESS

When I try to connect to the server using public ipadress 

122.xxx.xxx.xxx\SQLSERVER

I get an error:

Error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

I also have host name from dlinkddns, tried connecting with host name with same error.

Steps which I have tried:

  1. Enabled remote connections.
  2. Configured 1433 TCP port to be allowed in firewall. Disabled antivirus program. I tried disabling firewall with no success.
  3. Enabled all protocols "TCP/IP', 'Named Pipes', 'Shared memory'
  4. Under TCP/IP properties, changed 'IP All' 'TCP Port' to 1433. I tried enabling IP1 to public ipadress with port 1433 with no success.
  5. I have dlink DIR 816 router, i dont see anything called port forwarding instead it has 'port triggering', i configured to allow port 1433 TCP and 1434 UDP.
  6. I can ping both ipadress and hostname with success but the server would not connect.

I have tried everything to the best of my knowledge but nothing works, any inputs to setup my connection remotely will be of great help!

3
sql-server
Have you started SQL Server Browser service? - ManojAnavatti
If the instance name on the server is SQLEXPRESS - why are you trying to connect to 122.xxx.xxx.xxx\SQLSERVER ?? Have you tried: 122.xxx.xxx.xxx\SQLEXPRESS ?? - marc_s
Apologies for the typo, i tried with 122.xxx.xxx.xxx\SQLEXPRESS with same error. - Mike
Yes, SQL browser service is started and checked running. - Mike
Could be a firewall issue (hardware or software). - TT.

3 Answers

1
votes

The error is pretty specific here. 

error: 26 - Error Locating Server/Instance Specified (.Net SqlClient Data Provider)

You must understand one thing. Your local IP (192.168.0.xxx) is not visible from WWW. Only your Public IP or Global IP is. Once someone comes inside your network they will be able to ping your IP (192.168.0.xxx).

To make it work, you must have the SQL Server installed in a computer whose local IP is accessible from the internet. That means, it should be hosted in a Domain.

Connecting to SQL Server over the Internet

1
votes

We can connect to SQL Server remotely over the internet if the Server has a static public IP address and port forwarding is configured on the router to allow remote connection requests through the router firewall.

In the router firewall settings, there is normally a games and application section, in there add SQL application and give the port range (1433 normally) and then direct the incoming connection by selecting the SQL Server device name on the network.

0
votes

I fixed the issue by adding local ip into DMZ on my router. Everything works like a charm now.