How to connect SAP Hana from RstudioConnect. Rshiny

Question

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 Internet, but not able to connect SAP HANA from Rstudio

Answer 1

Your connection needs to be established between R and SAP HANA, so shiny and Connect don't actually play a role here.

It seems that somebody from SAP wrote a blog post how to achieve this at Connecting R Studio to SAP HANA via JDBC. That answer proposes to use jdbc as the connector.

But it also seems possible to use an odbc driver according to HANA meets R

In general it's probably easier to configure odbc rather than jdbc, so I suggest you do the following:

• Make sure you download and install the correct ODBC driver for your system
• Configure the dataset name (DSN) appropriately
• Connect

The page Setting up ODBC Drivers at https://db.rstudio.com/ may be helpful here. Although it doesn't mention SAP HANA specifically, the principles should be similar and hopefully helpful.