Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

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

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.

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 (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.
  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,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 I entered the external address of 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.

If all goes well, you will now be able to set up your ODBC using the external IP and port. Happy databasing!

Comments (6)

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

That setup has an unnecessary step - you need not to define the external IP in MSSQL, as communication will take part using the internal IP. Only if the public IP would be routed directly thru (like usually in a DMZ), that step would be needed.
Further, if you change IP settings for MSSQL, you need to restart the services to have them applied.


Thank you for your comments. I agree, you don't always have to specify the IP in MSSQL but doing so will eliminate any doubt that it is listening on the correct IP and port. Also, good point in the restarting of services. I should have mentioned that when I was checking to make sure the services were running. Thank you!
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

" but doing so will eliminate any doubt that it is listening on the correct IP and port!
Sorry, but that is not true at all. You can enter whatever you want there - if it isn't used, it has no value. I fear it is rather misleading then helpful.
Qlemo:can you plz explain Only if the public IP would be routed directly thru (like usually in a DMZ), that step would be needed.with a example..thanks

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

It's quite simple: If you have a public IP assigned on your MSSQL server, and that machine has more than one IP, and that public IP is routed thru the the Internet routing device to that machine, then you have to setup the public IP and port in the MSSQL network settings.
The same applies if you need another port (different from the "local" one) on the machine.

Any other scenario is handled automatically - MSSQL binds to all network adapters (and their first IP address), with the default port or one determined automatically, if not configured to be fixed.

In short: If the router does all the work, that is translatiing the public IP and port to the internal IP and port, you do not need to configure a second IP in MSSQL network config. And that is the configuration used most of time.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.