Using Connection Strings

See below for Connection String Examples.

A source or destination can be configured using either an ODBC data source or a DSN-less connection string.

To configure a a DSN-less connection string:

  1. Select the ODBC Driver radio button. The source or destination screen will now look like this:

  1. Select an ODBC driver from the list.

  2. The ODBC driver name will appear in the Source or Destination Name field above the DSN list. Be sure to modify this name to clearly identify the database; the name cannot be changed after it is saved.

  3. Enter a valid connection string (maximus 254 characters); see below for examples.

  4. Enter the User ID and Password of an account that has permission to obtain data from the selected server. If you are configuring a connection to a SQL Server that uses Windows authentication you do not need to enter the user ID and password; select the Use Integrated Security radio button to disable the user and password fields, and add Trusted_Connection=Yes to the connection string.

  5. Click the Advanced tab if you want to modify the connection limits, configure AS/400 multi-member access, or configure a Source for incremental replication operations (SQDR Plus is required for incremental replication.)

  6. Click OK to save the properties and return to the Replicator Manager window.

Connection String Examples

The following examples typically show the minimum required keywords to establish a connection; refer to the documentation for the ODBC driver of interest for full information.

In cases where multiple drivers are available for a DBMS type, the recommended driver is typically listed first. However, there are scenarios where an alternate driver may be recommended. Contact Stelo support and refer to the technical document SQDR: Recommended ODBC Drivers for advice.

Db2 (StarSQL)

HostName=myhostip;Server=MYRDB

HostName=myhostip;Port=446;Server=MYRDB;IsolationLevel=2;PkgColID=SQDR

HostName=myhostip;Netlib=SQSSL.DLL;Port=448;Server=MYRDB;IsolationLevel=2;PkgColID=SQDR

See the StarQuest Technical Document Using a DSN-less Connection with StarSQL for additional information.

It is very important to specify IsolationLevel=2 when a Db2 for i source is being used for incremental replication.

SQL Server (Microsoft 17 or 18 Driver)

Note: for Microsoft 18 Driver, the default for SSL encryption (parameter Encrypt) is now Yes. You must either install a Certificate Authority certificate (if needed), set TrustServerCertificate=Yes, or set Encrypt=No or Optional.

Server=(local) or Server=. or Server=127.0.0.1 (for a local default instance)

Server=.\SQLEXPRESS (for a local named instance)

Server=MYHOST (for a default instance)

Server=MYHOST\SQLEXPRESS (for a named instance)

Server=MYHOST,<port> (for a default instance listening on a port other than 1433)

Server=MYHOST,<port>\SQLEXPRESS (for a named instance if SQL Browser service is not being used)

Server=MYSERVER;Trusted_Connection=Yes;Database=MyDatabase;Encrypt=Yes;

Use Trusted_Connection=Yes to use integrated Windows security.

Use Encrypt=Yes to specify an SSL connection; use Encrypt=No or Optional to specify a non-SSL connection.

Use Encrypt=Yes;TrustServerCertificate=yes to specify an SSL connection without verifying the server certificate. This is useful if the server certificate was issued by a Certificate Authority (CA) unknown to the client machine or the connection specifies an IP address or hostname other than the Common Name defined in the certificate.

Use Database to specify a default database; this can be overridden when creating subscriptions.

If you get a connection failure ("invalid connect string param"), it may be necessary to add a trailing semicolon.

Specify MultiSubnetFailover=Yes when connecting to a SQL Server availability group listener or Failover Cluster Instance.

SQL Server (SQDR bundled ODBC driver)

HostName=localhost\SQLEXPRESS; EnableQuotedIdentifiers=1

During creation of a subscription using this driver as a destination, select Insert using ODBC rather than the default of Use native-loader function in the Replication Options of the Destination panel.

Azure SQL Database

In the Azure Portal, select the Overview panel for the SQL database.

Select Show database connection strings - this will display the connection string to use with the Microsoft ODBC 17 driver.

Here is an example; specifying the database here is important.

Server=tcp:myserver.database.windows.net,1433;Database=mydatabase;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

To use the SQDR bundled ODBC driver, the connection string will look something like this:

HostName=myserver.database.windows.net; EnableQuotedIdentifiers=1; Database=mydatabase

Go to the Advanced Panel of the source or destination and change Target Schema from the user name (e.g. myID@myaccount) to dbo or the desired schema name.

Oracle (SQDR bundled ODBC driver)

HostName=myhostip;PortNumber=1521;ServiceName=myservice.mydomain.com

HostName=myhostip;PortNumber=1521;SID=MYSID

If you are using a multitenant container database (CDB), use the ServiceName property to specify a connection to the PDB of interest. The ServiceName is a fully qualified name consisting of the PDB name followed by a domain name; use the command lsnrctl status on the Oracle server to list the service name expected by the Listener.

