Connecting to FileCatalyst Central Database
Posted by Chris Kuhlman, Last modified by Aly Essa on 28 June 2017 07:45 AM

Overview

With the release of File Catalyst Direct 3.0.1, Central will provide the ability to enable external access to its database of transfer information.

First, a little basic information: Central uses a Derby database to store its data. This means that whatever tool you are planning on using to access the database will need to be able to interact with Derby. In order to do this, the tool must be able to use the Apache Derby Client driver (http://db.apache.org/derby). Our preferred client is a free Java based tool called SQuirreL SQL Client (http://www.squirrelsql.org/), which includes the Apache drivers by default. This is the tool that will be used in this guide. However, you can use any tool that supports the driver. You will just need to adjust your set up as appropriate for your chosen tool.


WARNING: The FileCatalyst Central Database is a critical component of the deployment and by connecting to it you are directly modifying it. We strongly recommend making a backup of the deployment before making any modifications. We will not be able to revert any changes that are being made in error.  

Environment

FileCatalyst Central v3.5 and later.

SQuirreL SQL Client.

Windows Environment.

 

Resolution

  1. Connecting SQuirreL to FileCatalyst Central DB.

    The first step is to configure Central to provide access. Currently, the configuration UI of Central does not allow for configuration of the database access, so we need to make the changes directly to the MAConfig.conf file which can be found in the install directory of FileCatalyst Central. Follow these steps to make the changes to this file:
    1. Shutdown the Central Service using the Service Manager (Services.msc).
    2. Once Central is shut down, open MAConfig.conf in a text editor and scroll down to the bottom. You are looking for the parameters:

      • FCMonitoringAgent.config.allow.external.database.access: This parameter determines whether or not the database will be accessible or not. Set it to equal true.

      • FCMonitoringAgent.config.derby.drda.host: This value is the address that remote hosts must use to connect to the database. It defaults to localhost, if no value is set, then it will prevent any non-local access to the database. This value can be any address that points to the FileCatalyst Central. If you wish to allow anyone access, as long as their address points to the correct machine, the value must be set to 0.0.0.0. In this case, the network server will listen on all interfaces.

      • FCMonitoringAgent.config.derby.drda.portNumber: This value is the port which clients use to connect to the server. The default value is 1527. This is the standard port for Derby databases. You can use any port number here that is available on the machine.

      • FCMonitoringAgent.config.external.database.username: This value is the username used for remote access. The default value is username. Because this value is loaded in from the configuration file and is not encoded in any way, it is limited with the characters that it can accept. It is recommended that you use a simple string of alphanumeric values. When this information can be modified from the UI, it will be encoded and thus able to be a more complex string.

      • FCMonitoringAgent.config.external.database.password: This sets the password for the remote access and defaults to password. It has all the same limitations as the username.

        Note: As with any FileCatalyst Central configuration value, if the values are not present in maconfig.conf, they will be set to the defaults.

    3. Once you have your values set, you can restart FileCatalyst Central service.

  2. Configuring a connecting client.

    1. For this article, we will be using SQuirreL as the client, but the basics should be similar for any client that supports the Derby driver.

      • In SQuirreL, we want to Add an Alias. This is what the new alias dialog looks like: 



      • As you can see in the illustration above, most of the values are pretty straight forward. The URL, however, is a little complicated because it contains most of the information that is used to connect to the database:

        jdbc:derby://<server>:<port>/<databaseName>;<URL attribute>=<value>

      • Here is an example of an alias tha thas been filled out:



      •  Click Test to verify your connection and that it is connecting successfully. Hit OK when the test is complete. At this point you should see the Name you entered in the aliases list:



      • Double click on the Alias and SQuirrL will open and Auto Logon to the database. This opens a tab that shows the contents of the database.



      • The only branch on the tree that we need to concern ourselves with is the MONITOR_DB branch. As mentioned above, this is the name of our database and this branch contains all of the tables with our data under TABLE. However, before we are able to access our data, we have one more step.

        In Derby, the database assumes that you want to connect to a schema within the database that is the same as the username you have used to connect. In this case, it assumes that we want to connect to a schema within the database named USERNAME as that’s the username we used in the MAConfig.conf. Not only is this not the schema we want, it doesn’t even exist. While we can open the schema with the above viewer and examine the contents, we do not have the ability to make queries which is likely the reason you wanted to connect to the database in the first place.
    2. In order to access the correct schema we need to switch to the SQL tab and enter the command:

      SET SCHEMA MONITOR_DB

    3. After we have entered this command we can now retrieve data from the database. The external user is read only and only selects may be done.

  3. Here is our current Schema: 

Management Schema for FileCatalyst Central