Previous Topic

Next Topic

Book Contents

Book Index

Database - Remote Access

Secure, Remote Read-Only Access Using MOVEit Transfer API

To remotely access information from the MOVEit Transfer configuration and audit database, use the MOVEit Transfer API "ReportRunCustom()" method and custom queries. This method requires users to authenticate with MOVEit Transfer credentials, protects data in transit with SSL and is firewall friendly because it uses HTTPS on port 443.

Example

This example uses VB code with the MOVEit Transfer API, to return a list in XML format that contains a maximum of 5 users whose full names start with A.

Dim boolOK as Boolean = false
Dim fields as String = "Username,RealName,Email,Notes"
Dim tables as String = "users"
Dim criteria as String = "RealName LIKE 'A%'"
Dim groupings as String = ""
Dim order as String = "RealName"
Dim limit as String = "5"
Dim OutputFormat as String = "XML"
Dim LocalPath as String = "d:\reports\newest_5_A_users.xml"
boolOK = ReportRunCustom( fields, tables, criteria, groupings, order, limit, OutputFormat, LocalPath )
IF boolOK then...

See Web Interface - Reports - Custom Reports and the MOVEit Transfer API documentation for more information. It is not necessary, to pre-configure custom reports on MOVEit Transfer to use them from the MOVEit Transfer API, but for information about how each field in the API function call is used, see Custom Reports.

If you want read/write access to the database that cannot be achieved through the MOVEit Transfer API, use a more complex solution that involves direct access to the database.

Establishing Secure Remote Access to the MOVEit Transfer database

To securely access the underlying MySQL database that MOVEit Transfer uses to store its configuration and audit logs through a an ODBC connection, you must set up a user in the MySQL database and set up a secure channel using stunnel.

Remote access to the MOVEit Transfer database may also require the 3.51 version of the MySQL ODBC driver (MyODBC), available at no charge from MySQL (and installed by default with MOVEit Transfer and MOVEit Automation). However, several other MySQL database drivers, including several non-ODBC drivers for .NET, have also been tested.8.

These instructions set up a secure MySQL port listening on TCP port 33062. Modify the necessary stunnel configuration file to change this value.

For more information about remote reporting using this tool, including stunnel CLIENT instructions, see the MOVEit Automation documentation.

Set Up Read-Only Database User on MOVEit Transfer

In most cases, set up a read-only user to access the MOVEit Transfer database.

Caution: Making changes to the database is an advanced task. Attempting this procedure without sufficient experience could cause configuration errors and affect the MOVEit Transfer tamper-evident check feature.

  1. Open a command prompt on MOVEit Transfer.
  2. CD into the Bin subfolder of your MySQL root folder. (e.g., D:\MySQL\Bin)
  3. Run the following command (using the appropriate root password):

    D:\mysql\bin>mysql --user=root --password=31r00t0
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 19660
    Server version: 5.0.44-classic-nt MySQL Enterprise Server - Classic (Commercial)

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

  4. At the mysql> command prompt, issue the following commands, substituting micentral and m1c3ntra1 with the username and password you want to use when connecting from your remote clients):

    mysql> GRANT SELECT,CREATE TEMPORARY TABLES ON moveitdmz.* TO 'micentral'@'localhost' IDENTIFIED BY 'm1c3ntra1';
    Query OK, 0 rows affected (0.00 sec)

    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)

    mysql> exit
    Bye

  5. To test the permissions you just set up, issue the following commands, substituting appropriate credentials where necessary.

    D:\mysql\bin>mysql --user=micentral --password=m1c3ntra1 moveitdmz
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 19660
    Server version: 5.0.44-classic-nt MySQL Enterprise Server - Classic (Commercial)

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> select count(*) from users;

    +----------+

    | count(*) |

    +----------+

    | 42 |

    +----------+

    1 row in set (0.08 sec)

    mysql> insert into log set ID=1;
    ERROR 1044: Access denied for user: 'micentral@localhost' to database 'moveitdmz'
    mysql> exit
    Bye

Set Up STunnel Server on MOVEit Transfer

  1. Open a command prompt on MOVEit Transfer.
  2. CD into the Util subfolder of your MOVEit Transfer root folder. (e.g., D:\MOVEitDMZ\Util)
  3. Run this command (and look for the following output):

    D:\moveitdmz\Util>stunnel_makecert

    D:\moveitdmz\Util>openssl genrsa -out stunnel_key.pem 2048

    Loading 'screen' into random state - done

    Generating RSA private key, 2048 bit long modulus

    ..............+++

    ....+++

    e is 65537 (0x10001)

    D:\moveit dmz\Util>openssl req -config stunnel_makecert_parms.txt -new -x509 -key stunnel_key.pem -out stunnel_mysqlserver.pem -days

  4. Run this command (there is no need to change the configuration on this server):

    D:\moveitdmz\util>stunnel stunnel_mysqlserver.conf

  5. If this command fails, an error dialog box opens with a message similar to Stunnel server is down due to an error... and then a larger dialog box that contains details about the error.

    Whether this command succeeds or fails, small STunnel icon (stunnel_icon.gif (904 bytes)) appears in your tray.

  6. If the command succeeds, continue with the next section of these instructions, below. If there were problems, right-click on the STunnel icon and select Exit.

Set Up STunnel As a Service

After testing your new connection, run the following commands to install stunnel as a service (so it starts automatically).

  1. Exit the stunnel application on both MOVEit Transfer and MOVEit Automation.
  2. On MOVEit Transfer, execute the following command to install the service (watch for Created service pop-up):

    D:\moveitdmz\util>stunnel -install stunnel_mysqlserver.conf

  3. On MOVEit Transfer, execute the following command to start the service:

    D:\moveitdmz\util>net start stunnel

If you have selected SQL Server as your database engine, you can securely access the data using standard Microsoft drivers, such as ADO and ADO.NET. To cause all communications between your program and Microsoft SQL Server to be encrypted, include the following in your connection string:

TrustServerCertificate=yes; Encrypt=yes;