External Database Logging Configuration

Overview

Automate's Log Management preferences enables the option of logging to an external database. This allows Automate events to be written to an external data source such as Microsoft SQL Server, Oracle or mySQL. Automate will automatically create and maintain the tables in this database when logging starts. Automate events can then be queried against any other database on the server. Furthermore, you can customize the level of logging that is most effective for your organization.

External database logging can be configured using a standard OLE DB connection string dialog. This article explains how to enable and configure external database logging to an existing database schema located on a local or remote system by way of the Data Link Properties dialog. While this article references only a few databases, Automate supports the use of any database system, provided the computer running Automate has a valid OLE DB or ODBC driver installed 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

Ensure that the following requirements are met in order to ensure proper external database configuration:

• A properly configured and running mySQL (or compatible database system) installation.

• An existing and preferably empty database or schema.

• Any necessary information required to connect to the database, such as a valid username and password.

• Proper installation of the mySQL (or compatible database system) ODBC Drivers for Windows.

Instructions

To Enable Database Logging

1. In Task Administrator, select Options > System Settings > Log Management.

2. Scroll down to, and check the option labeled Enable database logging, and then click the Configure button (shown below) to open the Data Link Properties dialog.

3. In the Data Link Properties dialog that appears, go to the Provider tab, and then select Microsoft OLE DB Provider for ODBC Drivers (as shown below) then click Next.

4. From the Connection tab, toggle the Use connection string radio button, and then click the Build button to the right of the edit-box (illustrated below). This opens the Select Data Source dialog.

5. From the Select Data Source dialog, choose the Machine Data Source tab, and then click the New button near the bottom of the dialog (shown below). This opens the Create New Datasource dialog.

6. From the Create New Data Source dialog that appears, locate the "mySQL ODBC 3.51 Driver" entry (as shown below) and click Next.

7. 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 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, "Automate10Log"), 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.

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 check-box (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 drop-down 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. Furthermore, the process of configuring a database connection string requires that the database password be stored in the registry in clear text. 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 schema 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.

Common Providers

The following displays the Connection tab and required parameters for some common providers:   

---

 

Microsoft OLE DB Provider for ODBC Drivers

Use Data Source:

Select your Data Source Name (DSN) from the list. Use the Refresh button to rebuild the list.

Use Connection String:

Advice: use a DSN. It's easier. If you must you can create a connection string here or type it in.

Allow saving password:

It is suggested to always tick this although I have only found it required for Oracle databases.

Test Connection:

Always test the connection before you continue.

Microsoft OLE DB Provider for SQL Server

Server Name:

Select or type your server name. This is the PC name or IP address on which the database runs.

Use Windows NT Security:

Requires you to be setup as a user on the server.

Use specific Username and password:

Authentication is left to the database itself.

Select database:

Select a database to use. Leave blank to get the default database.

Test Connection:

Always test the connection before you continue.

Microsoft OLE DB Provider for Oracle

Server Name:

Use the database alias as setup in your tnsnames.ora setup. Use the same database as you would use in SQL Plus

Allow saving password:

Important! Set this when connecting to Oracle databases.

Test Connection:

Always test the connection before you continue.

Common Parameters

The following table describes the most common connection parameters.

Property	Description
Data Source	Enter the name of the data source you want to access, typically a server name.
Location	Enter the location of the data source you want to access, typically a database name.
Use Windows NT integrated security	Click to use an authentication service to identify yourself. To use this feature, your OLE DB provider must support an authentication service. The OLE DB Provider for SQL Server is an example. You also must have permissions in the data source to use the authentication service.
Use a specific user name and password	Select to use a supplied user name and password to authenticate your logon information to the data source
User name	Enter the User ID to use for authentication when you log on to the data source.
Password	Enter the password to use for authentication when you log on to the data source.
Blank password	Enables the specified provider to return a blank password in the connection string.
Allow saving password	Allows the password to be saved with the connection string. Whether the password is included in the connection string depends on the functionality of the calling application. If saved, the password is returned and saved unmasked and unencrypted.
Enter the initial catalog to use	Enter the catalog that you want to access upon connection.
Test Connection	Click this button to attempt a connection to the specified data source. If no connection is made, review the settings. IMPORTANT: Spelling errors or case sensitivity can block connections.