Troubleshooting BOE connection error to GPDB using Data direct driver after SSL is turned on on GPDB

Post date: Jan 23, 2015 4:6:17 AM

Sample Default odbc.ini File

References:

The following is a sample odbc.ini file that Setup installs in the installation directory. All occurrences of ODBCHOME are replaced with your installation directory path during installation of the file. Values that you must supply are enclosed by angle brackets (< >). If you are using the installed odbc.ini file, you must supply the values and remove the angle brackets before that data source section will operate properly. Commented lines are denoted by the # symbol. This sample shows 32-bit drivers with file names beginning with iv. A 64-bit driver file would be identical except that driver names would begin with dd and the list of data sources would include only the 64-bit drivers.

[ODBC Data Sources]

Apache Hive Wire Protocol=DataDirect 7.1 Apache Hive WP Driver

DB2 Wire Protocol=DataDirect 7.1 DB2 Wire Protocol

dBASE=DataDirect 7.1 dBASEFile(*.dbf)

FoxPro3=DataDirect 7.1 dBASEFile(*.dbf)

Greenplum Wire Protocol=DataDirect 7.1 Greenplum Wire Protocol

Impala Wire Protocol=DataDirect 7.1 Impala Wire Protocol

Informix Wire Protocol=DataDirect 7.1 Informix Wire Protocol

MySQL Wire Protocol=DataDirect 7.1 MySQL Wire Protocol

Oracle Wire Protocol=DataDirect 7.1 Oracle Wire Protocol

PostgreSQL Wire Protocol=DataDirect 7.1 PostgreSQL Wire Protocol

Progress OpenEdge Wire Protocol=DataDirect 7.1 Progress OpenEdge Wire Protocol

Salesforce=DataDirect 7.1 Salesforce

SQLServer Wire Protocol=DataDirect 7.1 SQL Server Wire Protocol

Sybase Wire Protocol=DataDirect 7.1 Sybase Wire Protocol

Sybase IQ Wire Protocol=DataDirect 7.1 Sybase IQ Wire Protocol

Teradata=DataDirect 7.1 Teradata

Text=DataDirect 7.1 TextFile(*.*)

Informix=DataDirect 7.1 Informix

Oracle=DataDirect 7.1 Oracle

SQLServ Legacy Wire Protocol=DataDirect 7.1 SQL Server Legacy Wire Protocol

[Greenplum Wire Protocol]

Driver=ODBCHOME/lib/ivgplm27.so

Description=DataDirect 7.1 Greenplum Wire Protocol

AlternateServers=

ApplicationUsingThreads=1

ConnectionReset=0

ConnectionRetryCount=0

ConnectionRetryDelay=3

Database=<database_name>

EnableDescribeParam=1

EnableKeysetCursors=0

EncryptionMethod=0

ExtendedColumnMetaData=0

FailoverGranularity=0

FailoverMode=0

FailoverPreconnect=0

FetchRefCursors=1

FetchTSWTZasTimestamp=0

FetchTWFSasTime=0

HostName=<Greenplum_host>

HostNameInCertificate=

IANAAppCodePage=4

InitializationString=

KeyPassword=

KeysetCursorOptions=0

KeyStore=

KeyStorePassword=

LoadBalanceTimeout=0

LoadBalancing=0

LoginTimeout=15

LogonID=

MaxCharSize=

MaxLongVarcharSize=

MaxPoolSize=100

MaxVarcharSize=

MinPoolSize=0

Password=

Pooling=0

PortNumber=<Greenplum_server_port>

QueryTimeout=0

ReportCodepageConversionErrors=0

TransactionErrorBehavior=1

TrustStore=

TrustStorePassword=

ValidateServerCertificate=1

XMLDescribeType=-10

Details about these parameters visit.

http://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/userguide/rza1391515477658.html

Application Using Threads

Attribute

ApplicationUsingThreads (AUT)

Purpose

