Establish JDBC Connection in R through single sign-on

Question

I am currently trying to connect to an Amazon Web Service database through the RJDBC package within R.

I have all the information about the driver, jarfile & URL needed in order to connect to the database, however the problem occurs with trying to get the username and password as access is given through a single sign-on site for which I get access to all other apps.

This is the current code setup I have to obtain connection to the database

jdbcConnection <- dbConnect(drv,
                               URL,
                               s3_staging_dir=Staging_Dir,
                               user=getOption("AWSUSER"),
                               password=getOption("AWSPASS"))

The credentials to access the sign on site itself does not work. Is it possible to use another method other than requiring:

 user = <>,
 password = <>)

Thanks

Answer 1

You can certainly connect to databases with RJDBC using Single Sign-On (SSO). Here's an example how you could do so with Okta SSO and Snowflake.

# load library
library(RJDBC)

# specify driver
jdbcDriver <- JDBC(driverClass="net.snowflake.client.jdbc.SnowflakeDriver", 
            classPath="/home/<your_username>/R/snowflake-jdbc-3.6.6.jar") # <-- this is where I saved the jar file

# create a connection
# this is the most critical part.
# you have to make sure you enter your SSO path as well as corp username with domain
con <- dbConnect(jdbcDriver, "jdbc:snowflake://company.us-east-1.snowflakecomputing.com/?authenticator=https://your_domain_name.okta.com/", 
                    'username@domain.com', 'password')

# to query data
# at this point, you are good to go. start querying data.
dbGetQuery(con, "select current_timestamp() as now")