Adaptive ERP
AppSynergy's Platform
Resources
The following describes how to configure a federated data source with some of our most popular plugins.
First sign in to Shopify and create a Custom Application, then use those values to set a basic connection string as follows:
NOTE: This driver supports caching; see Caching to Improve Performance for how to configure it.
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:
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:
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:
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.
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:
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.
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:
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.
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:
You are able to connect to both on-prem and cloud-hosted MySQL, MariaDB and other "MySQL compatible" databases.
The following assumes the server has an SSL certificate signed by a global certificate authority:
This section describes how to configure the PostgreSQL federated driver.
You are able to connect to both on-prem and cloud-hosted PostgreSQL databases.
The following assumes the server has an SSL certificate signed by a global certificate authority:
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.
The following is for connecting to PostgreSQL on AWS RDS. In the case of AWS you must reference the AWS RDS CA bundle.
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 ¤tSchema=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:
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:
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:
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.