You can connect CIC to an existing on-premise or private cloud database or to the CIC database. Either type of connection allows you to use the database as a Data Source in your integrations. This article describes how to implement both types of connections. 

Connecting to an existing database from CIC

You can connect CIC to an existing database either directly or using DB Proxy (CIC Agent as a proxy).

Connecting directly allows CIC to perform SQL operations on an on-premise database without having to install any additional software in the on-premise or private cloud environment. However, connecting directly exposes your database to CIC and, thus, requires open inbound ports. See Connecting directly to a database from CIC

Connecting using DB Proxy also allows CIC to perform direct database operations. And because CIC Agent connects outbound to CIC over a mutually authenticated secure connection, there is no need to open inbound firewall ports. However, you must install and configure some CIC software where it is accessible from the system the database resides on. See Using DB Proxy to connect to a database.

Both of these connection types provide you with end-to-end visibility of all operations since database operations are directly visible to the CIC platform. 

Connecting directly to a database from CIC

1. In the CIC Studio, open the Project where you want your Data Source to reside.
2. Add the JDBC driver you want to use to your Project's Classpath.
   1. Add a lib directory to the Project and add the JDBC driver .jar file to the new lib directory.
   2. Open the Manifest editor and do the following: 
      • In the Runtime tab, click Add in the Classpath section and add the driver jar.

        

      • In the Build tab, ensure that the lib directory is added in the Runtime Information area of the Build tab.

        

        

      • Make sure to select lib directory and the driver jar in both the Binary Build and Source Build areas of the tab.

        

      • Save your work.
3. Create and configure your Data Source. When the JDBC Driver is added to the MANIFEST.MF, the Data Source Classpath field will be left blank, but you will be able to select the JDBC driver. See Data Sources. Make sure you use the JDBC driver you just added to your Project.
4. Reference your Data Source in other CIC Studio objects like you would any other Data Source.

Using DB Proxy to connect to a database

CIC DB Proxy allows you to connect CIC to an on-premise database server without using a VPN or requiring the customer to open an external JDBC inbound port to their internal network.  See About DB Proxy for more information.

Do the following to configure DB Proxy:

1. Install the CIC Agent and create an Access Point accessible from the system where the database resides. The CIC Agent and Access Point must be installed on physical or virtual hardware inside the on-premise or private cloud deployment.  See Managing Access Points.
2. Perform steps above in Connecting directly to a database from CIC, except while you are configuring your Data Source, do the following:
   • Under Connection Information, when you specify the database URL, insert jdbc:remote:// at the front of the URL and insert the Access Point name (MyAccessPoint in this example) in your JDBC connection string. The following example enables CIC to use the Access Point, MyAccessPoint to connect to the postgresql database, MyDatabase, at 10.10.25.156.

jdbc:remote://MyAccessPoint;jdbc:postgresql://10.10.25.156/MyDatabase

About DB Proxy

CIC DB Proxy enables the CIC Studio/Integration Server to connect to a customer’s on-premise database server without using a VPN or requiring the customer to open an external JDBC inbound port to their internal network. 

All TCP connections from the CIC Agent (Access Point) to the cloud are initiated by the Agent and are outbound connections only. 

Below is the database connection/session creation flow:

1. CIC Studio prepares a custom JDBC remote URL used to establish a DBProxy connection.
2. CIC Studio requests a DBProxy connection to be established to an on-prem database.
3. The DBProxy service sends a session initiation request to the CIC Agent over a connection that was previously established (outbound) by the CIC Agent on startup. 
4. CIC Agent establishes an ephemeral outbound connection to the CIC DBProxy service for the duration of the database session.
5. CIC Agent establishes a connection to the DB Server. 

JDBC Remote URL

CIC uses a custom JDBC URL to initiate and establish a connection to an on-prem database.  The URL is called a “Remote JDBC URL” and is formatted as follows:

jdbc:remote://ap-name;jdbc:dbDriver://onPremHost:onPremPort/database

 

Where:

• jdbc:remote is the CIC Studio wrapper scheme used to identify that this connection will access an on-prem database server.
• ap-name is the Access Point that will be used in the DBProxy connection.
• dbDriver is the database-specific driver that will be used to communicate with the database server, for example sqlserver, as400, postgres, mysql, oracle:thin, etc.
• onPremHost is the database server hostname or IP address.
• onPremPort is the database server TCP port.

Given a user-entered JDBC URL:

jdbc:remote://Test Access Point;jdbc:postresql://10.10.30.24:5432/testdb 

CIC will establish a DBProxy connection:

• The on-prem database server is a PostgreSQL server and the connection will use the postgres driver.
• The CIC Agent used to connect to the cloud and the database server is identified as “Test Access Point” in CIC. 
• The on-prem database server is listening at IP address 10.10.30.24 and port number 5432.

Access Point Outbound Port Requirements

The Access Point (CIC Agent) requires the following outbound ports opened in order to establish DBProxy connections:

• 443
• 17000

Note - Testing data source connections from CIC Integration Studio requires the following outbound ports to be opened:

• 443
• 17000
• 14000-16999

Supported Database Servers

The following database servers have been tested using CIC DBProxy and include JDBC URL and driver configuration notes when applicable. 

PostgreSQL

JDBC URL - jdbc:remote://AccessPoint;jdbc:postgres://dbhost:dbport/dbname

Default Port - 5432

SQLServer

JDBC URL - jdbc:remote://AccessPoint;jdbc:sqlserver://dbhost:dbport;databaseName=dbname

Default Port - 1433

AzureSQL

JDBC URL - jdbc:remote://AccessPoint;jdbc:jtds:sqlserver://dbhost:port;databaseName=dbname;encrypt=true;trustServerCertificate=true;serverName=dbhost;

Default Port - 1433

DB2 (iSeries)

JDBC URL - jdbc:remote://AccessPoint;jdbc:as400://dbhost:dbport;translate binary=true metadata source = 0;libraries=library

Default Port - 8471

DB2 (Win/Lin)

JDBC URL - jdbc:remote://AccessPoint;jdbc:db2://dbhost:dbport/dbname

Default Port - 5021

Oracle

JDBC URL - jdbc:remote://AccessPoint;jdbc:oracle:thin@dbhost:dbport:sid

Default Port - 1521

Redshift

JDBC URL - jdbc:remote://AccessPoint;jdbc:redshift://dbhost:dbport/dbname

Default Port - 5439

Miscellaneous Notes

• No JDBC .jar files are required to be loaded on the access point. 
• When using Integration Studio on-prem, outbound ports 15000-15999 must be opened.
• All DBProxy TCP connections are secured regardless of whether or not the JDBC connections string is configured to use SSL/TLS.

Connecting to the CIC database

It is possible to use the CIC database as a Data Source. Contact Cleo for assistance. 

Using a proxy to connect to a database from CIC

1. Install the CIC Agent and create an Access Point accessible from the system where the database resides. The CIC Agent and Access Point must be installed on physical or virtual hardware inside the on-premise or private cloud deployment.  See Managing Access Points.
2. Perform steps above in Connecting directly to a database from CIC, except while you are configuring your Data Source, do the following:
   • Under Connection Information, when you specify the database URL, insert jdbc:remote:// at the front of the URL and insert the Access Point name (MyAccessPoint in this example) in your JDBC connection string. The following example enables  CIC to use the Access Point, MyAccessPoint to connect you to the postgresql database, MyDatabase, at 10.10.25.156.