Viz Hub Sync Account

Database Repository Connection

What is a Database Repository Connection?

Database Connections are only available in Editions that contain the Enterprise Data Stream add-on. See the Tasktop Editions table to determine if your edition contains this functionality.

Database Repository

A database repository, is a tool such as MySQL or Oracle, which allows you to flow data to a central database. Database repositories are used as part of the Enterprise Data Stream add-on.

In order to configure an Enterprise Data Stream Integration, you must first connect to the database that will be used by that integration. Creating a new database connection is similar to creating a standard repository connection, with a few extra considerations. To create a new database connection, follow the steps below.

Before You Begin

  1. Review the User Management section for instructions on how to log in and manage your user accounts.
  2. Set a Master Password, which will be used to encrypt your repository credentials.
  3. Apply your License on the Settings screen. You can learn how to apply your license here.

Supported Databases

The following databases and versions are supported for use with the Enterprise Data Stream add-on:

General Support

  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

Extended Support

  • 9.6 (End-of-Service-Life Date: )

(lightbulb) If you are interested in extended support, please reach out to support.

General Support

  • 2017
  • 2019

Extended Support

  • N/A

General Support

  • 19c
  • 21c

Extended Support

  • 18c (End-of-Service-Life Date: )

We recommend using JDBC driver version 8.0 or later when creating a SQL connection for Enterprise Data Stream integrations.

General Support

  • 5.7.7+
  • 8.0

Extended Support

  • N/A

(lightbulb) Note: The user must be a SQL authenticated user (and not a Windows authenticated user)

Database Connections and Encryption

The following section describes different ways to configure your database connection. If you choose not to encrypt your connection, data will be transmitted over the network unprotected and will be at risk of being intercepted. Likewise use of self-signed certificates or other certificates not signed by a trusted Certificate Authority puts your data at risk as Hub cannot verify the identity of the server at the end of the connection.

Please ensure your connection is configured in a way that is aligned with your security policy and the associated risks are understood and accepted.

Configuration Details

For PostgreSQL, please refer to PostgreSQL documentation for more information.

Location

  • Example Format: jdbc:postgresql://hostServerName:postgreSqlServerPort/MyDatabaseName

You can enable encrypted connections by setting ‘ssl=true' (e.g., jdbc:postgresql://<server-name>:<port>/?ssl=true).

