Sql-server – Connect to SQL Server over Internet

connectivityremotesql server

I want to connect to SQL Server via Internet:

  1. I turned off the firewall
  2. I enabled the the DMZ in the router, host address is 192.168.1.24 which is the SQL Server LAN IP
  3. I open port in the router for both SQL Server and SQL Server Browser (1433 – 1434)
  4. I checked the open port via (canyouseeme) website, and the result is success for 1433 and error for 1434 (I don't know the reason for the error)
  5. I made the necessary configuration for the TCP protocols
  6. I checked that the server is allowed remote connection

But I failed to connect to the server via Internet.

My LAN IP is 192.168.1.24
The instance is default instance
My public IP is 95.218.156.146 (for example)

When I type (192.168.1.24) in the server name text-box I succeed to connect to the server,
but I fail to connect when I type 195.218.156.146 or 95.218.156.146 ,1433

Note:

  1. When I put the public IP in the brower then the router page appears (meaning that the public IP is correct)
  2. I didn't find port forwarding in my router, but I have configured the DMZ and port mapping
    enter image description here
    enter image description here

Best Answer

The recommended way to do this is to set up a VPN between the two sites. The steps to do this vary depending on the particular firewall/router being used. If a VPN cannot be used, the following information is provided for how to set up a connection with port mapping. If port-mapping is used, it should only be used in conjunction with additional firewall rules to restrict what source IP addresses can route through the mapped port so that every hacker on earth doesn't have access to your SQL Server.

1. Server IP Configuration

Check your Windows Server's IP configuration by running IPCONFIG / ALL. Your results can contain both IP addresses, but at least your internal IP:
- 192.168.1.24
- 95.218.133.168

2. Check your routing

Start a traceroute with the following command: TRACERT <external IP of server>. Verify that you receive the route to your server.

Important do the same from your LAN and from the INTERNET. The results should look like similar to these for the INTERNET check:

 1    <1 ms    <1 ms    <1 ms  <IP of your router>
 2    <1 ms    <1 ms    <1 ms  <IP of your ISP>
 3    <1 ms    <1 ms    <1 ms  <Another IP connecting to your company>
 4     2 ms     1 ms     1 ms  outside.yourcompany.com [<public IP of your company>]
 5     3 ms     1 ms     1 ms  dmz.yourcompany.com [<IP of DMZ component>]
 6     3 ms     1 ms     1 ms  router.yourcompany.com [<IP of router>]
 7     3 ms     1 ms     1 ms  sqlserver.yourcompany.com [<IP of server>]

Depending on your firewall settings, you might not get past your company's firewall, but if tracert is going in the right direction, then it "knows" the way. Your firewall is just not telling anybody what your network looks like and might not be forwarding the port request to your SQL Server (See Router Configuration later on)

3. Check your SQL Server IP Configuration

  1. In the SQL Server Configuration Manager open up the branch for SQL Server-network configuration and select your instance. (E.g. Protocols for 'MSSQLSERVER')
  2. Right click and open up the properties. Verify that you don't have any settings that could inhibit a connection. Close the setting when you have finished.
  3. In the right hand pane for the protocol settings verify that the TCP/IP protocol is 'Enabled'.

  4. Now right click the TCP/IP setting and open up the properties. In the Protocol tab...
    a) Verify again that Enabled is set to Yes.
    b) Verify that the setting Listen All is set to Yes.
    c) The screen should look like this:
    Protocol Tab

  5. Switch to the IP-Addresses tab and verify for each IPn that ...
    a) Active is set to Yes
    b) Enabled is set to Yes
    c) IP address is your external IP address (or your internal address if you are unable to assign the external address to your SQL Server, because you only have one public IP.)
    d) TCP Dynamic Ports is set to (No value/empty)
    e) TCP port is set to 1433
    f) The screen should look a bit like this, but with your IP address: enter image description here

  6. An then verify the settings for the IPAll portion, by checking that ...
    a) TCP Dynamic Ports is set to (No value/empty)
    b) TCP Port is set to (No value/empty)
    c) The screen should look like this:
    IPAll Configuration

4. Check your router configuration

Your public IP is not the server's, it's the router's. If your server is behind the router then you have to ensure your router is forwarding the request to your SQL Server. This configuration setting can vary from router to router. Here a few examples for configuring routers:
- Port Forwarding (Zyxel)
- Setting up static port sharing (Fritz AVM)
- How do I configure Port Forwarding on my router? (D-Link)

With the provided tips and tricks you should be able to set your SQL Server to accept connections from the Internet.

DISLCAIMER: It is not really recommended to allow connections via the Internet to a database in your LAN. You risk being attacked.

Related Question