# Connection Profiles

A single Workato on-prem agent can be used to connect with multiple on-prem applications. A connection profile uniquely identifies each one and contains configuration information required to connect to that application.

# What is a connection profile

Profiles are configured in the <INSTALL_HOME>/conf/config.yml. A config file can contain profiles to a few types of systems:

Additionally, you can configure proxy servers for on-prem agents installed in a server with limited internet connectivity.

# Applying a new configuration

After configuring your connection profiles, you need restart your on-prem agent. Learn how to run your on-prem agent on your machine.

# autoReload on-prem profiles

By default, each time you update the on-prem config.yml, you need to restart the agent to apply the configuration.

To allow the on-prem agent to automatically apply the changes, add the following to the top of your configuration file:

config:
  autoReload: true

autoReload does not apply for Server settings

Any change made to the Server profile will not be picked by autoReload. Manually stop and restart the OPA to apply these changes.

# Sample config

A typical config file looks something like this:

database:
  profile1:
    ...
  profile2:
    ...

files:
  profile3:
    ...
  profile4:
    ...

jms:
  profile5:
    ...

ldap:
  profile6:
    ...

server:
  classpath:
    ...
  staging:
    ...

Do not use spaces or special characters in connection profile names. You may use _ and - characters.

# Database profile

Database connection profiles are located in the database section of <INSTALL_HOME>/conf/config.yml.

Each database profile is nested under the database header. A sample database profile is provided below.

database:
  your-connection-name: // you may specify your own connection name
    adapter: sqlserver
    host: localhost
    port: 1433
    database: sales
    username: sa
    password: foobar

port numbers can be omitted when matching defaults for a given database type.

# Types of databases

The following databases are support by the on-prem agent:

Database adapter
Microsoft SQL Server sqlserver
Oracle Database oracle
PostgreSQL postgresql
MySQL mysql
Redshift redshift
Other JDBC-compatible database jdbc

Specific which database using the adapter property (learn more) or a complete JDBC URL provided in the url property(learn more).

For other JDBC-compatible databases, you can connect the database using jdbc as the adapter and providing theurl and driverClass properties. Learn more about JDBC profiles.

Oracle Service

If you are using Oracle Service, you must use the url property in the connection profile. Learn more about connecting to Oracle databases.

# Database profile: adapter property

Here's a sample configuration using the adapter property. This sample connects to an instance of SQL server and Redshift:

database:
  sales:
    adapter: sqlserver
    host: localhost
    port: 1433
    database: sales
    username: me
    password: foobar
  operations:
    adapter: redshift
    host: localhost
    port: 1433
    database: customers
    username: me
    password: foobar

# Database profile: url property

Here's a sample configuration using the url property. This sample connects to an instance of PostgreSQL.

database:
  sales:
    url: jdbc:postgresql://sales.database:5432/sales
    username: joe
    password: Secret123
    ApplicationName: workato

# Database profile: Connecting to Oracle database

When working with Oracle database, you may be connecting to either an SID or Service. If you are using SID, you can use both ways to define the profile. First, the

Using adapter property:

database:
  erp:
    adapter: oracle
    host: localhost
    port: 1521
    database: XE
    username: admin
    password: xxx

Using url property:

database:
  erp:
    url: jdbc:oracle:thin:@localhost:1521:XE
    username: admin
    password: xxx

When connecting to an Oracle Service, use the url property:

database:
  erp:
    url: jdbc:oracle:thin:@localhost:1521/PROD
    username: admin
    password: xxx

# JDBC profile

For a connection profile to other JDBC-compatible databases, it require url and driverClass properties.

  • The url property is a valid JDBC URL.
  • The driverClass property is the fully-qualified name of JDBC driver class for the given database. The driver class must be available on the agent's classpath property.

Additionally, you must set up a the server profile, which specifies the staging environment and the classpath property. Learn more about the Server profile.

database:
  tpc:
    url: jdbc:presto://warehouse.intra:8889/tpch
    driverClass: com.facebook.presto.jdbc.PrestoDriver
    adapter: jdbc
    user: my_user
    SSL: false

server:
  classpath: jdbc
  staging: staging

