Not able connect to data warehouse through Apache Knox

[#1]

Hi team,

I am currently facing issue connecting to Hive database using python libraries (pyodbc, pyhive, impyla), the Cloudera driver and Apache Knox.

While the network level connection to the relevant host and port is successful. (verified via ncat, telnet), I am unable to complete the connection and authenticate through these tools.

Connection details:

Driver=/opt/cloudera/hiveodbc/lib/64/libclouderahiveodbc64.so;"

"Host=nonprodm01.cdslindia.com;"

"Port=8443;"

"Schema=default;"

"TransportMode=http;"

"HTTPPath=/gateway/default/hive"

"SSL=0;"  # Enable SSL if Knox uses HTTPS

"AuthMech=3;"  # For username/password authentication; use 1 for no auth or 2 for Kerberos

"UID=username;"

"PWD=password;"

Knox gateway is used to access HiveServer2

Error message:

Connection failed: "('HY000', '[HY000] [Cloudera][DriverSupport] (1110) Unexpected response received from server. Please ensure the server host and port specified for the connection are correct and confirm if SSL should be enabled for the connection. (1110) (SQLDriverConnect)')."

Could you please assist in identifying the correct parameters (such as HTTPPath, transport_mode, SSL, etc) or guide me to connect through Knox with these clients.

Thank you for your support.

Kind regards,

Kedar Nandiwdekar

CDSL

Email: kedar.intern@cdslindia.com

M: +91 8452806087

[#2]

Hi @champa ,

If you are connecting to a Public Cloud Virtual Warehouse you should use port 443 and the HiveServer2 endpoint.

Check this article:

https://lighthouse.cloudera.com/s/article/How-to-solve-the-ClouderaDriverSupport-1170-Unexpected-res...

If you are not using public cloud and you are trying to connect to a on premise cluster, there 3 common causes for this:

1. SSL/TLS Configuration Mismatch (Most Likely)
The error explicitly mentions SSL, making this the primary area to check.

Knox Requires HTTPS: Knox Gateway typically runs on HTTPS (port 8443). Your client connection string must be configured for SSL/TLS.

Action: Ensure your JDBC/ODBC connection string includes the necessary parameters: ssl=true (or equivalent) and, if a self-signed certificate is used, potentially a trustedcerts or trustStore path.

2. Missing Certificate: The client often needs to trust the Knox certificate.

Action: Ensure the Knox public certificate is correctly imported into your client machine's Java TrustStore or configured in the ODBC/JDBC driver settings.

Action 1 (Check Hive): Verify the Hive configuration in your cluster manager (Cloudera Manager or Ambari). The following property should be set:

hive.server2.transport.mode = http

hive.server2.thrift.http.path = /cliservice (This is the default path)

Action 2 (Check Connection String): Ensure your connection URL is using HTTP transport and specifies the path:

Example URL: jdbc:hive2://<knox_host>:8443/;ssl=true;transportMode=http;httpPath=gateway/default/hive

3. Incorrect Host, Port, or Path (Knox Topology)
The connection string must correctly route through the Knox topology.

Parameter What to Check
Host/Port The Knox Gateway hostname and port (usually 8443), not the HiveServer2 hostname and port.
Gateway Path The path defined in your Knox topology. It is typically /gateway/<topology_name>/hive (where <topology_name> is usually default).
Knox Topology On the Knox server, verify that the Hive service role is correctly defined in the active hive.xml or default.xml topology file, pointing to the correct HS2 host and port.

Let me know if this helps.

Best Regards