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
- In the SQL Server Configuration Manager open up the branch for SQL Server-network configuration and select your instance. (E.g.
Protocols for 'MSSQLSERVER'
)
- 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.
- In the right hand pane for the protocol settings verify that the TCP/IP protocol is
'Enabled'
.
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](https://cdn.statically.io/img/i.sstatic.net/t0Qgi.png)
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](https://cdn.statically.io/img/i.sstatic.net/XO306.png)
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](https://cdn.statically.io/img/i.sstatic.net/9JgpY.png)
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.
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