# On-prem files profile

Working with on-prem files requires you to define a file system profile in the files section. You need to specify the base folder for file access as it will be used for resolving relative paths. A folder named HR in the C:\Documents\ directory will be configured like this:

files:
  hrfiles:
    base: "C:\\Documents\\HR"

In another example, if you wish to provide access to the employees folder in the Desktop directory, the configuration will have a file path that looks something like this:

files:
  hrfiles:
    base: "/Users/me/Desktop/employees"

# SAP profile

SAP connection profile must be defined in the server and sap section together.

Here, lib_ext is the directory where you put the SAP JCo connector libraries. If this directory is not already created, create this directory under the root directory of the OPA and put the SAP JCo connector libraries there. The lib_ext folder should already contain the SAPConnector.jar.

TIP

Learn more about the classpath property in the Server profile.

There are two connection types that the SAP connector supports - Direct connections or Message Server connections. Below is the example of Direct connection type. Use this connection type if SAP system is directly exposed as an application server.

server:
  classpath:
    - lib_ext

sap:
  # Direct connection to an ABAP application server
  sap_profile_1: # This is on-prem agent connection profile name
    network_connection:
      gateway_host: "xx.xx.xx.xx"
      system_number: "00"
      program_id: "WORKATO"
    user_logon:
      client: "800"
      language: "EN"
      user: "USERN"
      password: "PASSW"
Parameter Description Click to expand image
gateway_host Could be either DNS name or IP address. If present, this describes a direct connection to SAP host. Could be in the format xx.xx.xx.xx. This is the IP Address of the SAP application server you are connecting directly. This can be seen on the SAP Logon Pad which is used to login to your on-premise SAP Application server. Host
system_number Two digit SAP system number. It identifies the logical port on which the application server is listening for incoming requests. Commonly found from the TCP Port 33XX where XX is the system_number System number
program_id OPTIONAL. Only needed when IDOCs are used in recipes. This matches the program ID given to the RFC destination linked to Workato in Tcode SM59. This is defaulted to WORKATO if not supplied. Program ID
client The actual client number which is used for connecting to Workato. Use the same one you log into with your SAP Logon Pad. It's always a 3 digit integer. Client
language OPTIONAL. Represents the logon language. If the property is not provided, the user's or system's default language is used. Valid values are two-character ISO language codes or one-character SAP language codes. Language
user SAP user provisioned for Workato. Using background user and disabling dialog properties are recommended. User
password SAP user password. Password

Below is the example of messageserver connection type. Use this connection type when SAP system is behind message server gateway.

server:
  classpath:
    - lib_ext

sap:
  # Message Server connection to an ABAP application server
  sap_profile_2: # This is on-prem agent connection profile name
    network_connection:
      message_server_host: "10.30.xx.xx"
      logon_group: "PUBLIC"
      system_id: "DEV"
      program_id: "WORKATO"
    user_logon:
      client: "800"
      language: "EN"
      user: "USERN"
      password: "PASSW"
    # (OPTIONAL) only required if your message server port is not aligned with your system number.
    # i.e. if your message server port is 3601 but your system number is 01, your messager server port is aligned with your system number
    # i.e. if your message server port is 3601 but your system number is 00, your messager server port is NOT aligned with your system number
    jco_overrides:
      jco.client.msserv: "3601"
      jco.server.msserv: "3601"
Parameter Description Click to expand image
message_server_host Message Server host in the format of xx.xx.xx.xx. This is the IP Address of the Message Server you are connecting. Message Server Host
logon_group (Optional) Logical group name of the application servers. Can be found in SAP Tcode SMLG Group
system_id The system ID of the system the message server belongs to. See SAP Note 52959 in case of connection error.
program_id OPTIONAL. Only needed when IDOCs are used in recipes. This matches the program ID given to the RFC destination linked to Workato in Tcode SM59. This is defaulted to WORKATO if not supplied. Program ID
client The actual client number which is used for connecting to Workato. Use the same one you log into with your SAP Logon Pad. It's always a 3 digit integer. Client
language OPTIONAL. Represents the logon language. If the property is not provided, the user's or system's default language is used. Valid values are two-character ISO language codes or one-character SAP language codes. Language
user SAP user provisioned for Workato. Using background user and disabling dialog properties are recommended. User
password SAP user password. Password
jco.client.msserv Message Server port. Not to be confused with the gateway port. Can be found in SAP Tcode SMMS. Message Server Host
jco.server.msserv Message Server port. Not to be confused with the gateway port. Can be found in SAP Tcode SMMS. Message Server Host

