using remote_query with SQL Serverless Warehouse

[dsay96]

Hello, 

Im trying to set up remote_query() as an option for our developers to use when querying our DB2 server. Unfortunately the connection keeps timing out. The workspace is in a VPC in AWS and the interactive clusters can successfully use the JDBC connection from Unity Catalog to access tables in the DB2. For serverless we set up and established an NCC private endpoint rule, an AWS PrivateLink endpoint, AWS NLB, and a target group. All elements of the networking part are healthy and accepted. What I think is the issue would be that there is no way to pass the TSL certs in the handshake between dbx warehouse and db2 server. Has anyone gotten this flow working before? 

[Satyasai]

Hi @dsay96 

Try these steps 

Troubleshooting & Resolution Steps
1. Pass DB2 SSL Parameters directly in the Unity Catalog JDBC Connection
Within the Unity Catalog Connection definition you're using within remote_query(), pass the JDBC URL or options for explicit SSL properties:

Option A: Explicit CA Certificate (Recommended)
Pass the CA certificate file path (if uploaded to a UC Volume) or embed the truststore parameters in the Connection Options:

Plaintext
sslConnection=true;sslTrustStoreLocation=/Volumes/main/default/jars/db2_truststore.jks;sslTrustStorePassword=your_password;
(Make sure the SQL Warehouse's service principal / users have READ VOLUME access to the UC Volume containing the .jks or .pem file).

Option B: Test with Unverified SSL (Isolation Test)
To see if the TLS is actually causing the timeout, temporarily relax certificate validation in your UC Connection string properties:

Plaintext
sslConnection=true;sslTrustStoreLocation=null;
(Or setting the driver property: sslConnection=true with trustServerCertificate properties depending on your specific DB2 JDBC driver version.

2. Validate PrivateLink Port & Target Group routing
Since Serverless will be using your Network Connectivity Config (NCC) Private Endpoint Rule:

DB2 typically uses port 50000 (non-SSL) and 50001 (SSL). Ensure your AWS NLB listener and Target Group are explicitly listening on the SSL port (e.g., 50001) and forwarding to the correct DB2 port.

Check that the Security Group attached to the AWS NLB explicitly allows ingress traffic from the Serverless Subnet CIDR assigned to your Databricks NCC endpoint.

[dsay96]

For generic Unity Catalog JDBC connections on Serverless SQL, can the isolated JDBC environment use a custom JKS truststore supplied through a Unity Catalog Volume? The documentation currently states that SSL certificates are not supported. We use JTOpen/JT400 and need a private CA certificate to connect to IBM i over TLS port 9471.

[dsay96]

@Satyasai 

[balajij8]

@dsay96 You can use interactive compute or try to replace the private CA cert with a public CA cert on the IBM server. If the server presents a certificate from a recognized public CA (DigiCert etc), the serverless runtime's default ca certs truststore may validate it automatically and hence no custom truststore may be needed.

[aayush_410]

A few things worth separating out here, because the symptom (timeout, not a handshake/certificate error) points away from your working theory:

Question the diagnosis first: if the TLS handshake itself were the problem — no way to present/validate a cert — you'd typically see an SSL handshake failure or certificate validation error, not a connection timeout. A timeout with all networking components showing healthy/accepted is the classic signature of a different, more common serverless PrivateLink issue: DNS interception isn't engaging for the specific hostname you're using in the connection string. When it works correctly, the DB2 FQDN resolves to a Databricks-internal address and routes out through the private endpoint interface. When it's not engaging, traffic falls back to the normal eth0 path — which then can't reach your DB2 server at all (it's private), and you get exactly the timeout you're describing, even though every NCC/PrivateLink/NLB component reports healthy in isolation.

To confirm this before chasing TLS:

From a serverless notebook, run nslookup <db2-hostname> and ip route get <resolved-ip>.
Confirm the hostname resolves to a Databricks-internal address (not a public IP) and that the route leaves via the private endpoint interface, not eth0.
Make sure the exact FQDN you're using in the remote_query() connection string is the literal domain registered in your NCC private endpoint rule — no CNAME chasing, no alternate hostname. A mismatch here is the single most common cause of this exact "everything's healthy but it times out" pattern.

On the TLS question specifically — there is a place to pass certs for this flow, it's just not a client-side JDBC property the way it would be on a classic cluster. For remote_query()/Lakehouse Federation connections, the cert handling lives on the Unity Catalog Connection object itself (Catalog Explorer → Create/Edit Connection), not in a per-query connection string. If your DB2 server uses a cert from a private/internal CA, that CA needs to be added to the UC Connection's trust configuration — that's the mechanism you're missing, not a fundamental gap in the flow. Since your interactive cluster already connects successfully via JDBC, grab the CA chain it's trusting (openssl s_client -connect <db2-host>:<port> -showcerts from that cluster will dump the full chain) and add that same chain to the UC Connection used by serverless.

If you want to escalate to Databricks support/community with a solid report, include: NCC ID + workspace region, the private endpoint/VPC endpoint service ID, the exact hostname:port used in the remote_query() config, the nslookup/ip route get output from a serverless notebook, and proof the same hostname:port works from inside the VPC on a trusted host. That's the standard ask for this failure mode and gets you a much faster answer than iterating on TLS theories.

[Louis_Frolio]

Hello @dsay96, I took a look at both internal and external documentation and here is what I found.

TLS probably isn't your blocker. @aayush_410 called it: a bad certificate throws a handshake or PKIX error, not a timeout. A timeout with every component reporting healthy means the driver is reaching for something the private path doesn't carry. Since you're on JTOpen/JT400 against IBM i, here's the order I'd work through.

1. Turn on the preview. JDBC connections on a serverless SQL warehouse need "Enable networking for isolated workloads in Serverless SQL Warehouses" enabled under workspace Previews, and the warehouse has to be on 2025.40 or later. Without it, the sandbox the driver runs in has no outbound network and every attempt hangs. Clusters don't have this gate, which fits your symptom. Also confirm the querying user has USE CONNECTION.

2. Fix the ports. JT400 by default hits the server mapper on 449, then the signon server (9476 with TLS), and only then the database server on 9471. If your NLB only forwards 9471, that first hop goes nowhere and the driver waits until it times out. The cluster in your VPC can route to every port, so it works there. Set secure=true;portNumber=9471 in the URL; with portNumber set, the driver skips the port mapper and the signon server entirely. Also confirm the hostname in the URL is exactly the FQDN on the NCC private endpoint rule (DNS chasing isn't supported), that the rule shows ESTABLISHED, and that "Enforce inbound rules on PrivateLink traffic" is Off on the endpoint service. One note on @Satyasai's sslConnection and sslTrustStoreLocation properties: those belong to IBM's JCC driver for Db2 LUW and JT400 doesn't recognize them, though the listener and port angle in that reply is the right one to chase.

3. Add the certificate. The JDBC connection now has a userProvidedServerCertificate option ("User provided server certificate" in Catalog Explorer). Paste one PEM certificate (10 KB max, only the first cert is loaded) and Databricks adds it to the sandbox's default Java trust store every time the connection runs. It only establishes trust; secure=true is what turns TLS on. Drivers that use their own truststore ignore it, so don't count on a JKS in a Volume. JT400 with secure=true uses the standard Java trust store, so your private CA cert should satisfy it. Skip trustServerCertificate-style shortcuts in production. @balajij8's alternative of a public CA cert on the IBM i also works and removes this step entirely.

CREATE CONNECTION ibmi_jt400 TYPE JDBC
ENVIRONMENT (
  java_dependencies '["/Volumes/<catalog>/<schema>/<volume>/jt400.jar"]'
)
OPTIONS (
  url 'jdbc:as400://<ibmi-host>;secure=true;portNumber=9471',
  user '<user>',
  password '<password>',
  externalOptionsAllowList 'dbtable,query',
  userProvidedServerCertificate '-----BEGIN CERTIFICATE-----
<your private CA cert>
-----END CERTIFICATE-----'
);

To diagnose, the JDBC connection docs include a connectionTest Python UDF that runs nc -zv from the compute itself. Run it on the warehouse against <ibmi-host> on 9471, then 449 and 9476. Nothing reachable at all points to item 1; 9471 open but 449 closed points to item 2. If it still times out after all three, interactive compute remains the fallback, or open a support case with the redacted error, warehouse version, JT400 version, port, and NCC rule state (no credentials, keys, or account IDs).

References:

• JDBC connection: https://docs.databricks.com/aws/en/connect/jdbc-connection
• remote_query: https://docs.databricks.com/aws/en/query-federation/remote-queries
• Private connectivity to resources in your VPC: https://docs.databricks.com/aws/en/security/network/serverless-network-security/pl-to-internal-netwo...
• Manage previews: https://docs.databricks.com/aws/en/admin/workspace-settings/manage-previews
• JT400 JDBC properties (portNumber, secure): https://jt400.sourceforge.net/com/ibm/as400/access/doc-files/JDBCProperties.html
• IBM i ports for JDBC through a firewall: https://www.ibm.com/support/pages/node/686785

Regards, Louis.