See Oracle SSL Connections for connecting with SSL (Secure Sockets Layer). This is required for Oracle Autonomous databases.

Oracle (Oracle ODBC driver)

DBQ=MYSERVER (where MYSERVER is one of the configured TNS names)

or DBQ=//hostname:port/SID (for connecting without tnsnames.ora)

You can also use an Oracle Internet Directory (OID) entry in place of tnsnames.ora.

If you are using a multitenant container database (CDB), specify a connection to the pluggable database (PDB) of interest with:

DBQ=//hostname:port/ServiceName

Where ServiceName refers to the PDB of interest; see the previous section for a details about ServiceName.

MySQL/MariaDB (MySQL Community Edition ODBC driver)

SERVER=myhostip;PORT=3306;DATABASE=mydb;FOUND_ROWS=1;NO_SSPS=1

This example shows connecting via TLS/SSL to AWS RDS for MySQL, validating the certificate downloaded from AWS:

SERVER=mysql2.xxxx.us-east-1.rds.amazonaws.com;PORT=3306;DATABASE=mysql2;FOUND_ROWS=1;NO_SSPS=1;SSLMODE=VERIFY_CA;SSLCA=C:\aws\global-bundle.pem

If fetching large amounts of data from a MySQL source results in memory issues for the SQDR service, add NO_CACHE=1 to the connection string

MySQL (SQDR bundled ODBC driver - MySQL Enterprise only)

HostName=myhostip;PortNumber=3306;Database=mydb

MariaDB/MySQL (MariaDB ODBC Driver)

SERVER=myhostip;PORT=3306;DATABASE=mydb;OPTIONS=2;CHARSET=utf8

SERVER=mysql2.xxxx.use-east1.rds.amazonaws.com;PORT=3306;DATABASE=mysql2;OPTIONS=2;CHARSET=utf8;FORCETLS=1;SSLCA=C:\bin\aws\global-bundle.pem

OPTIONS=2 is the equivalent of MySQL FOUND_ROWS=1 - Return matched rows instead of affected rows. This is only needed for destination.

Informix  (SQDR bundled ODBC driver)

HostName=myhost;PortNumber=9088;Database=mydb

If you are running SQDR on Linux, append Client_Locale=en_US.utf8 to the connection string:

HostName=myhost;PortNumber=9088;Database=mydb;Client_Locale=en_US.utf8

Informix (IBM Informix driver)

Server=myconn;Host=myhost;Service=9088;Protocol=olsoctcp;Database=mydb;DELIMIDENT=Y

The use of DELIMIDENT is not required if the server has been configured with DELIMIDENT (e.g. in an environment variable), or Quote SQL Identifiers is disabled in the Subscription Wizard panel of SQDR Service properties

If you are running SQDR on Linux, append Client_Locale=en_US.utf8 to the connection string:

Server=myconn;Host=myhost;Service=9088;Protocol=olsoctcp;Database=mydb;DELIMIDENT=Y;Client_Locale=en_US.utf8

OR (using Setnet32):

Server=myconn;DATABASE=mydb

where myconn is the name of a connection configured with Informix Setnet32:

Hostname=myhost
Protocolname=olsoctcp
Service Name=9088

Informix  (IBM Db2 ODBC driver)

HOSTNAME=myhost;PORT=9089;DATABASE=mydb

PostgreSQL (SQDR bundled ODBC driver)

HostName=myhost;Database=mydatabase;PortNumber=5432

This example shows connecting via TLS/SSL to Azure Database for Postgres without validating the certificate:

HostName=mypostgres.postgres.database.azure.com;Database=postgres;PortNumber=5432;EncryptionMethod=1;ValidateServerCertificate=0,

This example shows connecting via TLS/SSL to AWS RDS for Postgres, validating the certificate downloaded from AWS, and requiring TLS 1.2:

HostName=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;PortNumber=5432;EM=1;VSC=1;TrustStore=C:\aws\global-bundle.pem;CPV=TLSV1.2

The following properties are automatically added by SQDR when using a connection string:

FetchTSWTZasTimestamp=1;MaxCharSize=255;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;BatchMechanism=2

PostgreSQL (psqlODBC driver)

Servername=myhost;Database=mydatabase;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364

Servername=mypostgres.postgres.database.azure.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=require

Servername=postgres.xxxx.us-east-1.rds.amazonaws.com;Database=postgres;Port=5432;MaxVarcharSize=32767;MaxLongVarcharSize=214748364;SSLMode=verify-ca;pqopt={sslrootcert=C:\\aws\\global-bundle.pem}

SingleStore (formerly MemSQL)

SERVER=houmsqlfin23.eogresources.com;PORT=3306;DATABASE=db2_mirror;OPTIONS=70254610;CHARSET=utf8

