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")

[Kaniz_Fatma]

Hi @meret, 

• Distributed File System: Instead of using a local file path, consider storing the trust store file in a distributed file system (e.g., DBFS, HDFS, or ADLS).
• Custom Initialization Script: You can create an initialization script that runs on each executor node during cluster startup. This script can download the trust store file from a central location (e.g., blob storage) to a local path accessible by all nodes.
• Driver-Side Initialization: If possible, load the trust store directly from a URL (e.g., HTTPS) rather than a local file path. This way, all nodes can access it without relying on local paths.
• Remember, ensuring consistent access to the trust store across all nodes is crucial for successful connections.  

[meret]

Hi @Kaniz_Fatma 

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.