Foreign Catalog error connecting to SQL Server 2008 R2

[Nick_Pacey]

Hi,

Is there a limitation or know issue when creating a foreign catalog to a SQL Server 2008 R2?

We are successfully able to connect to this SQL Server through a JDBC connection string.  To make this work, we have to switch the Java encrypt flag to false as follow:

url = f"jdbc:sqlserver://{database_host}:{database_port};database={database_name};encrypt=false;trustServerCertificate=true;"

This works and allows reads through our notebook.  When we then try to create a federated connection using the same credentials, this also works - but there is no flag for "encrypt" to set.  When we then create a foreign catalog linked to this connection, it creates the catalog and appears to be okay.  The error occurs when we try to read the SQL Server database from the catalog browser or notebook.  The error appears to be some sort of namespace list  error.

summary: [FAILED_JDBC.UNCLASSIFIED] Failed JDBC jdbc: on the operation: Failed list namespaces SQLSTATE: HV000, data: {"type":"baseError","stackFrames":["org.apache.spark.sql.AnalysisException: [FAILED_JDBC.UNCLASSIFIED] Failed JDBC jdbc: on the operation: Failed list namespaces SQLSTATE: HV000\n\tat org.apache.spark.sql.jdbc.JdbcDialect.classifyException(JdbcDialects.scala:798)\n\tat org.apache.spark.sql.jdbc.JdbcDialect.classifyException(JdbcDialects.scala:782)\n\tat org.apache.spark.sql.jdbc.MsSqlServerDialect.classifyException(MsSqlServerDialect.scala:348)\n\tat

I'm not sure if this error is linked to the encrypt flag that we cannot set at the federated Connection level?  I can't see any other options or levers we can change.  

Any help appreciated!

Nick

[mark_ott]

There are known limitations and issues when connecting to SQL Server 2008 R2, particularly around encryption and JDBC settings, which can manifest as errors in federated catalog operations—even though a direct JDBC connection might succeed if the "encrypt" flag is set to false.​

Encryption Handling and JDBC Connections

• SQL Server 2008 R2 only supports up to TLS 1.0 by default, and newer JDBC drivers tend to enforce encryption by default, expecting TLS 1.2 or higher.​

• When connecting with JDBC, explicitly setting encrypt=false and trustServerCertificate=true bypasses encryption, which is known to work for legacy SQL Server instances.​

• Federated connections and foreign catalogs (typically managed by frameworks like Spark, Databricks, or other federated query engines) may not expose all connection string flags, notably encrypt, so legacy compatibility settings can't always be replicated at the catalog level.​

Error Interpretation

• The error you encountered (SQLSTATE: HV000, [FAILED_JDBC.UNCLASSIFIED] Failed JDBC jdbc: on the operation: Failed list namespaces) suggests the federated system tries to establish a secure connection (potentially with encryption by default) but fails due to the server’s outdated TLS support or certificate handling.​

• Not being able to set the encrypt flag at the federated connection or catalog level is a common problem, as those interfaces often don't fully expose all JDBC connection parameters needed to work with legacy servers.​

Mitigation Strategies

• Where possible, use the older JDBC driver most compatible with SQL Server 2008 R2 and avoid drivers that default to TLS 1.2+; some drivers allow advanced configuration through properties files or direct driver instantiation in code, but these options may be limited in managed/catalog connectors.​

• Upgrading the SQL Server to a newer service pack or version supporting more modern encryption protocols (TLS 1.2+) is the recommended solution, as support for legacy protocols is increasingly phased out by client libraries and drivers.​

• If upgrading is not an option, verify whether the federated query platform allows custom JDBC property injection or offers a way to wrap or proxy the connection using lower-level APIs.​

Key Takeaway

The failure to read namespaces from your foreign catalog in SQL Server 2008 R2 is most likely due to inability to disable encryption at the federated catalog or connection level, which is a documented compatibility issue for legacy SQL Server instances and modern JDBC drivers.