Database Connection Configuration

When installing Automate Enterprise, you will be given the option to choose Express Setup or Advanced Setup. The Express Setup option installs and configures an instance of the Microsoft SQL Express database engine.

This document discusses the “Existing SQL Server, MySQL, Oracle, or OLEDB database” configuration option available under Advance Setup.

When you choose the “Existing SQL Server, MySQL, Oracle, or OLEDB database” option, you can configure the connection to an existing database. If the database is new, the installation process will create the necessary tables. If the database contains Automate Enterprise tables from a previous installation, the installation process will attempt to update the tables, if necessary. It will only attempt an update if the updates to the schema are additive updates, that is, adding new tables and columns. If the existing schema cannot be updated you will need to setup a new database for Automate Enterprise and use the data migration utility to migrate the existing data to the new database.

IMPORTANT
Note:
You should not be selecting an existing database which contains data from a previous installation that is a major version older than the one you are currently installing. For example, if the existing database is from v10, then a v11 installation should point to an entirely new empty database or one that was created by an earlier version of 11.

Note: The SQL Server, MySQL, and Oracle options use the native database drivers to connect to the respective databases.  The OLEDB option will list all OLEDB drivers installed on the machine. You will need to pick an appropriate driver and configure the connection in accordance with that driver.

  1. To connect to an existing database, choose Advanced Setup and when prompted to choose the database choose the “Existing SQL Server, MySQL Oracle or OLEDB database” option.
  2. From the “Database type” drop down list, choose the connection type to be used.
  3. Configure the connection. See details below for configuring the connection.
  4. Click on “Test” to test the connection.
  5. If the connection is successful, then proceed with the installation.

SQL Server Database Connection Configuration

To configure Automate Enterprise to use an SQL database you must provide the connection details to an existing SQL server database.

When installing Automate Enterprise, in the Database connection configuration window, provide the following information to an existing SQL database:

Server: the address of the server, for example 192.168.10.10\SQLEXPRESS

Database: The name of an existing database, for example BPA

Database Username: username who is a member of the db_owner role for that database

Database Password: the password for the user

Note: You can click on “Test” to test connecting to the database.

If you have issues connecting to the database try connecting to the same database using the Microsoft SQL Server Management Studio with the same information.

MySQL Database Connection Configuration

To configure Automate Enterprise to use a MySQL database, you must provide the connection details to an existing MySQL server database.

When installing Automate Enterprise, in the Database connection configuration window, provide the following information to an existing MySQL database:

Server: the address of the server, for example 192.168.10.10.

Database: The name of an existing database, for example BPA

Database Username: username of user who has been granted all Object Rights, DDL Rights, and permissions to create temporary tables and to lock tables. Note, if the MySQL database is a remote database, users must also have been granted permissions to login from a remote machine (i.e., see Login => Host Matching)

Database Password: the password for the user

Note: You can click on “Test” to test connecting to the database.

If you have issues connecting to the database try connecting to the same database using the MySQL Workbench with the same information.

Oracle Database Connection Configuration

To use Automate Enterprise with Oracle, you will need to provide a database user.

In Oracle a database user account owns a single schema, which has the same name as the user. In a production database, the schema owner usually represents a database application rather than a person.

Given below are the minimum requirements for the Oracle database.

Oracle Database Setup

Note: You may use any name for the automate user. For the purpose of this document we will use “automatedb” as the user that represents the Automate Enterprise database.

  1. Create an Oracle User. For example:

CREATE USER automatedb IDENTIFIED BY automatedb;

  1. Grant create session privileges to the user. For example:

GRANT CONNECT TO automatedb;

  1. Grant tablespace quota to user. For example:

GRANT UNLIMITED TABLESPACE TO automatedb;

  1. At a minimum, grant create table, delete table, and alter table DDL (Data Definition Language) privileges to the user. For example:

GRANT CREATE TABLE TO automatedb;

  1. At a minimum, grant select, insert, update, and delete DML (Data Manipulation Language) privileges to the user.

By default the user will be granted DML privileges to the user’s schema.

Configuring the Connection

To configure Automate Enterprise to use an Oracle database you must provide the connection details to an existing SQL server database.

When installing Automate Enterprise, in the Database connection configuration window, provide the following information to an existing Oracle database:

Server:  host-name:port/SID for the Oracle Server, for example localhost:1521/orcl

Database: The database filed will be grayed out as it will be inferred from the username.

Database Username: The user name of the user who owns the schema to be used as the Automate Enterprise database.

Database Password: the password for the user

Note: You can click on “Test” to test connecting to the database.

If you have issues connecting to the database try connecting to the same database using the Oracle SQL Developer with the same information.


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: August 28, 2019