If the certificate for the PostgreSQL server is self-signed you'll need to set ‘sslfactory=org.postgresql.ssl.NonValidatingFactory' and ‘sslmode=require’ (e.g., jdbc:postgresql://<server-name>:<port>/?ssl=true&sslmode=require&sslfactory=org.postgresql.ssl.NonValidatingFactory).

If the certificate for the PostgreSQL server is not self-signed you'll need to add the certificate to the JDBC’s truststore.

For SQL Server, please refer to Microsoft documentation for more information.

Location

  • Example Format: jdbc:sqlserver://hostServerName;instanceName=MyInstance;databasename=MyDatabaseName

You can enable encrypted connections by setting 'encrypt=true' (e.g., jdbc:sqlserver://<server-name>:1433;encrypt=true;trustServerCertificate=false). If the certificate for the MySQL server is self-signed you'll need to set 'trustServerCertificate=true' (e.g., jdbc:sqlserver://<server-name>:1433;encrypt=true;trustServerCertificate=true)

If using JDBC driver mssql-jdbc-10.2.x or later, the “;trustServerCertificate=“ parameter and its corresponding value is required by the driver.

(lightbulb) Note: Some older editions may be missing security updates and will need to apply security service packs to use a self-signed certificate and encryption. You may experience certificate errors if the SQL Server is using a self-signed or corporate certificate. To work around this, you will need to disable certificate validation in the JDBC driver or add the certificate to the JDBC’s truststore.

For Oracle, please refer to this whitepaper for an overview of how to set up connections to encrypted Oracle server. For a guide to configuring the Oracle server to support SSL, please refer to Oracle documentation.

Location

  • Example Format: jdbc:oracle:thin:@hostServerName:oracleServerPort/SID

For the most part assuming that the server is set up properly, you can follow Case#2 in the white paper and simply use a URL with the following format: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(PORT=2484)(HOST=<hostname>))(CONNECT_DATA=(SERVICE_NAME=<servicename>))). On the server, make sure to disable client authentication by setting 'SSL_CLIENT_AUTHENTICATION=FALSE ' in the listener.ora and sqlnet.ora files.

For unencrypted connections, the protocol should be TCP and the port would generally be 1521, but the URL would otherwise be the same. The above example connection string is formatted in the 'Oracle Net connection descriptor' format, but Hub also accepts 'Thin-style service name' connection strings such as jdbc:oracle:thin:@<hostname>:1521:<servicename>.

If the certificate for the Oracle server is self-signed, but you still want to use SSL, you will need to follow Case#1 in the white paper. As described in the paper, only anonymous cipher suites are permitted when trying to use SSL without server authentication. You can specify the cipher suites in the sqlnet.ora file on the Oracle server.

(lightbulb) Note: Some versions of Oracle do not by default support anonymous cipher suites. Thus, they will need to be imported to the server before enabling them.

For MySQL, refer to MySQL documentation for the details on how to set up your connection.

Location

  • Example Format: jdbc:mysql://hostServerName:mysqlServerPort/MyDatabaseName

To enable encryption on older MySQL servers (5.6.25 and earlier or 5.7.5 and earlier) you need to set the connection property 'useSSL=true' (e.g., jdbc:mysql://<server-name>:3306?useSSL=true). Later versions will implicitly try to connect using an encrypted connection. Regardless of the version, the client will only enforce that the server uses TLS if the property 'requireSSL=true' is set.

If the certificate for the MySQL server is self-signed you will need to set 'verifyServerCertificate=false' (e.g., jdbc:mysql://<server-name>:3306?useSSL=true&verifyServerCertificate=false).

Step 1: Download the JDBC Driver

(warning) Note: This step is not required if using a Hub Cloud instance.Microsoft SQL Server

The JDBC driver for Microsoft SQL Server can be downloaded from the Microsoft support site. The SQL Driver Location should reference the directory containing the sqljdbc42.jar file. This file should be the only .jar file in that directory, or you may end up with errors upon configuring your collection.

Hub currently supports use of the 7.0.0.jre8 driver version.

MySQL

The JDBC driver for MySQL can be downloaded from the MySQL download site. The SQL Driver Location should reference the directory containing the mysql-connector-java-<version>-bin.jar file.

Oracle

The JDBC driver for Oracle can be downloaded from the Oracle support site. Note that it is best if the Oracle JDBC driver that is used matches the version of the Oracle server that you are connecting to. Additionally, the ojdbc6.jar file is the only file that should be in the directory that is used for the SQL Driver Location or you may end up with errors upon configuring your collection.

PostgreSQL

The JDBC driver for PostgreSQL can be downloaded from the PostgreSQL download site. The SQL Driver Location should reference the directory containing the postgresql-<version>.jar file.


Step 2: Upload the JDBC driver

The SQL driver files must be put on the file system of the same server where Hub is installed. When setting up a connection to your database with the SQL connector, the SQL Driver Location field should reference the location of the SQL driver files on the server.

(warning) Note: This step is not required if using a Hub Cloud instance.


Microsoft SQL Server

The SQL Driver Location should reference the directory containing the sqljdbc42.jar file. This file should be the only .jar file in that directory, or you may end up with errors upon configuring your collection.

MySQL

The SQL Driver Location should reference the directory containing the mysql-connector-java-<version>-bin.jar file. 

Oracle

The SQL Driver Location should reference the directory containing the ojdbc6.jar file. The ojdbc6.jar file should be the only file in that directory, or you may end up with errors upon configuring your collection. Note that it is best if the Oracle JDBC driver that is used matches the version of the Oracle server that you are connecting to.

PostgreSQL

The SQL Driver Location should reference the directory containing the postgresql-<version>.jar file.

Step 3: Connect to your Database

  1. In Hub, click Repositories at the top of the screen, and click New Repository Connection.

  2. Select Tasktop SQL as the repository.

  3. Enter a label for your connection. This is how it will be referenced through the Hub application.

  4. Enter the URL of your database. The protocol should be "jdbc:sqlserver://" for a MS SQL database, "jdbc:mysql://" for a MySQL database, "jdbc:oracle://" for an Oracle database, or "jdbc:postgresql://" for a PostgreSQL database.

  5. Select the appropriate JDBC driver (SQL Server, MySQL, Oracle, or PostgreSQL).

  6. Enter the SQL driver location, which is the location of the SQL driver files on the Hub server. See steps 1 and 2 above for more information on the SQL driver files.

    1. (warning) Note: This step is not required if using a Hub Cloud instance.
  7. Enter a username and password for your database.
  8. If you'd like, you can test your connection by clicking the Test Connection button in the upper right corner.

  9. In general, we recommend that users do not edit the Concurrency Limit or Event Rate Limit fields. You can learn more about these fields here.

  10. Click Save and then Done to save the connection. 

New Database Connection




Viewing Associated Configuration Elements

To view associated configuration elements (such as collections or integrations that utilize the repository connection you are viewing), click the Associated Elements tag in the upper right corner of the screen.

View Associated Elements

Associated Elements