Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
The following connection properties are required in order to connect to data.
Server: The host name or IP of the server hosting the MySQL database.
Port: The port of the server hosting the MySQL database.
You can also optionally set the following:
Database: The default database to connect to when connecting to the MySQL Server. If this is not set, tables from all databases will be returned.
To authenticate using standard authentication, set the following:
User: The user which will be used to authenticate with the MySQL server.
Password: The password which will be used to authenticate with the MySQL server.
As an alternative to providing the standard username and password, you can set IntegratedSecurity to True to authenticate trusted users to the server via Windows Authentication.
You can leverage SSL authentication to connect to MySQL data via a secure session. Configure the following connection properties to connect to data:
SSLClientCert: Set this to the name of the certificate store for the client certificate. Used in the case of 2-way SSL, where truststore and keystore are kept on both the client and server machines.
SSLClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
SSLClientCertSubject: The subject of the TLS/SSL client certificate. Used to locate the certificate in the store.
SSLClientCertType:; The certificate type of the client store.
SSLServerCert: The certificate to be accepted from the server.
Using SSH, you can securely login to a remote machine. To access MySQL data via SSH, configure the following connection properties:
SSHClientCert: Set this to the name of the certificate store for the client certificate.
SSHClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
SSHClientCertSubject: The subject of the TLS/SSL client certificate. Used to locate the certificate in the store.
SSHClientCertType: The certificate type of the client store.
SSHPassword: The password that you use to authenticate with the SSH server.
SSHPort: The port used for SSH operations.
SSHServer: The SSH authentication server you are trying to authenticate against.
SSHServerFingerPrint: The SSH Server fingerprint used for verification of the host you are connecting to.
SSHUser: Set this to the username that you use to authenticate with the SSH server.
Set the following properties to connect to Informix:
Server: Set this to the name of the server running Informix.
Port: Set this to the port the Informix server is listening on.
Database: Set this to the name of the Informix database.
User: Set this to the username of a user allowed to access the database.
Password: Set this to the password of a user allowed to access the database.
You will also need to install the IBM Data Server Provider for .NET on the appRules Portal machine, the installation registers it in the machine.config.
To connect to Access, set the DataSource property to the path to the Access database file.
The appRules embedded database SQL syntax is similar to Microsoft SQL Server. It supports Views and Stored Procedures.
Below is a sample connection string
data source=[SharedFilesFolder]\SQL Database.appdb; open mode=MultiProcessReadWrite;
To get help information for any property, click on the property row and the help text will be displayed at the bottom the property grid.
The Server and Port properties must be set to a MariaDB server. If IntegratedSecurity is set to false, then User and Password must be set to valid user credentials. Optionally, Database can be set to connect to a specific database. If not set, the provider reports tables from all databases.
The following are the properties for connecting to MariaDB. The required properties are identified using the *** characters.
If a connection property value has special characters such as semicolons, single quotes, spaces, etc., then you must quote the value using either single or double quotes.
Other properties (Proxy...) are detailed in section common properties
Connection Life Time
The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.
Database***
The name of the MariaDB database.
Integrated Security
Whether or not to authenticate with Windows authentication.
Max Rows
Limits the number of rows returned when no aggregation or group is used in the query. This helps avoid performance issues at design time.
Password***
The password required for authentication with the MariaDB server.
Pool Idle Timeout
The allowed idle time for a connection before it is closed.
Pool Max Size
The maximum number of connections in the pool.
Pool Min Size
The minimum number of connections in the pool.
Pool Wait Time
The maximum number of seconds to wait for an available connection.
Port
The port of the MariaDB server.
Query Passthrough
This option passes the query to MariaDB as-is.
Server***
The host name or IP address of the server.
SSH Client Cert
The SSH client certificate store for SSH Client Authentication.
SSH Client Cert Password
The password for the SSH client certificate.
SSH Client Cert Type
The type of key store containing the SSH client certificate.
SSH Password
The SSH password.
SSH Port
The SSH port
SSH Server
The SSH server.
SSH Server Finger Print
The SSH server fingerprint
SSH User
The SSH user.
SSL Client Cert
The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).
SSL Client Cert Password
The password for the TLS/SSL client certificate.
SSL Client Cert Subject
The subject of the TLS/SSL client certificate.
SSL Client Cert Type
The type of key store containing the TLS/SSL client certificate.
SSL Server Cert
The certificate to be accepted rom the server when connecting using TLS/SSL.
Timeout
The value in seconds until the timeout error is thrown.
Use Connection Pooling
This property enables connection pooling.
User***
The user to authenticate when connecting to MariaDB.
Use SSH
Use SSH.
Use SSL
This field sets whether SSL is enabled.
Zero Dates to Null
Whether or not to return Date and DateTime values consisting of all zeros as NULL.
Below is a sample connection string for SQL Server
Data Source=WIN-UFD8979431O\SQLAPPRULES; Initial Catalog=ReplicateCRM; Integrated Security=True; MultipleActiveResultSets=True
The following are the properties for connecting to Microsoft SQL Server. The required properties are identified using the *** characters.
If a connection property value has special characters such as semicolons, single quotes, spaces, etc., then you must quote the value using either single or double quotes.
ApplicationIntent
Declares the application workload type when connecting to a database in an SQL Server Availability Group.
ApplicationName
name of the application associated with the connection string.
AsynchronousProcessing
Boolean value that indicates whether asynchronous processing is allowed by the connection created by using this connection string.
AttachDBFilename
string that contains the name of the primary data file. This includes the full path name of an attachable database.
ConnectionString
connection string resulting of the properties defined, you can directly update it or copy it from another datasource
ConnectRetryCount
[Supported in the .NET Framework 4.5.1 and later versions] The number of reconnections attempted after identifying that there was an idle connection failure. This must be an integer between 0 and 255. Default is 1. Set to 0 to disable reconnecting on idle connection failures.
ConnectRetryInterval
[Supported in the .NET Framework 4.5.1 and later versions] Amount of time (in seconds) between each reconnection attempt after identifying that there was an idle connection failure. This must be an integer between 1 and 60. The default is 10 seconds.
ConnectTimeout
Gets or sets the length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
ContextConnection
value that indicates whether a client/server or in-process connection to SQL Server should be made.
CurrentLanguage
SQL Server Language record name.
DataSource***
name or network address of the instance of SQL Server to connect to.
Encrypt
Boolean value that indicates whether SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed.
Enlist
Boolean value that indicates whether the SQL Server connection pooler automatically enlists the connection in the creation thread's current transaction context.
FailoverPartner
name or address of the partner server to connect to if the primary server is down.
InitialCatalog***
name of the database associated with the connection.
IntegratedSecurity***
Boolean value that indicates whether User ID and Password are specified in the connection (when false) or whether the current Windows account credentials are used for authentication (when true).
LoadBalanceTimeout
minimum time, in seconds, for the connection to live in the connection pool before being destroyed.
MaxPoolSize
maximum number of connections allowed in the connection pool for this specific connection string.
MinPoolSize
minimum number of connections allowed in the connection pool for this specific connection string.
MultipleActiveResultSets***
Must be set to true, so the application can maintain multiple active result sets (MARS). When false, an application must process or cancel all result sets from one batch before it can execute any other batch on that connection.
MultiSubnetFailover
when application is connecting to an AlwaysOn availability group (AG) on different subnets, setting MultiSubnetFailover=true provides faster detection of and connection to the (currently) active server.
NetworkLibrary
String that contains the name of the network library used to establish a connection to the SQL Server.
PacketSize
size in bytes of the network packets used to communicate with an instance of SQL Server.
Password***
password for the SQL Server account. Specified when IntegratedSecurity set to false
PersistSecurityInfo
Boolean value that indicates if security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state.
Pooling
Boolean value that indicates whether the connection will be pooled or explicitly opened every time that the connection is requested.
Replication
Boolean value that indicates whether replication is supported using the connection.
TransactionBinding
string value that indicates how the connection maintains its association with an enlisted System.Transactions transaction.
TrustServerCertificate
indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.
TypeSystemVersion
string value that indicates the type system the application expects.
UserID***
user ID to be used when connecting to SQL Server. Specified when IntegratedSecurity set to false
UserInstance
value that indicates whether to redirect the connection from the default SQL Server Express instance to a runtime-initiated instance running under the account of the caller.
WorkstationID
name of the workstation connecting to SQL Server.
Set the following properties to connect to DB2:
Server: Set this to the name of the server running DB2.
Port: Set this to the port the DB2 server is listening on.
Database: Set this to the name of the DB2 database.
User: Set this to the username of a user allowed to access the database.
Password: Set this to the password of a user allowed to access the database.
You will also need to install the IBM Data Server Provider for .NET on the appRules Portal machine, the installation registers it in the machine.config.
To connect, set the following connection properties:
Server: The host name or IP address of the Vertica database.
Database: The name of the database hosted on the Vertica Server.
User: The username of the authenticating Vertica database user.
Password: The password of the authenticating Vertica database user.
Port: The port for Vertica (5443 by default). This property is optional.
To connect to Oracle, you'll first need to update the appropriate environment variable and ensure it contains a folder location that includes the Oracle OCI Library assemblies.
If appRules is already installed on your computer, a windows PATH variable to [Installation Folder]/appStrategy/appRules/bin/x64 must have been created by the Installer.
If not, create it manually : see here
The following are the properties for connecting to Oracle. The required properties are identified using the *** characters.
If a connection property value has special characters such as semicolons, single quotes, spaces, etc., then you must quote the value using either single or double quotes.
Server***
The host name or IP of the server hosting the Oracle database.
Port*** The port used to connect to the server hosting the Oracle database.
Service Name*** The service name of the Oracle database.
User***
The Oracle OCI user account used to authenticate.
Password***
The password used to authenticate the user.
Browsable Schemas***
Comma-separated list of schemas to restrict browse-able database object tree.
Datasource Oracle Net Services Name, Connect Descriptor, or an easy connect naming that identifies the database to which to connect.
Other properties (Proxy...) are detailed in section common properties
Connection Properties
Enter the required properties or the ConnectionString text for connecting to the data source.
To get help information for any property, click on the property row and the help text will be displayed at the bottom the property grid.
Connection Properties
Enter the required properties or the ConnectionString text for connecting to the data source.
To get help information for any property, click on the property row and the help text will be displayed at the bottom the property grid.
The following connection properties are usually required to connect to PostgreSQL.
Server: The host name or IP of the server hosting the PostgreSQL database.
User: The user which will be used to authenticate with the PostgreSQL server.
You can also optionally set the following:
Database: The database to connect to when connecting to the PostgreSQL Server. If this is not set, the user's default database will be used.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
Unless you select another scheme, Password is the default authentication mechanism the provider uses to connect to PostgreSQL Server.
To use standard authentication, set the AuthScheme to Password to connect to PostgreSQL with login credentials.
Then, to authenticate, set the Password associated with the authenticating user.
There are subtypes of the Password authentication scheme supported by the provider which must be enabled in the pg_hba.conf file on the PostgreSQL server.
See for more information about authentication setup on the PostgreSQL Server.
MD5
The provider can authenticate by verifying the password with MD5. This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to md5.
SASL
The provider can authenticate by verifying the password with SASL (particularly, SCRAM-SHA-256). This authentication method must be enabled by setting the auth-method in the pg_hba.conf file to scram-sha-256.
Methods available for connecting to PostgreSQL with Microsoft Azure include:
Azure Active Directory OAuth
Azure Active Directory Password
Azure Active Directory MSI
Azure AD is a connection type that leverages OAuth to authenticate. OAuth requires the authenticating user to interact with PostgreSQL using an internet browser. The provider facilitates this in several ways as described below. Set your AuthScheme to AzureAD. All AzureAD flows assume that you have done so.
After setting the following connection properties, you are ready to connect:
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
OAuthClientId: (custom applications only) Set this to the client Id in your application settings.
OAuthClientSecret: (custom applications only) Set this to the client secret in your application settings.
CallbackURL: Set this to the Redirect URL in your application settings.
When you connect the provider opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The provider then completes the OAuth process:
Extracts the access token from the callback URL and authenticates requests.
Obtains a new access token when the old one expires.
Saves OAuth values in OAuthSettingsLocation that persist across connections.
Admin Consent
Admin consent refers to when the Admin for an Azure Active Directory tenant grants permissions to an application which requires an admin to consent to the use case.
Admin Consent Permissions
When creating a new AzureAD app in the Azure Portal, you must specify which permissions the app will require. Some permissions may be marked as "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your app requires admin consent, there are a couple of ways this can be done.
The easiest way to grant admin consent is to just have an admin log into portal.azure.com and navigate to the app you have created in App Registrations. Under API Permissions, click Grant Consent for your app to have permissions on the tenant under which it was created.
Client Credentials
Client credentials refers to a flow in OAuth where there is no direct user authentication taking place. Instead, credentials are created for just the app itself. All tasks taken by the app are done without a default user context. This makes the authentication flow a bit different from standard.
Client OAuth Flow
All permissions related to the client oauth flow require admin consent. T
In your App Registration in portal.azure.com, navigate to API Permissions and select the Microsoft Graph permissions. There are two distinct sets of permissions - Delegated and Application permissions. The permissions used during client credential authentication are under Application Permissions. Select the permissions you require for your integration.
You are ready to connect after setting one of the connection properties groups depending on the authentication type.
Authenticating using a Client Secret
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
AzureTenant: Set this to the tenant you wish to connect to.
OAuthGrantType: Set this to CLIENT.
OAuthClientId: Set this to the client Id in your app settings.
OAuthClientSecret: Set this to the client secret in your app settings.
Authenticating using a Certificate
InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
AzureTenant: Set this to the tenant you wish to connect to.
OAuthGrantType: Set this to CLIENT.
OAuthClientId: Set this to the client Id in your app settings.
OAuthJWTCert: Set this to the JWT Certificate store.
OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.
Authentication with client credentials takes place automatically like any other connection, except there is no window opened prompting the user. Because there is no user context, there is no need for a browser popup. Connections will take place and be handled internally.
Note: Azure PostgreSQL Flexible servers are not supported. Only Azure PostgreSQL Single Server instances are supported.
Ensure that an Active Directory admin has been set in the Azure PostgreSQL instance (Active Directory admin -> Set admin).
Next, set the following to connect:
User: Set this to the Azure Active Directory user you granted access to the Azure PostgreSQL server.
AzureTenant: Set this to the Directory (tenant) ID, found on the Overview page of the OAuth app used to authenticate to PostgreSQL on Azure.
Server: Set this to the Server name of the Azure PostgreSQL server, found on the Overview page of the Azure PostgreSQL instance.
Database: Set this to the database you'd like to connect to on the Azure PostgreSQL instance.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthClientId: Set this to the Application (client) ID, found on the Overview page of the OAuth app used to authenticate to PostgreSQL on Azure.
OAuthClientSecret: Set this to the Value of the client secret, generated at the Certificates and secrets page of the authenticating OAuth app.
CallbackURL: Set this to the Redirect URI you specified during the creation of your OAuth app.
To connect using your Azure credentials directly, specify the following connection properties:
AuthScheme: Set this to AzurePassword.
User: Set this to your user account you use to connect to Azure.
Password: Set this to the password you use to connect to Azure.
AzureTenant: Set this to the Directory (tenant) ID, found on the Overview page of the OAuth app used to authenticate to PostgreSQL on Azure.
Server: Set this to the Server name of the Azure PostgreSQL server, found on the Overview page of the Azure PostgreSQL instance.
Database: Set this to the database you'd like to connect to on the Azure PostgreSQL instance.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
To authenticate to your PostgreSQL Google SQL Cloud Instance using a service account, you must create a new service account and have a copy of the accounts certificate. For a JSON file, set these properties:
AuthScheme: Set this to GCPServiceAccount.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to GOOGLEJSON.
OAuthJWTCert: Set this to the path to the .json file provided by Google.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
For a PFX file, set these properties instead:
AuthScheme: Set this to GCPServiceAccount.
InitiateOAuth: Set this to GETANDREFRESH.
OAuthJWTCertType: Set this to PFXFILE.
OAuthJWTCert: Set this to the path to the .pfx file provided by Google.
OAuthJWTCertPassword: (optional) Set this to the .pfx file password. In most cases you must provide this since Google encrypts PFX certificates.
OAuthJWTCertSubject: (optional) Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
OAuthJWTIssuer: Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.
If you are running PostgreSQL on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:
AuthScheme: Set this to AzureMSI.
User: Set this to the Azure PostgreSQL user that maps to your managed service identity. Provide this in the format myuser@myservername, where myservername doesn't include the ".postgres.database.azure.com" part.
Server: Set this to the Server name of the Azure PostgreSQL server, found on the Overview page of the Azure PostgreSQL instance.
Database: Set this to the database you'd like to connect to on the Azure PostgreSQL instance.
Port: The port of the server hosting the PostgreSQL database. 5432 by default.
The MSI credentials are automatically obtained for authentication.
To obtain the credentials for an IAM user, follow the steps below:
Sign into the IAM console.
In the navigation pane, select Users.
To create or manage the access keys for a user, select the user and then go to the Security Credentials tab.
To obtain the credentials for your AWS root account, follow the steps below:
Sign into the AWS Management console with the credentials for your root account.
Select your account name or number and select My Security Credentials in the menu that is displayed.
Click Continue to Security Credentials and expand the "Access Keys" section to manage or create root account access keys.
In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user.
To authenticate as an AWS role, set the following:
AuthScheme: Set this to AwsIAMRoles.
User: Set this to the AWS-hosted PostgreSQL user that you granted the aws_iam role to. This user should map to an AWS user that has a role containing a policy which includes the rds-db:connect permission.
AWSRoleARN: Specify the Role ARN for the role attached to the authenticating IAM user. This will cause the provider to attempt to retrieve credentials for the specified role.
AWSAccessKey: The access key of the authenticating IAM user.
AWSSecretKey: The secret key of the authenticating IAM user.
Note: Roles may not be used when specifying the AWSAccessKey and AWSSecretKey of an AWS root user.
See for information about creating custom applications and reasons for doing so.
The authentication with Kerberos is initiated by PostgreSQL Server when the CData ADO.NET Provider for PostgreSQL is trying to connect to it. You should setup Kerberos on the PostgreSQL Server to activate this authentication method. Once you have Kerberos authentication setup on the PostgreSQL Server, see for details on how to authenticate with Kerberos by the provider.
Connection Life Time
The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.
Max Lob Size
The volume in numbers of bytes or UTF-8 chars which is allowed to query by non-parameterized SELECT query.
Pool Idle Timeout
The allowed idle time for a connection before it is closed.
Pool Max Size
The maximum connections in the pool.
Pool Min Size
The minimum number of connections in the pool.
Pool Wait Time
The max seconds to wait for an available connection.
Below is a sample connection string for SQL Azure
Server=tcp:apprulesazure.database.windows.net,1433;Initial Catalog=apprulessqlazure;Persist Security Info=False;User ID=apprulesadmin;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
The following are the properties for connecting to Microsoft SQL Server Compact Edition 4.0. The required properties are identified using the *** characters.
If a connection property value has special characters such as semicolons, single quotes, spaces, etc., then you must quote the value using either single or double quotes.
To connect to CE, set the DataSource property to the path to the CE database file.
AutoShrink Threshold Specify the amount of free space, as a percentage, that is allowed in the SQL Server Compact database before the autoshrink process runs.
Default Lock Escalation Specify the number of database locks that the SQL Server Compact database acquires before it tries to escalate locks.
Default Lock Timeout Specify the default interval, in milliseconds, that a transaction will wait for a lock.
Flush Interval Specify the interval, in seconds, between committed transactions to flush data to disk.
Locale Identifier Specify the Locale ID (LCID) of the SQL Server Compact database.
Max Buffer Size Specify the maximum amount of memory, in kilobytes, that SQL Server Compact uses before flushing data to disk.
Max Database Size Specify the maximum size, in megabytes, of the SQL Server Compact database.
Mode Specify the file mode in which to open the SQL Server Compact database. The default value for this property is Read Write.
The Mode option has four values, as described in the following table.
| Value | Description |
|---|---|
Persist Security Info Specify whether security information is returned as part of the connection string. The default value for this option is False.
Temp File Directory Specify the location of the SQL Server Compact temporary database file.
Data Source *** Specify the path to the SQL Server Compact database file.
Password Enter optional password for the SQL Server Compact database.
Read Only
Specifies read-only access to the database.
Read Write
Specifies read/write permission to the database.
Exclusive
Specifies exclusive access to the database.
Shared Read
Specifies that other users can read from the database at the same time.
