SAP IQ Connection Details

Introduction

Connector Version

This documentation is based on version 25.0.9368 of the connector.

Get Started

SybaseIQ Version Support

The connector enables connectivity to SybaseIQ Database (versions 15 and 16 supported) and SAP SQL Anywhere (version 17 supported) through the TDS protocol.

Establish a Connection

Connect to SybaseIQ

To connect to the SybaseIQ, specify the following connection properties:

• Server: Set this to the name or network address of the SybaseIQ or SAP SQL Anywhere database instance.
• Database: Set this to the name of the SybaseIQ or SAP SQL Anywhere database running on the specified Server.

Optionally, you can also secure your connections with TLS/SSL by setting UseSSL to true.

Authenticate to SybaseIQ

SybaseIQ supports several methods for authentication including basic, Kerberos, and, LDAP.

Basic

Set the AuthScheme to Basic and set the following connection properties to use SybaseIQ authentication.

• User: Set this to the username of the authenticating SybaseIQ user.
• Password: Set this to the username of the authenticating SybaseIQ user.

LDAP

To connect with LDAP authentication, you will need to configure SybaseIQ server-side to use the LDAP authentication mechanism.

After configuring SybaseIQ for LDAP, you can connect using the same credentials as basic authentication.

Kerberos

To leverage Kerberos authentication, begin by enabling it via the following connection property:

• AuthScheme: Set to Kerberos will be used for authentication to SybaseIQ.

See Using Kerberos information regarding the connection properties that need to be set for Kerberos authentication.

You can find an example connection string below:

Server=MyServer;Port=MyPort;User=SampleUser;Password=SamplePassword;Database=MyDB;Kerberos=true;KerberosKDC=MyKDC;KerberosRealm=MYREALM.COM;KerberosSPN=server-name

Use Kerberos

Kerberos

To authenticate to SybaseIQ with Kerberos, set AuthScheme to KERBEROS.

Authenticating to SybaseIQ via Kerberos requires you to define authentication properties and to choose how Kerberos should retrieve authentication tickets.

Retrieve Kerberos Tickets

Kerberos tickets are used to authenticate the requester's identity. The use of tickets instead of formal logins/passwords eliminates the need to store passwords locally or send them over a network. Users are reauthenticated (tickets are refreshed) whenever they log

in at their local computer or enter kinit USER at the command prompt.

The connector provides three ways to retrieve the required Kerberos ticket, depending on whether or not the KRB5CCNAME and/or KerberosKeytabFile variables exist in your environment.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. With this option there is no need to set the User or Password connection properties.

This option requires that KRB5CCNAME has been created in your system.

To enable ticket retrieval via MIT Kerberos Credential Cache Files:

1. Ensure that the KRB5CCNAME variable is present in your environment.
2. Set KRB5CCNAME to a path that points to your credential cache file. (For example, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0.) The credential cache file is created when you use the MIT Kerberos Ticket Manager to generate your ticket.

3. To obtain a ticket:

   1. Open the MIT Kerberos Ticket Manager application.
   2. Click Get Ticket.
   3. Enter your principal name and password.
   4. Click OK.

   If the ticket is successfully obtained, the ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.

The connector uses the cache file to obtain the Kerberos ticket to connect to SybaseIQ.

Keytab File

If your environment lacks the KRB5CCNAME environment variable, you can retrieve a Kerberos ticket using a Keytab File.

To use this method, set the User property to the desired username, and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If your environment lacks the KRB5CCNAME environment variable and the KerberosKeytabFile property has not been set, you can retrieve a ticket using a user and password combination.

To use this method, set the User and Password properties to the user/password combination that you use to authenticate with SybaseIQ.

Enable Cross-Realm Authentication

More complex Kerberos environments can require cross-realm authentication where multiple realms and KDC servers are used. For example, they might use one realm/KDC for user authentication, and another realm/KDC for obtaining the service ticket.

To enable this kind of cross-realm authentication, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also, set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

Fine-Tuning Data Access

Other Properties

• QueryPassthrough: Indicates if the Jitterbit Connector for SybaseIQ will submit queries to SybaseIQ unaltered or not. By default, queries are sent as-is to the SybaseIQ.
• Timeout: How long to wait for a response from the SybaseIQ before erroring out. The default is 30 seconds.
• UseSSL: Set this to true in order to enable SSL communications.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the SybaseIQ connector.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats. For further information, see the SSLServerCert property under "Connection String Options".

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Log

For an overview of configuration settings that can be used to refine logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

SSL Configuration

Customize the SSL Configuration

To enable TLS, set the following:

• ConnectionType: SQLAnywhere
• UseSSL: True

With this configuration, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Kerberos

SSL

Schema

Miscellaneous

Authentication

This section provides a complete list of authentication properties you can configure.

AuthScheme

The scheme used for authentication. Accepted entries are Password, Kerberos.

Possible Values

Password, Kerberos

Data Type

string

Default Value

Password

Remarks

Together with Password and User, this field is used to authenticate against the server. Password is the default option. Use the following options to select your authentication scheme:

