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

There is no proper way available to connect SAP HANA from RStudio Connect, You should Run it on local OR You can convert Data to Excel Sheet and take it as Input while execute on RSConnect

Answer 2

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.