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.

How to: Start SQL Browser Service

 When using a named database instance for Dynamics NAV, and the SQL Server is not on the same computer as Microsoft Dynamics NAV Server, then the SQL Server Browser Service must be running on the SQL Server.

 If it is not, Microsoft Dynamics NAV Server cannot connect to the Dynamics NAV database and an error occurs when you try to open a RoleTailored client. A database instanced named NAVDEMO is used when you install the CRONUS International Ltd. demonstration database by using Microsoft Dynamics NAV 2018 Setup.

You enable and start the SQL Server Browser Service by using the SQL Server Configuration Manager that is installed with Microsoft SQL Server 2008.

To start SQL Server Browser Service

1. On the computer running SQL Server, run the SQL Server Configuration Manager.

   On the Start menu, in the Search Programs and Files box, type SQL, and then choose SQL Server Configuration Manager.

2. In SQL Server Configuration Manager, in the navigation pane on the left, choose SQL Server Services.

3. Right-click SQL Server Browser, and then choose Properties.

4. On the Service tab of the SQL Server Browser dialog box, set the Start mode to Automatic.

5. Choose OK to return to the SQL Server Configuration Manager main page.

6. Right-click SQL Server Browser again, and then choose Start.

Change authentication mode with SSMS

 

1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

Enable sa login

You can enable the sa login with SSMS or T-SQL.

Use SSMS

1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.

2. On the General page, you might have to create and confirm a password for the sa login.

3. On the Status page, in the Login section, click Enabled, and then click OK.

Adding Users and Setting Permissions for the SQL Database

 After creating the database that contains the Sterling Gentran:Server® tables, you need to add users to your database management system and give the users permission to access the Sterling Gentran:Server database.

About this task

Use this procedure to add users in SQL server and give them access to the database.

Procedure

1. From the Start menu, select Programs > SQL Management Studio.

   The first time you execute the SQL Server Enterprise Manager, you are prompted to register the SQL server.

2. Select Microsoft SQL Server.
3. Select your server name and expand.
4. Select Security.
5. Right-click on Logins and select New. Enter the username.
6. To set permissions, double-click the user account and do one of the following:
   • If you are using SQL Authentication, enter the user name.
   • If you are using Windows Authentication, select Windows Authentication and select either domain or local.
7. Change the default database to GentranDatabase.
8. On the left side, select Server Roles. On the right side, select public and sysadmin.
9. On the left side, select User Mapping.
10. On the right side, select GentranDatabase.
11. Under Database Roles, select public and db_owner.
12. Click OK.

How do I grant view permissions so a SQL Login can see only one database?

 I want to create a database (SAMPLE) with an system administration account and then access it using a SQL Login, similar to a service account.

That SQL Login should not be able to see the other databases in my server instance. Online, there are many articles that offer one of three variations on a solution.

Option 1. do the following a) DENY ANY DATABASE to the user and then b) grant permissions on the database in the User Mappings. This does not work.

Option 2. do the following a) DENY ANY DATABASE to the user and then b) ALTER AUTHENTICATION on the database. This works, but changes the ownership of the database to the user SQL Login. I don't want this. The system admin account used to create the database needs to remain its owner. The SQL Login should be able to view it.

Option 3. involves using "contained" databases, which is not an option for me.

Conceptually, I am thinking there should be a fourth option that looks something like:

Option 4. do the following a) DENY ANY DATABASE to the user and then b) GRANT VIEW ON DATABASE::SAMPLE TO SAMPLE_USER. This would deny blanket visibility while restoring itemized visibility, but I have not figured it out.

Testing Results:

This is what the SAMPLE_USER sees when I implement Option 1. It does not expose the SAMPLE database to the SAMPLE_USER Login, even though it is mapped. This does not work.

This is what SAMPLE_USER sees when I implement Option 2. This works, however, it changes the database ownership which I cannot allow.

I am going to add a follow on with details of how this is configured. But, the question is, how do I grant view permissions to ONE database that does not change the ownership of that database?

--------------------------------------------------------------------------------------------------------------

Answer:

Bottom line: We cannot limit what the SQL Login sees without changing is role in an unacceptable way, but we can control what it can access.

https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/

Per the article,

