Connecting to SQL Server over the Internet

         Once I had to analyze a SQL Server in a remote site every day, to do the analyzing I had to get a RDC to the machine or go to that site. This was so irritating for me because working with a RDC is like watching a slow-motion movie, and I don’t like to go outside in daytime ;-).

So I Google to find a way to connect directly to the all SQL server instances in all sites over the internet. There was some Q&A, blogs and some topics in MSDN but I couldn’t find an every steps to configure the SQL server, Firewall, Router in one place. So I decide to write this article for help newbies to the SQL Server.

 

How to: configure SQL Server to Connect Remotely.

1. Go to SQL Server Configuration Manager.
2. Select SQL Server Network Configuration.
3. Select Protocols for your Server Instance.
4. Double Click on protocol TCP/IP and go to IP Addresses Tab

If you have a default Instance you will see TCP Port set to 1433 in IPAll (Default instances of the Database Engine use TCP port 1433 by default.), if there are no port have set to TCP port, set it to 1433.

 

For the named instance SQL server dynamically assign an unused TCP port number the first time the instance is started. If that port is used by another application SQL server will assign an unused TCP port. So we can’t use a dynamic port to connect remotely to the SQL Server. Assign a new TCP port number to TCP port.

5.  Go to Protocol tab and Set Enabled to Yes. Click Ok

6.  Go to SQL Server Services Restart the SQL server.

7.  Start the SQL Server Browser Service (if u need to remote connections to the Dedicated Administrator Connection this service should be enabled.)

 

Now the SQL Server Configuration part is done.

How to: Configure a Windows Firewall for Database Engine Access

 

                To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access. To do so we need to create a firewall rule to open a port to connect with the SQL server

 

To create a new firewall rule using the New Rule wizard

• On the Start menu, click Run, type WF.msc, and then click OK.
• In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule.

• Select Port Click Next

• Enter the SQL Server Listening port(s) (this port is that we configured in SQL Server Configuration Manager, TCP/IP port) and click next.

• Select allow the connection, Click next.

• Select Appropriate profiles click next.

• Give a Name click finish

How to: configure a Router.

      Open the Port Forwarding or Applications and Gaming tab. Each router will be slightly different, but look for these two options. If you don't see them, try Advanced Settings and look for a Port Forwarding subsection. You will see something like below.

 

Application: Is the name (Give “SQL” or any name u like)

External Port: You will connect to the SQL server using this port

Internal Port: this should be the port that we configured in SQL Server Configuration Manager, TCP/IP port

Protocol: Select TCP

IP Address: IP address on the computer that is running SQL Server

Enabled: you know what this is right? ;-)

If you need more knowledge about port forwarding read this article.

Find the public IP

Open IE and go to Google type “My IP” and search, it will show your public IP. When we are connecting through the internet we will use this IP as our SQL server Name

 

How to: connect to a SQL server instance

 

               Now we have configured the SQL server and punch a hole on the firewall to connect to the SQL Server from outside and also we have configured the router to forward the request to the SQL server.

 

Open SQL Server Management Studio.

 

 

Type the public IP and the external port number (that you have set in router) in the Server name and give login details

You are done.

 

Click Connect…..