• Password: Set this to use your Password.
• Kerberos: Set this to use Kerberos authentication.

Server

The name of the server running the SybaseIQ or SAP SQL Anywhere Database.

Data Type

string

Default Value

""

Remarks

Set this property to the name or network address of the SybaseIQ or SAP SQL Anywhere database instance.

Port

The port of the SybaseIQ database.

Data Type

string

Default Value

2638

Remarks

The port of the Server hosting the SybaseIQ Database.

Database

The name of the SybaseIQ or SAP SQL Anywhere database.

Data Type

string

Default Value

""

Remarks

The name of the SybaseIQ or SAP SQL Anywhere database running on the specified Server.

User

Specifies the user ID of the authenticating SybaseIQ user account.

Data Type

string

Default Value

""

Remarks

The authenticating server requires both User and Password to validate the user's identity.

Password

Specifies the password of the authenticating user account.

Data Type

string

Default Value

""

Remarks

The authenticating server requires both User and Password to validate the user's identity.

UseSSL

This field sets whether SSL is enabled. For the Sybase IQ, it is available only when the server version is 16.1 or higher.

Data Type

bool

Default Value

false

Remarks

This field sets whether the connector will attempt to negotiate TLS/SSL connections to the server. By default, the connector checks the server's certificate against the system's trusted certificate store. To specify another certificate, set SSLServerCert.

Charset

Charset name to communicate with server.

Data Type

string

Default Value

cp1252

Remarks

Charset name to communicate with server.

ConnectionType

This option specifies whether to connect to a SybaseIQ server or a SQLAnywhere server.

Possible Values

SybaseIQ, SQLAnywhere

Data Type

string

Default Value

SybaseIQ

Remarks

This option specifies whether to connect to a SybaseIQ server or a SQLAnywhere server.

Kerberos

This section provides a complete list of Kerberos properties you can configure.

KerberosKDC

Identifies the Kerberos Key Distribution Center (KDC) service used to authenticate the user. (SPNEGO or Windows authentication only).

Data Type

string

Default Value

""

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector requests session tickets and temporary session keys from the Kerberos KDC service, which is usually co-located with the domain controller.

If KerberosKDC is not specified, the connector tries to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the connector obtains the KDC from the specified file. If it is not found there, the connector tries to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux).
• Java System Properties: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC cannot be inferred from another location, the connector infers them from the configured domain name and host.

KerberosRealm

Identifies the Kerberos Realm used to authenticate the user.

Data Type

string

Default Value

""

Remarks

A realm is a logical network, similar to a domain, that defines a group of systems under the same master KDC. Some realms are hierarchical, where one realm is a superset of the other realm, but usually realms are nonhierarchical (or “direct”) and the mapping between the two realms must be defined. Kerberos cross-realm authentication enables authentication across realms. Each realm only needs to have a principal entry for the other realm in its KDC.

The Kerberos properties are used when using SPNEGO or Windows Authentication. The connector requests session tickets and temporary session keys from the Kerberos KDC service, which is usually co-located with the domain controller. The Kerberos Realm can be configured by an administrator to be any string, but it is usually based on the domain name.

If Kerberos Realm is not specified, the connector will attempt to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the connector will obtain the default realm from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)
• Java System Properties: Using the system properties java.security.krb5.realm and java.security.krb5.kdc.
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the connector will infer them from the user-configured domain name and host. This might work in some Windows environments.

KerberosSPN

Identifies the service principal name (SPN) for the Kerberos Domain Controller.

Data Type

string

Default Value

""

Remarks

If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN to the KDC's URL.

KerberosUser

Confirms the principal name for the Kerberos Domain Controller, which uses the format host/user@realm.

Data Type

string

Default Value

""

Remarks

If there is a Kerberos principal, that Kerberos principal name should always be used to authenticate to the database.

KerberosKeytabFile

Identifies the Keytab file containing your pairs of Kerberos principals and encrypted keys.

Data Type

string

Default Value

""

Remarks

A keytab (short for “key table”) stores long-term keys for one or more principals. In most cases, end users authenticate to the KDC using their client secret (password). However, in situations where authentication or re-authentication happen using automated scripts and applications, it may be more efficient to use a keytab, which sends passwords to the KDC in encrypted form, automatically.

Keytabs are normally represented by files in a standard format, and named using the format type:value. Usually type is FILE and value is the absolute pathname of the file. The other possible value for type is MEMORY, which indicates a temporary keytab stored in the memory of the current process.

A keytab contains one or more entries, where each entry consists of a timestamp (indicating when the entry was written to the keytab), a principal name, a key version number, an encryption type, and the encryption key itself. They can be generated using kutil.

For example:

[admin@myhost]# ktutil

ktutil: addent -password -p starlord/myhost.galaxy.com@GALAXY.COM -k 1 -e aes256-cts-hmac-sha1-96
Password for starlord/myhost.galaxy.com:

ktutil: addent -password -p starlord/myhost.galaxy.com@GALAXY.COM -k 1 -e aes128-cts-hmac-sha1-96
Password for starlord/myhost.galaxy.com:

ktutil: addent -password -p starlord/myhost.galaxy.com@GALAXY.COM -k 1 -e des3-cbc-sha1
Password for starlord/myhost.galaxy.com:
ktutil: wkt /path/to/starlord.keytab

To display a keytab, use klist -k.

KerberosServiceRealm

Identifies the service's Kerberos realm. (Cross-realm authentication only).

Data Type

string

Default Value

""

Remarks

The KerberosServiceRealm is used to specify a service's KerberosRealm when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication, which means that this property would not be required. However, the property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

KerberosServiceKDC

Identifies the service's Kerberos Key Distribution Center (KDC).

Data Type

string

Default Value

""

Remarks

The KerberosServiceKDC is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication, which means that this property would not be required. However, the property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

KerberosTicketCache

Specifies the full file path to an MIT Kerberos credential cache file.

Data Type

string

Default Value

""

Remarks

Set this property if you want to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.

SSL

This section provides a complete list of SSL properties you can configure.

SSLClientCert

Specifies the TLS/SSL client certificate store for SSL Client Authentication (2-way SSL). This property works in conjunction with other SSL-related properties to establish a secure connection.

Data Type

string

Default Value

""

Remarks

This property specifies the client certificate store for SSL Client Authentication. Use this property alongside SSLClientCertType, which defines the type of the certificate store, and SSLClientCertPassword, which specifies the password for password-protected stores. When SSLClientCert is set and SSLClientCertSubject is configured, the driver searches for a certificate matching the specified subject.

Certificate store designations vary by platform. On Windows, certificate stores are identified by names such as MY (personal certificates), while in Java, the certificate store is typically a file containing certificates and optional private keys.

The following are designations of the most common User and Machine certificate stores in Windows:

For PFXFile types, set this property to the filename. For PFXBlob types, set this property to the binary contents of the file in PKCS12 format.

SSLClientCertType

Specifies the type of key store containing the TLS/SSL client certificate for SSL Client Authentication. Choose from a variety of key store formats depending on your platform and certificate source.

Possible Values

USER, MACHINE, PFXFILE, PFXBLOB, JKSFILE, JKSBLOB, PEMKEY_FILE, PEMKEY_BLOB, PUBLIC_KEY_FILE, PUBLIC_KEY_BLOB, SSHPUBLIC_KEY_FILE, SSHPUBLIC_KEY_BLOB, P7BFILE, PPKFILE, XMLFILE, XMLBLOB, BCFKSFILE, BCFKSBLOB

Data Type

string

Default Value

USER

Remarks

This property determines the format and location of the key store used to provide the client certificate. Supported values include platform-specific and universal key store formats. The available values and their usage are:

SSLClientCertPassword

Specifes the password required to access the TLS/SSL client certificate store. Use this property if the selected certificate store type requires a password for access.

Data Type

string

Default Value

""

Remarks

This property provides the password needed to open a password-protected certificate store. This property is necessary when using certificate stores that require a password for decryption, as is often recommended for PFX or JKS type stores.

If the certificate store type does not require a password, for example USER or MACHINE on Windows, this property can be left blank. Ensure that the password matches the one associated with the specified certificate store to avoid authentication errors.

SSLClientCertSubject

Specifes the subject of the TLS/SSL client certificate to locate it in the certificate store. Use a comma-separated list of distinguished name fields, such as CN=www.server.com, C=US. The wildcard * selects the first certificate in the store.

Data Type

string

Default Value

*

Remarks

This property determines which client certificate to load based on its subject. The connector searches for a certificate that exactly matches the specified subject. If no exact match is found, the connector looks for certificates containing the value of the subject. If no match is found, no certificate is selected.

The subject should follow the standard format of a comma-separated list of distinguished name fields and values. For example, CN=www.server.com, OU=Test, C=US. Common fields include the following:

SSLServerCert

Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Location

Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.

Data Type

string

Default Value

%APPDATA%\SybaseIQ Data Provider\Schema

Remarks

The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is %APPDATA%\SybaseIQ Data Provider\Schema, where %APPDATA% is set to the user's configuration directory:

BrowsableSchemas

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Tables

Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.

If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Views

Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.

If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

IncludeSystemObjects

Whether to include system objects while retrieving metadata.

Data Type

bool

Default Value

false

Remarks

Whether to include system objects while retrieving metadata.

MaxRows

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

This property sets an upper limit on the number of rows the connector returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

MaxTDSPacketSize

The protocol sending and receiving buffer size. For the lower version, it's from 512 to 512. For the newer version, it's from 512 to 2048.

Data Type

string

Default Value

512

Remarks

MaxTDSPacketSize allows you to control the protocol sending and receiving buffer size.

Other

Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.

Data Type

string

Default Value

""

Remarks

This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

QueryPassthrough

This option passes the query to the SybaseIQ server as is.

Data Type

bool

Default Value

false

Remarks

When this is set, queries are passed through directly to SybaseIQ.

Timeout

A timeout for the provider.

Data Type

int

Default Value

30

Remarks

If the Timeout property is set to 0, operations will not time out; instead, they will run until they complete successfully or encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector raises an error condition.