# JMS profile

JMS connection profiles must be defined in the jms section. A JMS provider is specified by provider property of a connection profile. The following JMS providers are supported by the on-prem agent:

Messaging service provider
Amazon Simple Queue Service amazon-sqs or sqs
Apache ActiveMQ activemq
Azure Service Bus custom

# Amazon SQS

You need the following configuration properties when connecting to Amazon SQS:

jms:
  MyAmazonProfile:
    provider: amazon-sqs
    region: <Your Amazon API region, eg 'us-east-2'>
    accessKey: <Your Amazon API access key>
    secretKey: <Your Amazon API secret>

Note that you need to make sure your SQS queue is created before sending messages.

# Apache ActiveMQ

For connecting to a running ActiveMQ broker you only need to specify the broker URL:

jms:
  MyActiveMQProfile:
    provider: activemq
    url: tcp://localhost:61616

ActiveMQ broker cannot be embedded into the agent. Using any vm:// broker connections is not supported.

# Azure Service Bus

Azure Service Bus uses custom JMS provider. You will need the following configuration properties when connecting to Azure Service Bus:

jms:
  azureServiceBus:
    provider: custom
    class: org.apache.qpid.jms.JmsConnectionFactory
    remoteURI: amqps://<host-name>.servicebus.windows.net
    username: <policy-name>
    password: "<primary-key>"

Download jar files from here (opens new window) and extract it inside the lib_ext folder.

Add the classpath inside the config.yml file. Learn more about the classpath property.

server:
 classpath: lib_ext

# Apache Kafka profile

Kafka connection profiles must be defined in the kafka section. You need the following configuration properties when connecting to Kafka:

kafka:
  MyKafkaProfile:
    ... connection properties ...

You can provide any Kafka producer (opens new window) or consumer (opens new window) configuration properties, for example, bootstrap.servers or batch_size.

However, some properties are overridden by the on-prem agent and cannot be configured. You will get a warning when trying to redefine a protected property. Some examples of these protected properties:

Property name Comment
key.serializer Only StringSerializer is supported by agent
value.serializer Only StringSerializer is supported by agent
key.deserializer Only StringSerializer is supported by agent
value.deserializer Only StringSerializer is supported by agent
auto.offset.reset Defined by recipes
enable.auto.commit Defined internally

Workato Agent also supports the following (non-Kafka) configuration properties:

Property name Description
timeout General operation timeout, milliseconds.
url Comma-separated list of server URLs where protocol is either kafka or kafka+ssl.
ssl.truststore Allows inlining of PEM-encoded truststore for secure connection to Kafka
ssl.keystore.key Allows inlining of private key for secure connection to Kafka
ssl.keystore.cert Allows inlining of client certificate for secure connection to Kafka

ssl.* options above can be used when connecting to Kafka using SSL/TLS and allows you to keep PEM-encoded certificates and private keys inside the config.yml file. Any YAML-compatible multiline syntax could be used, for instance:

kafka:
  MyKafkaProfile:
    ssl.truststore:
    |
      -----BEGIN CERTIFICATE-----
      502mPNNAYkY4a7Zu84DLCXLFurEa4BhLBqLkzC6WdTrBN9z6Rp/svTIl6VgjSTP6
      .....
      -----END CERTIFICATE-----

Note that password-protected private keys cannot be inlined.

# Active Directory profile

Active Directory connection profiles must be defined in the ldap section. Example profile:

ldap:
  active_directory_main:
    url:
      - ldap://acme1.ldap.com:389
      - ldaps://acme2.ldap.com:636
    username: Administrator
    password: foobar
    base: dc=acme,dc=com
    ssl:
      cert: /path/to/PEM-encoded-certificate-or-trusted-CA
      trustAll: true
