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.