Note: The OPTIONS parameter decompiles to the following individually configurable settings, where the (n) is the power of 2 value used to construct "Options" bit mask):

MULTIPLE_STATEMENTS(26)
FORWARD_ONLY (21)
NO_CACHE(20)
NO_PROMPT(4)
FOUND_ROWS(1)

Apache Spark/Databricks (Simba driver, available from Databricks)

Host=adb-1234567276565967.7.azuredatabricks.net;Port=443;SSL=1;AuthMech=3;ThriftTransport=2;HTTPPath=sql/protocolv1/o/1239854276565967/0307-232555-cxx91kvv

The userID should be specified as token, and the password is a token generated in the Databricks web GUI - select Settings/User Settings/Access Tokens.

Limitations:  Does not support truncate OR delete, so only option is to append or recreate on each run of a snapshot. Does not support creation of an index over the destination.

Exasol

EXAHOST=demodb.exasol.com

Netezza

Servername=myhost;Port=5480;Database=MYDATABASE

Pervasive PSQL

DBQ=MYDATABASE;ServerName=myhost.1583;TCPPort=1583

Amazon Redshift (SQDR bundled ODBC driver)

HostName=myhostip;PortNumber=5439;Database=mydb

Salesforce  (SQDR bundled ODBC driver)

HostName=login.salesforce.com;TransactionMode=1;SQLEngineMode=2

Note: when connecting to Salesforce, the password field should contain an authentication token appended to the password - PasswordAuthenticationToken. The token is a case-sensitive alphanumeric code that’s tied to your password. Whenever your password is reset, your security token is also reset.

To reset the authentication token: In the Salesforce web interface, go to your personal settings. Enter Reset in the Quick Find box, then select Reset My Security Token. The new security token is sent to the email address in your Salesforce personal settings.

To specify the location of the schema map files created and maintained by the driver, specify SchemaMap or SMP. Note that older documentation (prior to March 2021) incorrectly shows the short name as SM rather than SMP.

HostName=login.salesforce.com;TM=1;SEM=2;SMP=C:\mapfiles\mySFserver.conf

Here is an example using a proxy server listening on port 3128:

HostName=login.salesforce.com;TM=1;SEM=2;PXHN=myproxyserver;PXPT=3128

Snowflake

SERVER=hostname.snowflakecomputing.com;DATABASE=MYDB;WAREHOUSE=MYWH

BigQuery

Catalog=potent-veld-386317;Email=sqdr-access@potent-veld-386317.iam.gserviceaccount.com;KeyFilePath=C:\ProgramData\StarQuest\sqdr\potent-veld-386317-9e83f0afa52a.json;OAuthMechanism=0;EnableSession=1

Note:  EnableSession=1 is a required parameter for SQDR.  This parameter permits the use of Transactions.

Text files

Database=C:\mydest;AllowUpdateAndDelete=1
or
Database=C:\mydest
or
Database=L:\Transfer;AllowUpdateAndDelete=1;TableType=Character;Delimiter=|;DataFileExtension=CSV;FirstLineNames=1

For details, see Using the SQDR Text Driver.

Tibero

Server=myhost;Port=8629;Database=mydb

IBM i Access (aka iSeries Access, Client Access)

System=mysystem; CommitMode=1; DefaultLibraries={QGPL,SQDR}

where mysystem is the name of a system that has been configured with System i Navigator.

It is important to specify CommitMode=1 (*CS - read committed) when a Db2 for i source is being used for incremental replication.

Using DefaultLibraries is optional, but simplifies configuration when using SQDR Plus.

System=mysystem;CommitMode=1;DefaultLibraries={QGPL,SQDR};Database=IASP1

where IASP1 is the name of a database name (RDB entry) associated with an Independent Auxiliary Storage Pool (IASP).

IBM Db2 ODBC driver (Db2 Connect)

Hostname=myhostip;port=446;Database=MYRDB;Protocol=TCPIP;LongDataCompat=1;Authentication=SERVER

Hostname=myhostip;port=50000;Database=MYRDB;Protocol=TCPIP;LongDataCompat=1;Authentication=ENCRYPT

LOB considerations:

LongDataCompat=1 is required for correct replication of LOB data types. When replicating larger LOB columns, set LOBMaxColumnSize and the SQDR service property Inderterminant Column Size to the maximum value to be used.

SSL:
Hostname=myhostip;port=50448;Database=MYRDB;Protocol=TCPIP;;LongDataCompat=1;Authentication=ENCRYPT;
SECURITY=SSL;
Ssl_client_keystoredb=C:\mySSLdir\mykeystore.kdb;
Ssl_client_keystash=C:\mySSLdir\mykeystore.sth

Make sure that the stash file (mykeystore.sth) is readable by the user SYSTEM (or whatever user ID the SQDR service is running as).