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

posted Apr 28, 2017, 8:14 AM by Sachchida Ojha
Sample Default odbc.ini File
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.
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:
x
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
0
=============================================================================
Comments