<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
26,025 Points
15,725 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
Comment
  • 3
  • 2
6 Comments
LVL 8

Author Comment

by:Coralie Crumrine
Thank you very much! :D
0
LVL 73

Expert Comment

by:Qlemo
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.
0
LVL 8

Author Comment

by:Coralie Crumrine
Qlemo,
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!
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LVL 73

Expert Comment

by:Qlemo
" 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.
0

Expert Comment

by:Sandeepiii
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

0
LVL 73

Expert Comment

by:Qlemo
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.
0

Featured Post

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month