Browse All Articles > 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.
***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!
***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!
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.
Comments (6)
Commented:
Further, if you change IP settings for MSSQL, you need to restart the services to have them applied.
Author
Commented: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!
Commented:
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.
Commented:
Commented:
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