Trouble Accessing Trust Store for Oracle JDBC Connection on Shared Compute Cluster

[meret]

Hi

I am trying to read data from an Oracle DB using the Oracle JDBC Driver:

df = (

spark.read.format("jdbc")

.option("url", "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(PORT=xxx)(HOST=xxx))(CONNECT_DATA=(SID=xxx)))")

.option("dbTable", "schema.table")

.option("user", "xxx")

.option("password", dbutils.secrets.get(scope = "oracle", key = "password"))

.option("driver", "oracle.jdbc.driver.OracleDriver")

.option("fetchsize", 2000)

.option("javax.net.ssl.trustStoreType", "JKS")

.option("javax.net.ssl.trustStore", "/dbfs/FileStore/certs/oracle.truststore.jks")

.option("javax.net.ssl.trustStorePassword", dbutils.secrets.get(scope = "oracle", key = "truststore_pw"))

.load()

)

If I use personal compute, the trust store is found and the connection is established successfully. If I however use a shared compute cluster, it cannot find the file:

java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

Caused by: oracle.net.ns.NetException: Unable to initialize the trust store.

at oracle.net.nt.CustomSSLSocketFactory.trustStoreFailure(CustomSSLSocketFactory.java:769)

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:418)

... 41 more

Caused by: java.nio.file.NoSuchFileException: /dbfs/FileStore/certs/oracle.truststore.jks

at sun.nio.fs.UnixException.translateToIOException(UnixException.java:86)

at sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:102)

at sun.nio.fs.UnixException.rethrowAsIOException(UnixException.java:107)

at sun.nio.fs.UnixFileSystemProvider.newFileChannel(UnixFileSystemProvider.java:177)

at java.nio.channels.FileChannel.open(FileChannel.java:287)

at java.nio.channels.FileChannel.open(FileChannel.java:335)

at oracle.net.nt.CustomSSLSocketFactory.loadFileBasedKeyStore(CustomSSLSocketFactory.java:1153)

at oracle.net.nt.CustomSSLSocketFactory.loadKeyStore(CustomSSLSocketFactory.java:1128)

at oracle.net.nt.CustomSSLSocketFactory.createSSLContext(CustomSSLSocketFactory.java:409)

... 41 more

Does anyone know how to solve this? 

I have also tried using Volumes, but with no success:

.option("javax.net.ssl.trustStore", "/dbfs/Volumes/test_catalog/oracle_jdbc_driver/certs/oracle_truststore.jks")

[meret]

Hi @Retired_mod 

Thanks for your reply.

Can you give me a concrete example on how to use a file stored in a volume? or if volume is not possible, from a distributed file system? Important for me is, that it works on a shared compute cluster, so dbfs is not an option as far as I know.

I understand the concept in general, but I struggle with the concrete implementation.

[NandiniN]

The trust store file needs to be accessible from all nodes in the shared compute cluster. You can achieve this by storing the trust store file in a location that is accessible to all nodes, such as a mounted volume or a distributed file system.

Here's some documentation on Volumes https://docs.databricks.com/en/sql/language-manual/sql-ref-volumes.html 

Make sure the file has sufficient permissions 

#!/bin/bash cp /dbfs/Volumes/test_catalog/oracle_jdbc_driver/certs/oracle_truststore.jks /tmp/oracle_truststore.jks chmod 644 /tmp/oracle_truststore.jks