Awards
- Experts Exchange Approved
Setting up SQL to use an external IP can give you flexability to access a database without opening up a VPN tunnel or full fledged RDP session. This is particularly useful for low bandwidth clients that have a poor internet connection or are being charged for bandwidth used.
***WARNING***
This will open up ports in your firewall. You will want to take precautionary steps to be sure that you are not making your network and/or server vulnerable to attack. I will show you a few ways you can protect yourself when using this method.
Let's get started
First, I'm going to check to make sure that my SQL settings will allow remote connections.
Open SQL Server Management Studio. Right click on the server and select Properties. This will show you the IP your SQL Server is using.
***WARNING***
This will open up ports in your firewall. You will want to take precautionary steps to be sure that you are not making your network and/or server vulnerable to attack. I will show you a few ways you can protect yourself when using this method.
Let's get started
First, I'm going to check to make sure that my SQL settings will allow remote connections.
Open SQL Server Management Studio. Right click on the server and select Properties. This will show you the IP your SQL Server is using.
Open SQL Server Configuration Manager. Expand the SQL Server 2005 Network Configuration menu and highlight the "Protocols for MSSQLSERVER". Make sure that TCP/IP is enabled.
Right click on the "TCP/IP" and select Properties.
Click on the "IP Addresses" Tab.
You should see 3 different entries: IP1, IP2, and IPAll. Usually IP1 will be your external IP, IP2 will be your internal IP.
You can specify the external IP here. I have chosen the IP 198.113.113.113 (not a real IP) I will use the IP in later steps.
You can also specify the port that will be used. I will use the default port of 1433 for this example.
*TIP: Use a custom port to help with security. Make sure to use the same port here and in the later firewall steps.
Next, open the Surface Area Configuration and click on MSSQLSERVER, expand the Database Engine, and click on Remote Connections. Make sure that the "Local and remote connections" option is selected. In the submenu, you can select either "Using TCP/IP only" or "Using both TCP/IP and named pipes"
Now an important step that is often overlooked and can be the source of much confusion.
Open the Services by going to Start-> All Programs -> Administrative Tools -> Services
Make sure that SQL Server and SQL Browser services are started and are set to start automatically. Even if all the settings are correct, if the Browser service is not started, it will fail.
Now I need to configure my firewall to allow the connection. I am using a Netgear FVX358.
Click on "Security" and then on "Services"
I will add a service and name it "Real Data Plus". I will make it a TCP type and since I only want to open one port, I will make the "Starting Port" and "Ending Port" the same, the default 1433. If you used a custom port in the SQL settings, you will need to use the same custom port here.
Now, I navigate to the "Firewall" menu, still under the "Security" settings.
Click on the "LAN WAN Rules" tab. Under the "Outbound policy" add a new entry. Be sure to name it the same as the Rule in the above step, in my case "Real Data Plus". Enter the internal address you want the traffic to be redirected to and the external ip the traffic will be coming from.
*TIP: For added security, specify the IP address or range that the traffic will be coming from. The firewall will block all others.
To test to make sure a connection can be made through the external IP open a command prompt and type the following command without the quotes: "sqlcmd -S 198.113.113.113,1433 -U sa" (Of course, use your own IP and port number) You will prompted for a password. Once you enter it, if you get the 1> prompt, you have successfully been connected. Type "EXIT". If you do not get this prompt, you are not able to be connected so something is not opened properly.
To test the port forwarding through the firewall, I used the website http://yougetsignal.com/to
If all goes well, you will now be able to set up your ODBC using the external IP and port. Happy databasing!
by: Bilbo3D on 2011-04-09 at 19:29:39ID: 25630