Page Comparison

...

It connects to the associated production instance as root, so it provides nearly unrestricted to the instance and is therefore very insecure and dangerous.

The main steps to configure the data edit tool for a specific PC are:

...

This page contains the following information:

Configure Putty

Download PuTTY from http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

...

Note that all the options below are defaults.  I'm including it in here in case they change.

Configure Auth Category

Here you most specify where the key file is used for security:

Image Removed

Configure Tunnels Category

Image Removed

 Image Added

Note: If you're using AWS and thus have a .pem rather than .ppk file, the "Converting Your Private Key Using PuTTYgen" section of Amazon's guide may be helpful.

Configure Tunnels Category

Postgres uses port 5432, so the source port is 9738 (this port will be used when configuring the ODBC connection later) and the destination port is localhost:5432.

Note that the port mapping for instances running in a docker container are located in: /wiki/spaces/IG/pages/12845102.

Image Added

Make sure to save this configuration so you can load it later.

Configure ODBC Connection

The Access Application is configured to use a DSN named SELV32.  So, we need to create that DSN using a postgres driver and the port we just created.

1. Control->Panel search for ODBC and choose "Set up ODBC data sources (32-bit)"
2. Select the SystemDSN tab and choose Add...
3. Select the PostgreSQL Unicode driver
4. Enter the following
   1. Data Source: SELV32
   2. Database: open_lmis
   3. Server: localhost
   4. User Name: postgres
   5. SSL Mode: prefer
   6. Port: 9738 (the port we entered into PuTTY)
   7. Password: p@ssw0rd
      Image Added
   8. Save

Your ODBC connection is now setup.

Run SELVeditor

The actual SELVeditor tool is an Access Database configured to connect to the open_lmis database and manipulate the data collection tables.  You only have to configure everyone once, so running it is simpler.

1. Run PuTTY
1. Select the correct configuration that you saved above
2. Select Load
3. Select Open
4. The following window will open and you can just minimize it.  It's important to NOT do anything in the window.
   Image Added
2. Load the Access Database - SELVeditor
   1. The system will remind you to run PuTTY.  Click the initialize button.
      Image Added

You can now use the Data Edit tool to change distribution data.  Note, that information is changed immediately in the production database with no record of the change.  Be very careful with this tool.

Troubleshooting

Symptom: You can SSH into the target machine but can’t connect to PostgreSQL

1. If PostgreSQL is hosted natively on an EC2 instance, verify that the instance belongs to a Security Group which exposes port 5432. This can easily be done via Amazon's Management Console.
   
2. Follow the steps here to ensure that PostgreSQL is configured to allow remote connections. Note that, for productions systems, these restrictions should not be cavalierly relaxed. The relevant configuration files for our current deployments of OpenLMIS reside in /var/lib/pgsql/9.2/data/
   

Symptom: Upon trying to connect to a remote database within Access, you encounter “Run-time error: 3151. ODBC—connection to SELV32 failed.”

1. Within Access, go to 'External Data' -> 'Linked Table Manager' (this might be in a different place in older Access versions, see http://www.addintools.com/documents/access/where-is-linked-table-manager.html). In the window that opens, select all tables, then choose 'Ok.'

2. If the above doesn't work, bring up the Linked Table Manager within Access, select all tables and click “Always prompt for new location” (see screenshot below). On the resulting dialog, switch to the “Machine” tab and select the ODBC data source for SELV.  

   Image Added