Conclusion
…there are limited options to hiding databases. Once you hide all databases the only logins that can see the databases are the logins that are the owners of the database or if the login is a sysadmin. Also, each database can only have one owner, so you can’t assign multiple owners to the same database.

Solution:

Start with a database (SAMPLE), a database level user (SAMPLE_USER) that is linked to a server level login (SAMPLE_LOGIN).

Use the server level login (SAMPLE_LOGIN) to set its Mappings to include the SAMPLE database.

Here you can see what my SAMPLE_LOGIN sees at the server level (everything) while it throws an error if I try to access any database…

In contrast, when SAMPLE_LOGIN tries to access a database that it is mapped to (as SAMPLE_USER is a database level user account that links to the server level SAMPLE_LOGIN), it can see the objects inside that database that have been granted to it under database user’s Securable permissions.

 

Find your IP address in Windows

For Wi-Fi connection

1. Select Start > Settings > Network & internet > Wi-Fi and then select the Wi-Fi network you're connected to.

2. Under Properties, look for your IP address listed next to IPv4 address.

For Ethernet connection

1. Select Start > Settings > Network & internet > Ethernet.

2. Under Properties, look for your IP address listed next to IPv4 address.

Invalid SSH Key

 

Problem

When adding an SSH public key to Bitbucket Cloud, the following error appears: Invalid SSH Key or Invalid SSH Key (ssh-keygen).

Cause

This error means that the data copied into the form was not able to be parsed to extract something that "looks like an SSH key". The cause of this error is that the SSH key is corrupt in some way and data pasted into the key field was modified on disk or copied incorrectly. Sometimes, this is due to line breaks being copied. Many tools will wrap the display of an SSH key. Simply highlighting and copying the key like this may copy line breaks depending on which text viewer you are using.

Diagnosis

Follow these steps to test the key on your workstation:

Diagnostic Steps:

You can test this locally by doing the following:

1. Create your SSH key

2. Use ssh-keygen to validate the key

   ssh-keygen -l -f temp.pub

3. Now, copy the SSH key exactly as you had when attempting to add it to the site
4. Paste the contents into a new file. Call it temp.pub

5. Try to use ssh-keygen again to validate the key.

   ssh-keygen -l -f temp.pub

At this point, you should have a response indicating if either is invalid.  

Resolution

Invalid key

If the copy is invalid, see the instructions in the above issue for copying the key properly.  If both are invalid, it is possible the tool you are using to create the key is not configured properly. Try starting from the beginning of our SSH keys guide and see if starting from scratch when setting up SSH helps.

If you continue to have issues, please contact support with the output of ssh-keygen as well as your platform and what you are using to create the key.

To copy the Existing Public Key

Once you have confirmed this is the key has not been corrupted, here are the instructions to copy the ssh public key again.

Windows

1. From your avatar in the bottom left, click Bitbucket settings.
   Bitbucket displays the Account settings page.
2. Click SSH keys.
   The SSH Keys page displays. It shows a list of any existing keys. Then, below that, a dialog for labeling and entering a new key.
3. Switch to your local desktop and start the PuTTYgen program.
4. Press Load.
5. Navigate to and open your default private key.
6. Enter your passphrase when prompted and press OK.
   The system displays your public key.
   
7. Select and copy the contents of the Public key for pasting into OpenSSH authorized_keys file field.
8. Back in your browser, enter a Label for your new key, for example, Default public key.
9. Paste the copied public key into the SSH Key field:
   
10. Press Add key.
    The system adds the key and it appears in the SSH Keys listing.
11. Close PuTTYgen.

OSX/Linux

1. From Bitbucket Cloud, choose avatar > Bitbucket settings from the application menu. 
   The system displays the Account settings page.
2. Click SSH keys.
   The SSH Keys page displays. If you have any existing keys, those appear on this page.

3. Back in your terminal window, copy the contents of your public key file.
   For example, in Linux you can cat the contents.

   $ cat ~/.ssh/id_rsa.pub

   In Mac OS X, the following command copies the output to the clipboard:

   $ pbcopy < ~/.ssh/id_rsa.pub

4. Back in your browser, enter a Label for your new key, for example, Default public key.

5. Paste the copied public key into the SSH Key field:
   
6. Press Add key.
   The system adds the key to your account. Bitbucket sends you an email to confirm addition of the key.