Disable ssl for federated connection on Amazon Redshift

[drag7ter]

Here is a doc how to set up connection and foreign catalog, but there is no any mentions how to disable ssl for the connection.

https://docs.databricks.com/en/query-federation/redshift.html

When I set up connection and foreign catalog I get this error, when test connectivity to my redshift host:

summary: [CANNOT_ESTABLISH_CONNECTION] Cannot establish connection to remote REDSHIFT database. Please check connection information and credentials e.g. host, port, user, password and database options. ** If you believe the information is correct, please check your workspace's network setup and ensure it does not have outbound restrictions to the host. Please also check that the host does not block inbound connections from the network where the workspace's Spark clusters are deployed. ** Detailed error message: The hostname my.host.com could not be verified by hostnameverifier RedshiftjdbcHostnameVerifier.. SQLSTATE: 08001, data: {"type":"baseError","stackFrames":["org.apache.spark.SparkIllegalArgumentException: [CANNOT_ESTABLISH_CONNECTION] Cannot establish connection to remote REDSHIFT database. Please check connection information and credentials e.g. host, port, user, password and database options. ** If you believe the information is correct, please check your workspace's network setup and ensure it does not have outbound restrictions to the host. Please also check that the host does not block inbound connections from the network where the workspace's Spark clusters are deployed. ** Detailed error message: The hostname my.host.com could not be verified by hostnameverifier RedshiftjdbcHostnameVerifier.. SQLSTATE: 08001\n\tat org.apache.spark.sql.errors.QueryExecutionErrors$.cannotEstablishConnectionError(QueryExecutionErrors.scala:1222)\n\tat com.databricks.spark.redshift.RedshiftTestConnection$.testCatalogConnection(RedshiftTestConnection.scala:50)\n\tat com.databricks.sql.managedcatalog.command.QueryFederationCommand$.testCatalogConnection(queryFederationCommandsExec.scala:147)\n\tat com.databricks.sql.managedcatalog.command.TestForeignCatalogConnectionCommand.run(queryFederationCommandsExec.scala:288)\n\tat org.apache.spark.sql.execution.command.ExecutedCommandExec.$anonfun$sideEffectResult$2(commands.scala:84)\n\tat org.apache.spark.sql.execution.SparkPlan.runCommandWithAetherOff(SparkPlan.scala:180

** Detailed error message: The hostname my.host.com could not be verified by hostnameverifier RedshiftjdbcHostnameVerifier.. SQLSTATE: 08001,

When I try to connect to this host from my notebook and the same cluster, it works, but in the options I set:

.option("autoenablessl", "false")

[Alberto_Umana]

Hi @drag7ter,

The Databricks Query Federation feature for Redshift does not explicitly allow for disabling SSL directly in the UI or configuration options for the catalog. However, you can attempt to pass the JDBC connection parameters through the catalog creation.

When creating or modifying your foreign catalog, include the following in the catalog’s connection options:

CREATE FOREIGN CATALOG redshift_catalog
USING com.databricks.spark.redshift
OPTIONS (
dbtable '<table>',
forward_spark_s3_credentials 'true',
aws_iam_role 'arn:aws:iam::<your-role>',
url 'jdbc:redshift://<hostname>:5439/<database>?ssl=false&autoEnableSSL=false'
);

[drag7ter]

That doesn't work in Databricks, see the syntax of SQL for creating foreign catalog:

[PARSE_SYNTAX_ERROR] Syntax error at or near 'com': missing CONNECTION. SQLSTATE: 42601 line 2, pos 6 == SQL == CREATE FOREIGN CATALOG redshift_catalog USING com.databricks.spark.redshift

[Alberto_Umana]

It's missing connection statement, could you please try:

CREATE FOREIGN CATALOG redshift_catalog

USING CONNECTION com.databricks.spark.redshift

OPTIONS (

  dbtable '<table>',

  forward_spark_s3_credentials 'true',

  aws_iam_role 'arn:aws:iam::<your-role>',

  url 'jdbc:redshift://<hostname>:5439/<database>?ssl=false&autoEnableSSL=false'

);