Federated Data Source Configuration

The following describes how to configure a federated data source with some of our most popular plugins.

QuickBooks
Microsoft SQL Server & Azure Cloud Databases
PostgreSQL
Salesforce
NetSuite
Shopify
JIRA
Complete list of other JDBC plugins available from our partner
Custom Drivers for non-standard APIs

QuickBooks

QuickBooks is available in several different editions in both on-prem (i.e. "Desktop") and cloud (i.e. SaaS or "online") configurations. These notes are specifically targeted at 2019/2020 on-prem Enterprise installations. Contact AppSynergy support for other configurations.

To connect AppSynergy to an on-prem QuickBooks installation:

Download and install Remote Connector for QuickBooks or QuickBooks Web Connector onto the machine running QuickBooks. Remote Connector accepts connections through a lightweight, embedded web server that supports SSL/TLS, which enables AppSynergy to connect securely over the Internet. Configure your Remote Connector installation as follows:
- Enable basic authentication and add a user with a strong password. User names and passwords should not contain any spaces or special characters to ensure compatibility.
- In the Advanced tab enable TLS, generate a certificate, save the configuration, and restart the server.
- The first time you connect, you will need to authorize the driver with QuickBooks. More details on setting up the Remote Connector are available here.
Configure your router so that port 8166 (the standard Remote Connector port) forwards to the machine running the Remote Connector for QuickBooks. Note that your router should be configured to only allow connections from the IP address of your private dedicated AppSynergy database server; you can obtain your IP address from support@appsynergy.com.
Setup a Federated Data Source for QuickBooks (see Tools > Federated Data Sources..). The basic format of the connection string is shown below. NOTE: the Test Connection button with the QuickBooks driver does not actually test the connection; it only verifies that the QuickBooks driver is installed correctly on the server. The only way to actually test the connection is to retrieve some data from QuickBooks.

QuickBooks Example Connect String

To obtain the value for the SSLServerCert property used in the connection string below you will need to extract it from the binary .pfx file that was generated by Remote Connector. You can use the following openssl command to extract the public certificate:

openssl pkcs12 -in sample.pfx -clcerts -nokeys -out sample_public.crt

The sample_public.crt file generated above can be opened with Notepad and its contents from -----BEGIN CERTIFICATE----- to -----END CERTIFICATE----- copied into the connection string shown below:

	jdbc:quickbooks:User=test;Password=test;URL=https://YOUR_IP_ADDRESS:8166;
	SSLServerCert=-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZ...Qw -----END CERTIFICATE-----;
	

Note that you can also specify the User and Password properties in the User and Password fields on the Data Source panel rather than in the connection URL.

NOTE: This driver supports caching; see Caching to Improve Performance for how to configure it.

Additional information about the specific tables exposed by QuickBooks is available here.

Salesforce

There are several authentication methods available for connecting to Salesforce: login credentials, SSO, and OAuth. Login credentials are the recommended option. In the example connect string below you will need to set the User, Password and SecurityToken parameters at a minimum. (To obtain a security token simply open the personal information page on Salesforce.com and click the link to reset your security token; the token will be emailed to you.)

By default the SecurityToken is required but you can make it optional by specifying a trusted IP address. (You can find the IP address of your federated server by creating an API key and pinging the listed Database Server Host.) To disable the security token, sign in to Salesforce and enter Network Access in the Quick Find box in the setup section, then add the IP address to the list of trusted IP addresses.

Example connect string:

jdbc:salesforce:User=myUser;Password=myPassword;SecurityToken=myToken;

By default, the driver connects to production environments. Add UseSandbox=true; to the connection string to use a Salesforce sandbox account. Ensure that your user/password/security token are valid for the sandbox.

NOTE: This driver supports caching; see Caching to Improve Performance for how to configure it.

NetSuite

This section describes how to configure the NetSuite federated driver.

The AccountId must be set to the Id of a company account that can be used by the specified User, and the user account specified must be granted Web Services access in NetSuite.

A basic connection string is as follows:

jdbc:netsuite:AccountId=XABC123456;User=user;Password=password;RoleId=3;

