<

How to allow ODBC to connect to SQL via and external IP

Published on
27,736 Points
17,436 Views
3 Endorsements
Last Modified:
Awarded
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.
 1SSSMProperties.png

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.
 3ConnectionManagerTCPIP.png

Right click on the "TCP/IP" and select Properties.
 4PropertiesDialog.png

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.
 6TCPIP-IPAddresses.png

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"
 7SurfaceAreaConfigRemoteConnecti.png

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.
 8Services.png

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.
 9RouterServices.png

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.
 10RouterFirewallMenu.png 11RouterFirewallEntry.png

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.
 12SQLCMD.png

To test the port forwarding through the firewall, I used the website http://yougetsignal.com/tools/open-ports I entered the external address of 198.113.113.113 and the port 1433 and it came back to tell me that my port is opened or closed. This is useful, as then you know if it is a SQL problem or a firewall problem.
 13PortCheck.png


If all goes well, you will now be able to set up your ODBC using the external IP and port. Happy databasing!
3
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free