External Database Logging Configuration

Overview

Automate Desktop's Log Management preferences enables the option of logging to an external database. This allows Automate Desktop events to be written to an external data source such as Microsoft SQL Server, Oracle or mySQL. Automate Desktop will automatically create and maintain the tables in this database when logging starts. Automate Desktop 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 Desktop supports the use of any database system, provided the computer running Automate Desktop 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. Select Enable database logging (OBDC or OleDB connections only) checkbox, and then select Configure. The Data Link Properties dialog opens.

   

3. Under the Provider tab, select Microsoft OLE DB Provider for ODBC Driver, and then select Next.

   

4. Under the Connection tab, select Use connection string , and then select Build. The Select Data Source dialog opens.

   

5. Under the Machine Data Source tab, select New. The Create New Datasource dialog opens.

   

6. Select User Data Source (Applies to this machine only), and then click Next.

7. Select the mySQL ODBC 3.51 driver, and then select Next.

8. Verify the information is correct, and select Finish.

9. 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 Desktop (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 selecting Test . Select OK.

10. 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, select OK.

Database logging is now configured. Automate Desktop 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 Desktop 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 Desktop's logging engine. This would help prevent a sensitive password from being exposed or a compromised Automate Desktop 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 name - Select your Data Source Name (DSN) from the list. Use the Refresh button to rebuild the list.

• Use connection string - Use a DSN for ease of use. You can also create a connection string here or type it in.

• Allow saving password - It is recommended to always select this option.

• Test Connection - Always test the connection before you continue.

Microsoft OLE DB provider for SQL server

• Select or enter a server name - Select or type your server name. This is the computer's name or the IP address on which the database runs.

• Use Windows NT Integrated security - Requires you to be setup as a user on the server.

• Use a specific user name and password - Authentication is left to the database itself.

• Select the database on the server - 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

• Select or enter a database name - Use the database alias as setup in your tnsnames.ora setup. Use the same database as you would use in SQL

• 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.