How to connect SAP Hana from RstudioConnect. Rshiny

Posit Professional & Hosted Products Posit Connect
, , , , , ,
1

**Warning: Error in .jcall: com.sap.db.jdbc.exceptions.SQLInvalidAuthorizationSpecExceptionSapDB: [10]: authentication failed

**Already Tried Below Solutions Which not working-

First Solution

username <- input$username2
        password <- input$password2
        sso_url <- 'https://sso.company_Name.com/app/company_Name_phnhdb_1/exk9yg60ufqxqAX5G1t7/sso/saml'
        okta_url <- 'https://company_Name.okta.com'
        jdbc_uri <- 'jdbc:sap://phnhdb.dx.xyz.com:30015'
        jar_location <- './lib/ngdbc-2.4.56.jar'
        auth_response <- POST(paste(okta_url, 'api/v1/authn', sep='/'),body = toJSON(list(username = username, password = password)),add_headers(.headers = c("Content-Type" = "application/json", "Accept"="application/json")))
        token <- fromJSON(content(auth_response, "text"))['sessionToken']
        session_response <- POST(paste(okta_url, 'api/v1/sessions', sep='/'),body = toJSON(token),add_headers(.headers = c("Content-Type" = "application/json", "Accept"="application/json")), verbose())
        cookie <- fromJSON(content(session_response, "text"))["id"]
        saml_response <- read_html(GET(sso_url, add_headers(.headers = c("Cookie" = paste("sid", cookie, sep='=')))))
        inputP <- saml_response %>% html_nodes("input") %>% xml_attr("value")
        saml_assertion <- rawToChar(base64decode(inputP[1]))
        jdbcDriver <- JDBC(driverClass="com.sap.db.jdbc.Driver",  classPath="./lib/ngdbc-2.4.56.jar")
        conn <- dbConnect(jdbcDriver, jdbc_uri, '', saml_assertion)
        query<- paste0("select * from tablename limit 1000)
        Data<- dbGetQuery(conn, query)

Returns Above Error

Second Solution Through DSN

conn<- dbConnect(odbc::odbc(), "HODBC")

# HODBC is the DSN from windows x64 bit

Returns Above Error

Session Info

sessionInfo()
R version 3.6.3 (2020-02-29)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 17763)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] readr_1.3.1           htmltools_0.5.0       shinyWidgets_0.5.3    shinyjs_1.1           shinycssloaders_1.0.0
 [6] shinydashboard_0.7.1  waiter_0.1.2          base64enc_0.1-3       rvest_0.3.6           xml2_1.3.2           
[11] rjson_0.2.20          httr_1.4.2            stringr_1.4.0         shinyalert_1.1        ape_5.4-1            
[16] sunburstR_2.1.4       highcharter_0.8.2     purrr_0.3.4           cluster_2.1.0         igraph_1.2.5         
[21] factoextra_1.0.7      RJDBC_0.2-8           rJava_0.9-12          DBI_1.1.0             plotly_4.9.2.1       
[26] ggplot2_3.3.2         ibmdbR_1.50.0         arules_1.6-6          Matrix_1.2-18         odbc_1.2.2           
[31] RODBC_1.3-16          readxl_1.3.1          DT_0.15               data.table_1.13.0     dplyr_1.0.2          
[36] bs4Dash_0.5.0         shiny_1.5.0          

loaded via a namespace (and not attached):
 [1] nlme_3.1-144      xts_0.12-0        lubridate_1.7.9   bit64_4.0.2       tools_3.6.3       backports_1.1.7   utf8_1.1.4       
 [8] R6_2.4.1          rpart_4.1-15      lazyeval_0.2.2    colorspace_1.4-1  withr_2.2.0       tidyselect_1.1.0  bit_4.0.4        
[15] curl_4.3          compiler_3.6.3    cli_2.0.2         labeling_0.3      scales_1.1.1      askpass_1.1       digest_0.6.25    
[22] pkgconfig_2.0.3   fastmap_1.0.1     htmlwidgets_1.5.1 rlang_0.4.7       TTR_0.24.0        rstudioapi_0.11   quantmod_0.4.17  
[29] farver_2.0.3      generics_0.0.2    zoo_1.8-8         jsonlite_1.7.0    crosstalk_1.1.0.1 magrittr_1.5      rlist_0.4.6.1    
[36] Rcpp_1.0.5        munsell_0.5.0     fansi_0.4.1       lifecycle_0.2.0   stringi_1.4.6     yaml_2.2.1        MASS_7.3-51.5    
[43] grid_3.6.3        blob_1.2.1        parallel_3.6.3    promises_1.1.1    ggrepel_0.8.2     crayon_1.3.4      lattice_0.20-38  
[50] hms_0.5.3         pillar_1.4.6      glue_1.4.1        packrat_0.5.0     rpart.plot_3.0.8  vctrs_0.3.2       httpuv_1.5.4     
[57] cellranger_1.1.0  gtable_0.3.0      openssl_1.4.2     tidyr_1.1.1       assertthat_0.2.1  mime_0.9          xtable_1.8-4     
[64] broom_0.7.0       later_1.1.0.1     rsconnect_0.8.16  viridisLite_0.3.0 tibble_3.0.3      ellipsis_0.3.1

Let me know if any solution on this, I tried various from the Internet, but not able to connect SAP HANA from Rstudio Connect

2

Have you configured the HODBC DSN on the RStudio Connect server? Do you have an ODBC driver for SAP Hana installed? What is the output of

isql HODBC

executed in a terminal on that server?

When trying to use ODBC connections it is often best to first make sure these work outside of R, i.e. using isql.

3

From the Rstudio Server side is the Admin part
I am not sure whether it is configured there or not. Rstudio Connect OS is CentOS I am not sure about this.

where I need to execute isql HODBC command,
I have tried it in the Rstudio console as well as terminal but it gives an error.

I am a Windows user...

Can you please guide me on this

4

Who is administering the Linux server that hosts RStudio Connect? Doing this sort of configuration will require root/sudo privileges. Do you have those? You will have to connect to the server using SSH, which is typically done using PuTTY on Windows.

Here the steps you need to do:

1 Like