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

michaelcwl — Thu, 06 Jul 2017 14:50:03 GMT

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))

}

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

v-sihou-msft — Fri, 07 Jul 2017 06:39:30 GMT

@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,

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

michaelcwl — Thu, 13 Jul 2017 12:11:28 GMT

Hi,

Tried your suggestion but still getting the same error.

Any other suggestions ?

Regards,

Michael

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

v-viig — Fri, 14 Jul 2017 07:54:54 GMT

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

topic Re: Trying to use R script as data source but having error for self signed certificate in Developer

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

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

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

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