Property name Description
url
required
The URL of the LDAP server to use. The URL should be in the format ldap://myserver.example.com:389.

For SSL access, use the LDAPS protocol. The URL format is in the same format ldaps://myserver.example.com:636.

If fail-over functionality is desired, you can provide more than one URL.
username
required
The username (principal) to use when authenticating with the LDAP server. This will usually be the distinguished name of an admin user. For example, cn=Administrator or simply Administrator.
password
required
The password (credentials) to use when authenticating with the LDAP server
base
optional
The base DN for all requests. When this attribute has been configured, all Distinguished names supplied to and received from LDAP operations will be relative to this LDAP path. This can significantly simplify working against a large LDAP tree.

However there are several occasions when you will need to have access to the base path. For more information on this, please refer to Obtaining a reference to the base LDAP path.
ssl
optional
cert Path the PEM encoded certificate or a trusted CA.
pem Full content of a PEM encoded certificate.
key Private key for mutual SSL setup. Required if pem is provided.
trustAll Set to true to enable self-signed certificates.

# HTTP profile

The http configuration section allows configuring agent access to internal HTTPS resources:

http:
  trustAll: true
  verifyHost: true

The agent may be configured to allow accessing internal HTTPS resources which use self-signed certificates. To enable self-signed certificates set trustAll property to true.

Normally a server certificate's Common Name (or Subject Alternate Name) field should match the target hostname. If you want the agent to accept server certificates with non-matching hostname, disable hostname verification by setting verifyHost property to false (defaults to true).

# NTLM profile

Certain HTTP resources require NTLM authentication. This can be done using a NTLM connection profile. Here are some example NTLM profiles:

ntlm:
  MyNtlmProfile:
    auth: "username:password@domain/workstation"
    base_url: "http://myntlmhost.com"
    cm_default_max_per_route: 15
    cm_max_total: 100
    verifyHost: true
    trustAll: false

  AnotherNtlmProfile:
    auth: "domain/workstation"
    username: "username"
    password: "password"
    base_url: "http://myntlmhost.com"
    cm_default_max_per_route: 15
    cm_max_total: 100
    verifyHost: true
    trustAll: false

The following profile properties are supported:

Property name Description
auth Full NTLM authentication credentials. This can include username, password, domain, and workstation.

For OPA version 2.4.7 or later, username and password can be configured separately if they contain special characters like @ and /.
username Username for NTLM authentication.
Only for OPA version 2.4.7 or later
password Password for NTLM authentication.
Only for OPA version 2.4.7 or later
base_url The base URL for NTLM resources
cm_default_max_per_route Optional. Sets the number of connections per route/host (must be a positive number, default 5)
cm_max_total Optional. Sets the maximum number of connections (must be a positive number, default 10)
http_connect_timeout Optional The timeout in milliseconds used when requesting a connection (must be a positive number, default 10000)
http_connection_request_timeout Optional The timeout in milliseconds until a connection is established (must be a positive number, default 10000)
http_socket_timeout Optional The socket timeout in milliseconds, which is the timeout for waiting for data or, put differently, a maximum period inactivity between two consecutive data packets (must be a positive number, default 10000)
verifyHost Optional. Specifies whether to enable verification of the host name for SSL/TLS connections (default true)
trustAll Optional. Specifies whether trust all certificates for SSL/TLS connections (default false)

HTTP methods supported for NTLM connections are GET, POST, PUT, PATCH, DELETE and HEAD.

# Command-line scripts profile

This profile allows users to run arbitrary scripts or commands on OPA. The script definition in the config file can have parameters. When you declare an action, you need to specify the values of the parameters.

An example profile on Unix can look like this:

