Advanced Topics - Database - Remote Access

Secure, Remote Read-Only Access Using MOVEit DMZ API

The easiest and most secure way to pull information out of the MOVEit DMZ configuration and audit database remotely is to use MOVEit DMZ API's "ReportRunCustom()" method and custom queries. This method requires users to authenticate with MOVEit DMZ credentials, protects data in transit with SSL and is firewall friendly because it uses HTTPS on port 443.

For example, to get a list of at most 5 users whose full names start with "A" in XML format, you could use the following VB code with MOVEit DMZ API.

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 DMZ API documentation for more information. (You do not need to "pre-configure" custom reports on MOVEit DMZ to use them from MOVEit DMZ API, but the DMZ "Custom Reports" documentation contains a complete explanation of how each of the fields used in the API function call are used.)

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

Establishing Secure, Remote Access to MOVEit DMZ's database

If Your Database Engine is MySQL

To securely access the underlying MySQL database MOVEit DMZ 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 DMZ 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 DMZ and MOVEit Central). However, several other MySQL database drivers, including several non-ODBC drivers for .NET, have been tested as well.

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

A complete set of instructions to do remote reporting using this tool (including stunnel CLIENT instructions) can be found in the MOVEit Central manual.

Set Up Read-Only Database User on MOVEit DMZ

In most cases you will want to set up a read-only user to access the MOVEit DMZ database. While certain tweaks of the database are allowed, unless you really know what you are doing it would be easy to wreck your configuration and/or run afoul of MOVEit DMZ's tamper-evident check feature. (This is essentially the same reason why other software vendors who store configurations in custom flat-file formats instead discourage people from hacking in with their favorite hex editors; yes, it's possible, but watch out!)

Open a command prompt on MOVEit DMZ.
CD into the "Bin" subfolder of your MySQL root folder. (e.g. "D:\MySQL\Bin")

Run this 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.

Once at the "mysql>" command prompt, issue the following commands (substitute "micentral" and "m1c3ntra1" with the username and password you would like 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

Finally, test the permissions you just set up with the following commands (substitute 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 DMZ

Open a command prompt on MOVEit DMZ.
CD into the "Util" subfolder of your MOVEit DMZ root folder. (e.g. "D:\MOVEitDMZ\Util")

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 1096

Run this command (there is no need to change the configuration on this server):
```
D:\moveitdmz\util>stunnel stunnel_mysqlserver.conf
```
If this command fails, you will see an error dialog box which says something like "Stunnel server is down due to an error..." and then a larger dialog box which contains details about the error. Whether or not this command succeeds, you will see a small STunnel icon () in your tray.
If all is well, you are ready to move to the next section. If there were problems, right-click on the STunnel icon and select the "Exit" menu item.

Install stunnel As a Service

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

Exit the stunnel application on both MOVEit DMZ and MOVEit Central.
On MOVEit DMZ, execute the following command to install the service (watch for "Created service" pop-up):
```
D:\moveitdmz\util>stunnel -install stunnel_mysqlserver.conf
```
On MOVEit DMZ, execute the following command to start the service:
```
D:\moveitdmz\util>net start stunnel
```

If Your Database Engine is Microsoft SQL Server

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, simply include the following in your connection string:

TrustServerCertificate=yes; Encrypt=yes;