mySQL and External Database Logging

 

Introduction

Automate Enterprise can log status and messaging events to an external database provider such as Microsoft SQL Server, Oracle or mySQL. The document explains how to setup a mySQL data source connection in Automate where system events and messages can be logged.

While there appears to be a number of steps, the bulk of the work involves setting up an initial ODBC connection to the mySQL database, which is a necessary procedure in Microsoft Windows to execute SQL statements on database engines provided by various database vendors.

What Will You Need

1. A properly configured and running mySQL installation
2. An existing and preferably empty database or schema
3. Any necessary information required to connect to the database, such as a valid username and password
4. Completed the installation of the mySQL ODBC Drivers for Windows, which can be found here: mySQL Connector/ODBC Driver

Note: This article was written and tested against version 3.51 of the mySQL ODBC driver.

Configuring External Database Logging

The following instructions explain how to enable and configure External Database Logging to an existing mySQL database schema located on a local or remote system:

1. In the Automate Task Administrator, select Options from the System menu. Navigate to the Logging tab. Check on the "Use external database logging" checkbox (this will be disabled if you are not running Automate Enterprise)
2. Click the Configure button. This will help build the connection string through a set of wizards.
3. On the "Data Link Properties" window, select the Provider tab, and select "Microsoft OLE DB Provider for ODBC Drivers". Click Next.
4. Select the "Use connection string" radio button, and click the Build button to the right of the editbox.
5. Choose the "Machine Data Source" tab and click the New button near the bottom of the dialog.
6. From the "Create New Data Source" dialog that appears, locate the "mySQL ODBC Driver" entry and click Next.
7. Click Next, verify the information is correct, and click Finish.
8. At the mySQL Provider properties, name the Data Source entry. This will be used to identify the connection in the future so it can be used again by Automate (or any other application that an interact with this mySQL schema) to connect to the correct database. Enter the hostname of the database being connected to. For this example, use localhost for "Host/Server Name (or IP)" (although if the database was located remotely, the hostname or IP address would also work), database name (for example, "Automate6Log", and the user name and password with read/write access to the database. You may want to test the parameters by clicking the Test button. Click OK.
9. Back at the Data Source builder, fill out the user name and password using the same information you provided on the mySQL dialog. Be sure to enable the "Allow saving password" checkbox (see below for important security considerations). The rest is left blank so the default values are used. (NOTE: Do not fill out the "Default catalog" dropdown of this dialog. A bug in the mySQL driver causes connections to fail if a default catalog is provided.) Test the information supplied is valid by clicking "Test Data Source". If everything is acceptable, Click OK.

Database logging is now configured. Automate will attempt to create the necessary tables upon startup if they do not exist. Errors that occur with the initialization or use of the database are written to the Windows Application Event Log accessible in the Control Panel - Administrative Tools.

Security Considerations

The Automate Task Service (which runs in a privileged account called LocalSystem) and the AMTask.exe process (which wraps a running task and runs in the context of a user account) require read/write access to the database. Further, the process of configuring a database connection string requires the database password be stored in the registry in cleartext.

Therefore, it is strong recommended that a secured account is used together with the mySQL database. The root or administrator password to the server or scheme should be avoided. Ideally, a special user should be created exclusively for use by Automate's logging engine. This would help prevent a sensitive password from being exposed or a compromised Automate installation from abusing it's database privileges.

Applies To: Automate 5, Automate 6 

No, open a new Support Case