command_line_scripts:
  workday_reports:
    concurrency_limit: 3
    timeout: 30
    scripts:
     copy_file:
       name: Copy file
       command:
         - /bin/cp
         - '{{source_file}}'
         - '{{target_directory}}'
       parameters:
         - { name: source_file }
         - { name: target_directory }           

     append_file_to_another:
       name: Append file to another
       command:
         - bash
         - -c
         - cat {{source_file}} >> {{target_file}}
       parameters:
         # Parameter quoting
         - { name: source_file, quote: '"' }
         # Advanced parameter quoting
         - { name: target_file, quote: { start: '"', end: '"', quote: '"', escape_char: \ } }

     generate_report:
       name: Generate report
       command:
         - python
         - /home/user/script.py
         - --from
         - '{{from_date}}'
         # Conditional fragment
         - { value: --to, if: to_date }
         # Conditional fragment
         - { value: '{{to_date}}', if: to_date }
       parameters:
         - { name: from_date }
         - { name: to_date, schema: { optional: true, control_type: select, pick_list: [01/01/2018, 02/02/2018] } }

The command-line script profiles are placed in the command_line_scripts section in config.yml. Each profile can contain multiple scripts. The profile configuration properties are as follows:

Property name Description
scripts The scripts hash. The value for each key contains the script profile.
concurrency_limit Optional. Maximum number of concurrently executed scripts. Defaults to 10 when not provided. After reaching the limit, requests are queued.
timeout Optional. Maximum duration(seconds) for each script execution. Defaults to 90 seconds when not provided.

The hash key is used as an unique identifier for a script profile. The script configuration properties are as follows:
Property name Description
name Friendly name for the script that will be displayed in the recipe UI.
command The command invocation array. The value of each item can use Mustache (opens new window) template variables to substitute the parameter values.
parameters Optional. The parameter array (defaults to an empty array).

The command invocation element configuration can be just a string, but also can contain these properties:
Property name Description
value The command invocation element value.
if The parameter name. If parameter value is empty, this command invocation element is not taken into account.

The parameter configuration properties are as follows:
Property name Description
name The parameter name.
quote Optional. The rules of parameter quoting (defaults to no rules).
schema Optional. The parameter schema.

The quote configuration can just be a string or have properties. The properties are as follows:
Property name Description
start The opening quote character.
end The closing quote character.
quote The quote character in the parameter value to be escaped.
escape_char The escape character.

If the quote configuration is a string, its value is considered as the value of the start, end and quote properties, and the escape_char property value is set to '\' on Unix and '""' on Windows.


The parameter schema configuration can have properties as follows:
Property name Description
optional Optional. The optional flag of the parameter (defaults to false).
label Optional. Friendly name for the script, that will be displayed in the recipe UI (defaults to the parameter name).
control_type Optional. Can be 'text' or 'select'. If it's 'select', property 'pick_list' should also be defined. Defaults to 'text'.
pick_list Optional. Values for selecting the parameter value. This property should be defined if property 'control_type' has value 'select'.

# Extensions profile

Working with Java extensions requires you to define an extensions profile. You need a server section to define where the jar files are located, and an extensions section to create individual profiles for the Java classes. A Java extension will be configured like this.

server:
  classpath: ext

extensions:
  security:
    controllerClass: com.mycompany.onprem.SecurityExtension
    secret: HA63A3043AMMMM

The server parameter configuration property is as follows:

Property name Description
classpath Specifies the location of user-defined class

Each extensions profile configuration properties are as follows:

Property name Description
controllerClass A required field to inform the OPA which Java class to map the extension to.
secret Optional environment property that is used in the Java class. Multiple properties can be added.

Find out how to create a Java extension.

# Server profile

The server configuration specifies where OPA assets are location. For example, jar files and driver classes as located in classpath property.

You can also specify a staging environment. The staging environment stores data temporarily while it is loaded into the target system. This is required when using OPA Smart Shunt and when connecting to all JDBC profiles.

Server directory paths

All directory paths specified in the server section must be existing sub-directories in the OPA installation folder.

For example, if your on-prem agent is installed in: /opt/workato-agent/.

You can specify the subdirectory staging as the staging area.

opt/
├── workato_agent/
    ├── conf/
    ├── bin/
    ├── lib_ext/
    ├── staging/

# Server profile: classpath property

This sample defines a jdbc directory that contains Java driver classes or user-defined classes.

server:
  classpath: lib_ext

# Server profile: staging property

This sample defines a staging directory that will be used as a staging area.

For example, when executing the SQL Server Export query result action, the CSV output is temporary stored in this directory before being streamed to the target application.

server:
  staging: staging