Sharing a Database Over a Virtual Private Network (VPN)

 When two computers are connected, they make a network.  When they connect over the internet, they make a virtual network, and when that connection is secured, you have a virtual private network (VPN).

 

In most ways, a VPN will look just like a LAN.  Your computers will all be shared on a network, with the only difference being that the network involves remotely connecting through the internet.

 

Setting up a VPN, involves installing software on each computer.  There are many different VPN offerings available.  We will use a very simple one (LogMeIn Hamachi) as an example.  

 

 

To share a database on LogMeIn's Hamachi Virtual Private Network:

1. The first step is to install Hamachi on one of the computers that will share the database.    The download can be accessed from  https://secure.logmein.com/products/hamachi2/download.aspx.  If you already have a LogMeIn account and network setup, it can be used, but it is not necessary.   From the link, choose the "Unmanaged" mode and click the Download Now button.   Follow the steps to install.
2. Once installed, Hamachi will open.  Click the Power button to begin.

3. When prompted for a client name, enter a name that identifies this computer and click the Create button.

4. The Hamachi window will load your new IP address.  Click the Create a new Network button.

5. Enter the Network ID and Password. The Network ID must be unique across all other users Hamachi networks.

6. The network is now complete.  The Hamachi window will appear.

7. Repeat steps 1-3 on each computer that will share the database.   Instead of creating a network though (as performed in Step 4), click the button to Join an Existing Network and provide the name of the network that was created earlier.

9. Under SQL Server Network Configuration, choose Protocols for SIXBITDBSERVER, then right click TCP/IP on the right and choose Properties.

10. On the IP Addresses tab, find the IP Address assigned to this computer by Hamachi.  Make sure Enabled is set to Yes.  Click OK.
11. On the main panel, choose SQL Server Services, then right click SQL Server Browser and choose Restart.  When finished, repeat the process to restart the SQL Server (SIXBITDBSERVER).  Exit SQL Server Configuration Manager.

11. Next, ensure that Hamachi was set up as a Home network.  From Start|Control Panel|Network and Internet|Network and Sharing Center, make sure the Hamachi network is set as Home network. If it is set to anything else, click it and change it to home network.

12. Now, we need to enable SQL Server through your firewall.  Both the SQL Server program and SQL Server Browser must be added to your firewall exceptions list.  In addition, a few ports must be opened.  If using Windows Firewall, you can follow these steps to allow the proper access.  If using any other firewall, follow the example here to allow the two programs and two ports.

    1. On the database server, from Windows Control Panel, start Windows Firewall.

    2. On the Exceptions tab, click the Add Program... button.

       

    3. First we'll add SQL Server to the exceptions list.   Browse to the location of the instance of SQL Server that you want to allow through the firewall.  For example, SQL Server 2008 RS with an instance name of SIXBITDBSERVER is located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.SIXBITDBSERVER\MSSQL\Binn.  You may need to change the MSSQL10_50.SIXBITDBSERVER depending upon the version of SQL Server you have installed.  Select the file named sqlservr.exe and click Open, then OK.

    4. While still in Windows Firewall, click the Add Program... button again.  Browse to the location of your SQL Browser.  This may be different depending upon current and past versions of SQL Server that have been installed.  For example, you may find it at C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe. If not there, try replacing the 90 with 100.  If you still cannot find it, do a search for sqlbrowser.exe.    Select the file named sqlbrowser.exe and click Open, then OK.

    5. Now, we will add the ports.  On the Exceptions tab, click the Add Port... button.

       

    6. Enter SQL TCP 1433 as the name, and 1433 for the port number.  Choose TCP for the protocol.

    7. Click the Change scope... button and select My network (subnet only). Click OK, then OK again.

    8. On the Exceptions tab, click the Add Port... button again.  This time, Enter SQL UDP 1434 as the name, and 1434 for the port number.  Choose UDP for the protocol.

    9. Click the Change scope... button and select My network (subnet only). Click OK, then OK again.

    10. Exit the Windows firewall settings window.

    11. Return to Step 6 above and open the Database drop down and you should see a list of databases.

13. Now your network is all set up and you are ready to share the database.
14. On each computer that is not the database server, you will specify the location of the shared database.  From the File menu, select Manage|Open Database.  The Open Database window will appear.
15. From the Open Database window, enter the Hamachi assigned IP Address of the computer hosting the database.  
16. Enter the SQL Instance to open.  Multiple copies of SQL Server can be installed on any given computer.  Each copy is called an instance.  A list of instances on the selected computer will appear in this dropdown.  By default, SixBit is installed to the SIXBITDBSERVER instance.
17. Click  "Use SQL Server Authentication" when connecting with a VPN.   By default, SixBit uses a userid of "sa" and a password of "S1xb1tR0x".  Versions prior to 1.00.052 used a password of "sixbit".  
18. A list of databases in the selected instance will appear in the Database drop down.  Choose the database to open.  By default, SixBit is installed with the name "SixBit".  Click OK when finished, and you will be connected to the database on the server.