Connect to SAS server from R via SAS JDBC - dbConnect() Error

[andre_young]

I am trying to connect to work SAS server from R, to extract datasets for further processing/analysis in R.

Due to corporate constraints, the only solution available is SAS JDBC type of connection, using IOM server subprotocol.

I have been trying to make this work with our IT and SAS support on and off for months now. I had them install the SAS JDBC drivers on my machine, and via trial and error arrived at the below piece of R code to establish the connection:

r

library(RJDBC)
library(rJava)
.jinit()

driver <- JDBC(driverClass = "com.sas.rio.MVADriver") 

conn <- dbConnect(
  driver, 
  "jdbc:sasiom://xxx.xxx.xxx:12345", 
  "user", 
  "pwd" 
)

running the above code I am able to create the driver object running JDBC() function, however, dbConnect throws the below exception:

r

Error in dbConnect(driver, "jdbc:sasiom://xxx.xxx.xxx:12345",  : 
  Unable to connect JDBC to jdbc:sasiom://xxx.xxx.xxx:12345
  JDBC ERROR: com/sas/util/ChainedExceptionInterface

Interestingly, using the dbConnect function with a modified connection string,

r

conn <- dbConnect(
  driver, 
  "jdbc:sasiom://xxx.xxx.xxx:12345?user=user&password=pwd"
)

gives a different error:

r

Error in dbConnect(driver, "jdbc:sasiom://xxx") : 
  Unable to connect JDBC to jdbc:sasiom://xxx
  JDBC ERROR: com/sas/util/RBBase

Unfortunately neither of the error messages don't give too much to work with.

We have tried experimenting with different Java versions, and I have tripple checked that all required JAR files are accessible via CLASSPATH.

I have extensively researched the internet and asked LLMs to no avail, and so now my last hope is the community hivemind power..

java version "1.8.0_60"

SAS version 9.4 Grid M8

JDBC drivers 9.4

[andre_young]

UPDATE: 

Managed to resolve the above JDBC Errors, however, now there is a new challenge which popped up:

Looks like we have (finally!) connected to the SAS server, but, there seems to be some confusion as to which type of server the JDBC driver expects:

"JDBC ERROR: Unable to establish connection. The client has connected to a SAS Metadata Server (v1.0) when it intended to connect to a SAS Workspace Server."

It feels like we are getting close to the finish line on this one - it looks as it pertains the actual SAS Server, rather than my local Java / R / JDBC setup. If anyone has any ideas what this error means and how to fix it, please let me know

[AhmedAl_Attar]

@andre_young 

Have a look into this guide for connection examples 
SAS 9.4 Drivers for JDBC: Cookbook

Look on page 26 (32/58), at the bottom, there is JDBC URL connection example

Hope it helps

[andre_young]

thanks Ahmed, I have verified the JDBC URL I am using against the connection example in the cookbook, and it appears to be in the correct format.

[Tom]

---

@andre_young wrote:

thanks Ahmed, I have verified the JDBC URL I am using against the connection example in the cookbook, and it appears to be in the correct format.

---

But are you still using the URL that points to the Metadata server instead of the server that can actually give you data?

[andre_young]

I am still waiting for my company SAS support to confirm the correct URL - it is a complete black box - I have no visibility into the SAS server(s) configuration.

The URL I am using is the one given to me by our SAS experts specifically for the JDBC connection, and it is the same URL which my SAS Enterprise Guide uses to estabish connection (which works fine)

[Tom]

If you can't get the SAS admin to tell you what you need you might ask one of the SAS users to use the code in the answer to this thread to extract the information on the application servers from the metadata server.

https://communities.sas.com/t5/Administration-and-Deployment/How-to-extract-SAS-Management-console-i...

[AhmedAl_Attar]

Just as an FYI, Enterprise Guide is a .NET software which does not use JDBC protocol.

EG uses connection profile, which stores information such as

- Connection name:

- Connection Description:

- Metadata Server name

- Port : 8591 (Default)

- UserName:

- UserPW:

Your username  has to be registered in the SAS Metadata base in order to authenticate you first, before it can figure out your authorization settings.
So if you don't have to SAS Enterprise Guide, contact your "SAS experts"/SAS Admin and verify you are registered and authorized to access the SAS deployment 

[AhmedAl_Attar]

@andre_young 

I personally try to verify connection using multiple software packages where possible.
I would recommend finding another JDBC supporting software be it free, such as DBeaver Community Edition,  or commercial, whichever you can get access to, to try connecting to SAS via JDBC.

In the past, I have discovered different packages have different level of support to standard protocols such JDBC/ODBC. Even Driver providers, have different level of supporting/implementing Driver Specifications.

Just a thought 

[andre_young]

I am unfortunately completely limited in what software I can use by company security policies, and have no room to experiment with other JDBC supporting software