External Database Logging Configuration

With AutoMate Premium, you have the option to enable database logging. AutoMate events are written to an external datasource such as Microsoft SQL Server, Oracle or mySQL. The datasource can be configured using a standard OLE DB connection string dialog. AutoMate will automatically create and maintain the tables in this database when logging starts. The AutoMate events can then be queried against any other database in your database server. Furthermore, you can customize the level of logging that is done to the database server.

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. However, while this example uses mySQL, AutoMate supports the use of any database system, provided the machine running AutoMate has installed a valid OLE DB or ODBC driver that allows access to that database. Although there appears to be a number of steps, the bulk of the work involves setting up an initial ODBC connection to the database, which is a necessary procedure in order to execute SQL statements on database engines provided by various database vendors.

Requirements


Below contains a list of requirements that need to be met in order to ensure proper external database configuration:

  1. A properly configured and running mySQL (or compatible database system) 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. Proper installation of the mySQL (or compatible database system) ODBC Drivers for Windows.

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

Configuring External Database Logging


  1. In the AutoMate Task Administrator, select Options from the System menu and navigate to the Logging tab and add a check to the option labeled Enable database logging.  

  2. Click the Configure button. This opens a dialog titled Data Link Properties which 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" (as shown below) then click Next.

  1. From the Connection tab, select the Use connection string radio button, and click the Build button to the right of the edit-box, as illustrated below. This opens a new dialog titled Select Data Source (shown below).

  1. From the Select Data Source dialog, choose the Machine Data Source tab and click the New button near the bottom of the dialog. This opens a dialog titled Create New Data Source.

  1. From the Create New Data Source dialog that appears, locate the "mySQL ODBC 3.51 Driver" entry and click Next.

  2. Click Next, verify the information is correct, and click Finish.

  3. 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 will 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)" (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 thereafter.

  4. 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 strongly 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.

See Also

Data Link Properties

Logging Options

Automate Event Log

Viewing the AutoMate Events Log