Determines whether the driver works with applications using multiple ODBC threads.

Valid Values

0 | 1

Behavior

If set to 1 (Enabled), the driver works with single-threaded and multi-threaded applications.

If set to 0 (Disabled), the driver does not work with multi-threaded applications. If using the driver with single-threaded applications, this value avoids additional processing required for ODBC thread-safety standards.

Default

1 (Enabled)

Connection Pooling

Attribute

Pooling (POOL)

Purpose

Specifies whether to use the driver’s connection pooling.

Valid Values

0 | 1

Behavior

If set to 1 (Enabled), the driver uses connection pooling.

If set to 0 (Disabled), the driver does not use connection pooling.

Notes

This connection option can affect performance.

Default

0 (Disabled)

Connection Reset

Attribute

ConnectionReset (CR)

Purpose

Determines whether the state of connections that are removed from the connection pool for reuse by the application is reset to the initial configuration of the connection.

Valid Values

0 | 1

Behavior

If set to 1 (Enabled), the state of connections removed from the connection pool for reuse by an application is reset to the initial configuration of the connection. Resetting the state can negatively impact performance because additional commands must be sent over the network to the server to reset the state of the connection.

If set to 0 (Disabled), the state of connections is not reset.

Notes

This connection option can affect performance.

Default

0 (Disabled)

Validate Server Certificate

Attribute

ValidateServerCertificate (VSC)

Purpose

Determines whether the driver validates the certificate that is sent by the database server when SSL encryption is enabled (Encryption Method=1). When using SSL server authentication, any certificate sent by the server must be issued by a trusted Certificate Authority (CA). Allowing the driver to trust any certificate returned from the server even if the issuer is not a trusted CA is useful in test environments because it eliminates the need to specify truststore information on each client in the test environment.

Valid Values

0 | 1

Behavior

If set to 1 (Enabled), the driver validates the certificate that is sent by the database server. Any certificate from the server must be issued by a trusted CA in the truststore file. If the Host Name In Certificate option is specified, the driver also validates the certificate using a host name. The Host Name In Certificate option provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.

If set to 0 (Disabled), the driver does not validate the certificate that is sent by the database server. The driver ignores any truststore information specified by the Trust Store and Trust Store Password options.

Notes

Truststore information is specified using the TrustStore and TrustStorePassword options.

Supported by Greenplum 4.2 and higher.

Default

1 (Enabled)

Encryption Method

Attribute

EncryptionMethod (EM)

Purpose

The method the driver uses to encrypt data sent between the driver and the database server. If the specified encryption method is not supported by the database server, the connection fails and the driver returns an error.

Valid Values

0 | 1 | 6

Behavior

If set to 0 (No Encryption), data is not encrypted.

If set to 1 (SSL), data is encrypted using SSL. If the server is not configured for SSL, the connection fails..

If set to 6 (RequestSSL), the login request and data are encrypted using SSL if the server is configured for SSL. If the server is not configured for SSL, an unencrypted connection is established.

Notes

This connection option can affect performance.

Supported by Greenplum 4.2 and higher.

Default

0 (No Encryption)

Query Timeout

Attribute

QueryTimeout (QT)

Purpose

The number of seconds for the default query timeout for all statements that are created by a connection. To override the value set by this connection option for an individual statement, set a different value in the SQL_ATTR_QUERY_TIMEOUT statement attribute on the SQLSetStmtAttr() function.

Valid Values

-1 | 0 | x

where:

is a positive integer that specifies a number of seconds.

Behavior

If set to -1, the query does not time out. The driver silently ignores the SQL_ATTR_QUERY_TIMEOUT attribute.

If set to 0, the query does not time out, but the driver responds to the SQL_ATTR_QUERY_TIMEOUT attribute.

If set to x, all queries time out after the specified number of seconds unless the application overrides this value by setting the SQL_ATTR_QUERY_TIMEOUT attribute.

Default

=============================================================================