Connection to Remote MS SQL Server 2005 Over The Internet

I have been working on this issue and want to share it out.

First of all, go download and install MS SQL Server 2005 Express Edition.

Step by step guide for setup of connection to remote MS SQL Server 2005 instances over the Internet.

It involves several steps. (It is important that you follow all the steps)

1. SQL Server Surface Area Communication Manager

You need to configure this to the remote MSSQL Server 2005. For this part, Microsoft actually narrated it clearly. Just follow this article.

Windows Firewall are deployed mostly by default in Windows XP and Windows Vista, except for Windows 2003 Server. If you have it installed and enabled, be sure to follow the step as described in the article. Otherwise, you may just choose to turn-off the firewall.

2. SQL Server Configuration Manager

You need to configure this part to the remote MSSQL Server. This portion is described too generically by Microsoft.

Ok, assuming that you follow default installation settings, where you are currently having a named instance called "SQLEXPRESS".



Ok, take a look at this article.

What it means is ...



You must follow exactly where you have to set the IP (IP1 in this case) to a valid IP (the IP which your computer or server is currently running; an IP which can be ping). Take note that if you are running the computer or server on dynamic IP mode, MSSQL Server 2005 will automatically assign a random IP for you in this scenario, u have to remove it as the random IP will have no use (It cannot be ping). If you have to use dynamic IP, you have to at least set the IP to the dynamic IP which your computer or server is currently running (i.e 192.168.1.101)

Be sure to clear the TCP Dynamic ports to blank instead of 0.

You are done for this part. Now u have to proceed to port forwarding at the router of the remote site.

3. Port Forwarding

Assuming you are using D-Link AirPlus G+ high-speed 2.4GHz router as for this scenario.

Create an entry to "Virtual Server"



Set the private port to the port you set earlier (which is 1475), the public port can be any number (in this case is also 1475).

Which it will then automatically create the following entry



Also have to create another port forwarding under "application", as according to this article.



Also, it would be better if you register a dynamic DNS if you are running on a dynamic WAN IP.



Ok, the port forwarding settings have been done.

4. Connecting to Remote Site

Ok, now that the remote site has been configured, you need to try to connect to it.

The most important thing is the connection string format. There are two formats.



Therefore, for this scenario, you can connect to the remote server (which you have configured above) using connection string of "seiya1.dyndns.org\sqlexpress,1475"

5. Linked Server

Now that you are able to connect to the remote MSSQL Server 2005, you can basically run SQL queries on it.

The proper concept is to use "OPENQUERY(T-SQL)" and "linked server".

Thus, to setup link server.



Ok, now you want to fill up the form the connection. Refer to this article for guidelines.

Basically...



where

Name = Any name
Provide = "Microsoft OLE DB Provider for SQL SERVER"
Product = "SQLOLEDB"
Data Source = Connection string mentioned earlier.

Woohoo, you are done!

What is SQL Native Client Protocol?

According to this article...

What it is trying to say is that..

It is used by client (i.e Management Studio) to connect to MS SQL server instances.

This means that by setting a 'aliases', you don't have specify the port number in the connection string.

Thus, reverting back to this scenario ...



And you can try to connect to it again using...



You don't have to specify the instance name because the port will point to the correct instance.

What is SQL Browser Service?

According to this article, this service is useful to help you to lookup for the instances of MSSQL Server 2005. Means, if you don't provide the port, by just stating the IP and instance name, it will help you to look for the right port number and connect on your behalf. (This is if the browser service is enabled earlier).

However, if we try to do that for this case, it means that the connection string = "seiya1.dyndns.org\sqlexpress" --> without the port number.


However, it doesn't work for this case because we need the port number for port forwarding. Remember that in this scenario, we set the public port = 1475 and private port = 1475.

Unless there is a way to do port forwarding for SQL Browser Service, otherwise it is mostly useful for situation which doesn't require port forwarding such as LAN or MPLS (Multi Protocol Label Switching) environment.

Upgrade to MS SQL Server 2005 SP2 for Express Edition ?

According to this article, there is no upgrade files for Express edition. You will have to reinstall from scratch.