2

I need to connect to Oracle DB via R, how can I enable SSL to connect The usual connection would be like:

> install.packages("RJDBC")
> library(RJDBC)
> drv <- JDBC("oracle.jdbc.driver.OracleDriver","/path/to/driver/com/oracle/oracle-thin/11.2.0.1.0/oracle-thin-11.2.0.1.0.jar”)
> conn <- dbConnect(drv, "jdbc:oracle:thin:@database:port:schema”, “user”, “passwd”)

In Java I can set the environment properties to specify the keystore etc to make JDBC connect via SSL. Something like:

String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=servername)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=servicename)))"); 
Properties props = new Properties(); 
props.setProperty("user", "scott"); 
props.setProperty("password", "tiger"); 
props.setProperty("javax.net.ssl.trustStore", 
                  "/truststore/ewallet.p12"); 
props.setProperty("javax.net.ssl.trustStoreType","PKCS12"); 
props.setProperty("javax.net.ssl.trustStorePassword","welcome123"); 
Connection conn = DriverManager.getConnection(url, props);

How do I do the equivalent in R / R Studio ?

If possible, can you show an example code please ?

Phil
  • 7,287
  • 3
  • 36
  • 66
Nova Guy
  • 505
  • 2
  • 9
  • 16

1 Answers1

1

I just ran into the same issue. This worked for me:


# install.packages("RJDBC")
props <-  c("-Djavax.net.ssl.trustStore=C:\\windows\\path\\to\\truststore.jks",
            "-Djavax.net.ssl.trustStorePassword=changeit",
            "-Djavax.net.ssl.trustStoreType=jks",
            "-Doracle.net.ssl_version=1.2")

options(java.parameters = props) 

library(RJDBC)

drv <- JDBC("oracle.jdbc.driver.OracleDriver","C:\\windows\\path\\to\\oracle\\driver\\ojdbc8-12.2.0.1.jar")

url <- "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=servername) PORT=2494))(CONNECT_DATA=(SERVICE_NAME=servicename)))"

conn <- dbConnect(drv, url, "user", "password")

chk <- dbGetQuery(conn, "SELECT 5432 betterport FROM DUAL ") 

chk

Creating the truststore was just using the java keytool with my certificate

keytool.exe -import -file C:\path\to\cert\mycert.cer -alias mycert -keystore C:\windows\path\to\truststore.jks -storepass changeit
Todd Sykes
  • 11
  • 1