Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
michaelcwl
Frequent Visitor

Trying to use R script as data source but having error for self signed certificate

Hi All,

 

i'm trying to connect to the Hornbill  Reporting API via an R scipt but reresulting in error. Must appreciated if anyone can help this.

 

Based on the hornbill documentation on BI Reporting (https://wiki.hornbill.com/index.php/PowerBI_Reporting ) I’ve set up the following on a development server:

 

 

Power BI Desktop build 2.45.4704.722 64-bit (April 2017)

Microsoft R Open 3.3.3

 

Installed below packages in R

 

RCurl

XML

jsonlite

 

setup a script to run with below parameters

instanceName

instanceZone

apiKey

 

When I try to run the R Script in Power BI desktop I get the following error:

 

Unable to Connect

 

We Encountered an error while trying to connect

 

Details: "ADO.NET: R script error.

Loading required package: methods

Loading required package: bitops

*   Trying 78.129.173.117...

* Connected to eurapi.hornbill.com (78.129.173.117) port 443 (#0)

* successfully set certificate verify locations:

*   CAfile: D:/Program Files/Microsoft/R Open/R-3.4.0/library/RCurl/etc/ca-bundle.crt

  CApath: none

* SSL certificate problem: self signed certificate in certificate chain

* Closing connection 0

Error in function (type, msg, asError = TRUE)  :

  SSL certificate problem: self signed certificate in certificate chain

Calls: invokeXmlmc ... getURL -> curlPerform -> .Call -> <Anonymous> -> fun

Execution halted

"

 

 

 

 

 

 

See below for the R script I’m running. 

 

#Define Instance Details

instanceName = "XXXX

instanceZone = "XXX"

 

# Define API Key

apiKey = “XXXXX”

 

 

# Define Report details

reportID = "121"

reportComment = "A comment to add to the report run"

 

# Import dependencies

library('RCurl')

library('XML')

 

# Build XMLMC URL

arrUrl = c("https://",

           instanceZone,

           "api.hornbill.com/",

           instanceName)

 

xmlmcURL = paste(arrUrl, collapse="")

 

# invokeXmlmc - take params, fire off XMLMC call

invokeXmlmc = function(url, key, service, xmethod, params)

{

  # Build Methodcall

  paramsrequest = paste(params , collapse="")

  arrRequest = c(               "<methodCall service=\"",

                  service,

                  "\" method=\"",

                  xmethod,

                  "\">",

                  paramsrequest,

                  "</methodCall>")

 

  request = paste(arrRequest, collapse="")

 

  # Build Invoke URL

  invokeURL = paste(url, "/xmlmc/", service, "/?method=", xmethod, sep="")

 

  # Build Headers

  espKeyAuth = paste('ESP-APIKEY ', key, sep="")

  requestHeaders = c('Content-Type'='text/xmlmc',

                     'Cache-control'='no-cache',

                     'Accept'='text/xml'

#                     ,'Authorization'=espKeyAuth

)

 

  data =  getURL(               url = invokeURL,

                  postfields=request,

                  httpheader=requestHeaders,

                  verbose=TRUE)

 

  return(data)

}

 

# suspendExec - wait for a given number of seconds

suspendExec = function(susSec)

{

  p1 = proc.time()

  Sys.sleep(susSec)

  proc.time() - p1 # The cpu usage should be negligible

}

 

### Kick off report run, get job ID

 

# Build XMLMC Request

arrXmlmcParams = c(      "<params>",

                    "<reportId>", reportID, "</reportId>",

                    "<comment>", reportComment, "</comment>",

                    "</params>")

 

reportRunResponse = invokeXmlmc(xmlmcURL, apiKey, "reporting", "reportRun", arrXmlmcParams)

xmltext  = xmlTreeParse(reportRunResponse, asText = TRUE,useInternalNodes=T)

runID = unlist(xpathApply(xmltext,'//methodCallResult/params/runId',xmlValue))

 

reportSuccess = FALSE

if(runID > 0){

  reportComplete = FALSE

  while(reportComplete == FALSE){

    # Wait a second...

    suspendExec(1)

   

    # Check status of report

    arrXmlmcRequest = c(                "<params>",

                         "<runId>", runID, "</runId>",

                         "</params>")

    xml.request = paste(arrXmlmcRequest , collapse="")

   

    reportRunStatus = invokeXmlmc(xmlmcURL, apiKey, "reporting", "reportRunGetStatus", xml.request)

    xmlRunStatus  = xmlTreeParse(reportRunStatus, asText = TRUE,useInternalNodes=T)

    runStatus = unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/status',xmlValue))

    if ( runStatus == "completed" ){

      reportCSVLink = unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/csvLink',xmlValue))

      reportSuccess = TRUE

      reportComplete = TRUE

    } else if ( runStatus == "failed" ){           

      reportSuccess = FALSE

      reportComplete = TRUE

    }

  }

}

 

if(reportSuccess == TRUE) {         

  # Now go get CSV

  # Build Invoke URL

  invokeURL = paste(xmlmcURL, "/dav/reports/", reportID, "/", reportCSVLink, sep="")

 

  # Build Headers

  espKeyAuth = paste('ESP-APIKEY ', apiKey, sep="")

  requestHeaders = c('Content-Type'='text/xmlmc',

                     'Authorization'=espKeyAuth)

 

  # GET request for report CSV content

  reportContent =  getURL(           url = invokeURL, httpheader=requestHeaders)

  ## CSV vector in to data frame object

  dataframe <- read.csv(textConnection(reportContent))

}

 

3 REPLIES 3
v-sihou-msft
Employee
Employee

@michaelcwl

 

Can you test your script in Rstudio to see if it returns same error?

 

This question seems to be more related to R. Please try to configure "ssl_verifypeer" into false to see if it works. Also refer to threads below:

 

https://stackoverflow.com/questions/29344357/how-to-get-r-to-accept-self-signed-certificates

 

https://stackoverflow.com/questions/22048703/rcurl-and-self-signed-certificate-issues

 

Regards,

Hi,

 

Tried your suggestion but still getting the same error.

 

Any other suggestions ?

 

Regards,

 

Michael

Hello @michaelcwl,

 

Could you please send your issue to pbicvsupport@microsoft.com?

We'll include our R script developer to assist you.

 

Ignat Vilesov,

Software Engineer

 

Microsoft Power BI Custom Visuals

pbicvsupport@microsoft.com

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.