2

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
  • what is the error? try portqry from you machine with the public IP and see if works. portqry -n 195.218.156.146 -p tcp -e 1433 – jesijesi Jun 26 '17 at 6:01
  • "fail to connect" is a useless description of the problem. What is the error message? Are you connecting with windows authentication or SQL authentication? are there any messages in your router logs or SQL Server logs? – Nick.McDermaid Jun 26 '17 at 7:06
  • What do you get if you type telnet 195.218.156.146 1433? Do you know if your SQL Server is set to dynamic ports? it'll tell you in the SQL Server log – Nick.McDermaid Jun 26 '17 at 7:08
  • 2
    Exposing Sql Server to the Internet is not a best practice. Why not use a VPN instead? – vonPryz Jun 26 '17 at 8:05
  • Dear jesijesi , Nick.McDermaid and vonPryz Thank you very much for your help for more description , i have attached the pictures , please take a look at it @jesijesi – salbenny Jun 27 '17 at 6:32
5

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.

  • thank you very much TELNET test failed, also i used the port query tool and the resul was " TCP port 1433 (ms-sql-s service): FILTERED " why its FILTERED ??!! i turned off firewall I do not have an antivirus i open "1433" port on my router and checked it via "canyouseeme " wesit and the result was "Success: I can see your service on 95.218.175.225 on port (1433) Your ISP is not blocking port 1433 " what else i must do ?? why tenet test fails ??why port 1433 (ms-sql-s service): FILTERED ?!! – salbenny Jun 28 '17 at 16:40
  • Of course i get the error when only trying connect over internet but fo local ip every thing is okay telnet 192.168.1.24 1433 gets no error Starting portqry.exe -n 192.168.1.244 -e 1433 -p TCP gets TCP port 1433 (ms-sql-s service): LISTENING i tryed use aonther port rather defult port 1433 , but the same result – salbenny Jun 28 '17 at 16:45

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.