The RoleId property can be optionally specified to log the user in with limited permissions (if not specified the user's default role is used). There are a large number of optional parameters that may be important in your particular configuration (including caching to improve performance); contact support for assistance with your configuration.

NOTE: This driver supports caching; see Caching to Improve Performance for how to configure it.

Shopify

First sign in to Shopify and create a Custom Application, then use those values to set a basic connection string as follows:

	jdbc:shopify:
	AppId=8cf036b3xxxxxxxxxxxx032b07f5;
	Password=yourpass;
	ShopUrl=https://MY_STORE_NAME.myshopify.com;
	

NOTE: This driver supports caching; see Caching to Improve Performance for how to configure it.

JIRA

This section describes how to configure the Atlassian JIRA federated driver.

JIRA Cloud - Basic Authentication

You can establish a connection to any JIRA Cloud account. To connect, provide the User and APIToken. To generate an API Token, log in to your Atlassian account and navigate to API tokens > Create API token. Note: API tokens are available in JIRA Cloud only. If you're using JIRA Server, you must provide User and Password for basic authentication as described below.

	jdbc:jira:
	User=admin;
	APIToken=YOUR_API_TOKEN;
	Url=https://yoursitename.atlassian.net;
	Other="DefaultColumnSize=255";
	

NOTE: The Test Connection button may return a false positive (i.e. indicate connection was successful when it was not) due to how this federated driver caches schema info. To actually test the connection you will need to run a query.

JIRA Server - Basic Authentication

You can establish a connection to any JIRA Server instance. To connect, provide the User and Password for basic authentication.

	jdbc:jira:
	User=admin;
	Password=123abc;
	Url=https://yoursitename.atlassian.net;
	Other="DefaultColumnSize=255";
	

NOTE: The DefaultColumnSize=255 parameter may be changed to another value. The JIRA API has string columns with an unspecified maximum length; this parameter sets the default max length when a federated table is created. You can change this to a shorter or longer value, and manually edit values of individual columns when creating the federated table. Note the total row size of a table cannot exceed 64K. When adding a federated table to your database you may need to remove some of the columns with the word "Aggregate" in their name to get under the 64k row size limit (e.g. the Issues table will require removing or editing down the size of certain Aggregate columns).

By default the connector only shows system fields. To access custom fields for Issues add the following parameter:

IncludeCustomFields=true;

NOTE: This driver supports caching; see Caching to Improve Performance for how to configure it.

Microsoft SQL Server & Azure Databases

This section describes how to configure the Microsoft SQL Server & Azure federated driver.

You are able to connect to both on-prem Microsoft SQL Server and cloud-hosted Microsoft Azure SQL databases.

A typical connection string:

	jdbc:sqlserver://YOUR_SERVER_NAME.database.windows.net:1433;database=YOUR_DATABASE;encrypt=true;
	trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=10; 
	

MariaDB

You are able to connect to both on-prem and cloud-hosted MySQL, MariaDB and other "MySQL compatible" databases.

Typical Secure Connection

The following assumes the server has an SSL certificate signed by a global certificate authority:

jdbc:mariadb://server123.domain.com/dbname?sslMode=verify-full

PostgreSQL

This section describes how to configure the PostgreSQL federated driver.

You are able to connect to both on-prem and cloud-hosted PostgreSQL databases.

Typical Secure Connection

The following assumes the server has an SSL certificate signed by a global certificate authority:

jdbc:postgresql://somehost.mydomain.com/dbname?sslmode=verify-full

The above example will validate the server's identity to prevent MITM attacks. This is the most secure option and is strongly recommended for production environments.

AWS Secure Connection

The following is for connecting to PostgreSQL on AWS RDS. In the case of AWS you must reference the AWS RDS CA bundle.

	jdbc:postgresql://somehost.mydomain.com/dbname?sslmode=verify-full&
	sslrootcert=/opt/parasql/etc/jdbc/postgresql/aws.pem
	

PostgreSQL supports multiple schemas within a single database. Although AppSynergy can work with multi-schema databases, it is usually best to specify a default schema search path either as part of the database's native definition or in the connection URL. This eliminates the need to fully qualify names (e.g. schema_name.table_name) and makes the database more compatible with third party tools.

To specify a default schema at connection time simply add the parameter &currentSchema=YOUR_DEFAULT_SCHEMA to any of the above connection strings.

Alternatively, to change the database's default search path to include a specific schema, issue the following command in PostgreSQL:

ALTER DATABASE mydb SET search_path=myschema;

Custom Drivers for other JSON APIs

Detailed configuration instructions for each of our Universal federated drivers is available separately. The information below is intended to provide an overview of the process.

Step 1: Create the Schema Files

Create a data source specifically for creating the schema configuration files for a particular API. You will use a URL like the following to have the system auto-discover the basic structure of an API end point:

	jdbc:json:
	URI="https://api.fbifreight.com/app/api/5/upload_documents/?key=somekey";
	Location=/var/opt/parasql/etc/json/API_NAME;
	Logfile=/var/opt/parasql/log/json/API_NAME.log";
	DataModel=RELATIONAL;
	GenerateSchemaFiles=OnStart;
	

The URI parameter specifies the API end point to auto-discover. The Location parameter determines the folder where the default schema files will be created (the folder must exist); these files can later be manually edited (the .rsd files are in XML format).

Step 2: Edit the Schema Files

Each .rsd file created during auto-discovery will contain the default mapping for the API endpoint. You can edit these .rsd files (they are in XML format) to tweak column mappings (names, datatypes, lengths, etc.) and additionally specify how to handle INSERT, UPDATE and DELETE operations if you need them (all of these mappings are manual except for the SELECT mapping). JSON objects that contain JSON arrays are split into separate relational tables unless DataModel=FLATTEN is specified whereby they will be left in place as simple JSON data.

Step 3: Deploy the Schema Files

Once you are done editing the schema configuration files you can then specify a connection string to deploy them. A connection string to use your custom schema will look something like the following:

	jdbc:json:
	Location=/var/opt/parasql/etc/json/API_NAME;
	Logfile=/var/opt/parasql/log/json/API_NAME.log";
	Verbosity=4;
	CustomUrlParams="key=xyz";
	

NOTE: This driver supports caching; see Caching to Improve Performance for how to configure it.

Caching to Improve Performance

Many of our federated drivers support caching to improve performance. To enable caching for a driver that supports it, add the following properties to the end of the connection URL:

	AutoCache=true;
	CacheLocation=/var/opt/parasql/cache/DRIVER_NAME_YOUR_ACCOUNT_ID.db;
	Other="DefaultColumnSize=255;CacheOptions=AutoTruncateStrings;CacheTolerance=28800;"
	

The DefaultColumnSize=255 and CacheOptions=AutoTruncateStrings parameters are needed for handling long strings.

The CacheTolerance parameter determines how frequently the cache is refreshed. If you want to manually refresh the cache (or just a portion of it) you can issue a command like the following:

	CALL parasql_federated_rmt_cmd('ds0e2e265945183b74a465ae990bbe1234', 'CACHE SELECT * FROM PurchaseOrders WHERE TimeModified >= CURRENT_DATE()') 
	

The first parameter in the above command is the data source ID which is shown on the Federated Data Sources panel as the DSID. The SELECT command determines what data is refreshed in the cache; in the above command we are taking advantage of a TimeModified field to only get the newest data.