Jump to content

Error connecting MS PowerBI

Dan Munns
 Share

Recommended Posts

Dan Munns Grand Master

Dan Munns

Posted
Posted

Hi all,

I get the below error when using the RScript connection for MS PowerBI

Anyone got any ideas?

Firewall settings are all good our end. 

Details: "ADO.NET: R script error.
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: C:/Program Files/R/R-3.5.1/library/RCurl/etc/ca-bundle.crt
  CApath: none
* SSL connection using TLSv1.0 / ECDHE-RSA-AES256-SHA
* Server certificate:
* 	 subject: C=GB; ST=Middlesex; L=Ruislip; O=Hornbill Service Management Limited; OU=Marketing; CN=*.hornbill.com
* 	 start date: 2018-06-13 00:00:00 GMT
* 	 expire date: 2020-03-23 12:00:00 GMT
* 	 subjectAltName: eurapi.hornbill.com matched
* 	 issuer: C=US; O=DigiCert Inc; OU=www.digicert.com; CN=GeoTrust RSA CA 2018
* 	 SSL certificate verify ok.
> POST /STB/xmlmc/reporting/?method=reportRun HTTP/1.1

Host: eurapi.hornbill.com

Content-Type: text/xmlmc

Cache-control: no-cache

Accept: text/xml

Authorization: ESP-APIKEY <REDACTED>

Content-Length: 157



* upload completely sent off: 157 out of 157 bytes
< HTTP/1.1 404 Not Found

< Server: nginx

< Date: Tue, 09 Oct 2018 10:43:42 GMT

< Content-Type: text/html

< Content-Length: 162

< Connection: keep-alive

< 

* Connection #0 to host eurapi.hornbill.com left intact
Error: 1: Opening and ending tag mismatch: hr line 5 and body
2: Opening and ending tag mismatch: body line 3 and html
3: Premature end of data in tag html line 1
Execution halted
"

And the code I am using from Github (PowerBIDataSource_Report.R) 

#Define Instance Details
instanceName = "REDACTED"
instanceZone = "eur"

# Define API Key
apiKey = "REDACTED"

# Define Report details
reportID = "59"
reportComment = "All Open Finance Requests"
deleteReportInstance <- TRUE

# Suspend for X amount of seconds between checks to see if the report is complete
suspendSeconds <- 10

# Import dependencies
library('RCurl')
library('XML')

# Build XMLMC URL
arrUrl <- c("https://", 
            instanceZone, 
            "api.hornbill.com/", 
            instanceName)

xmlmcURL <- paste(arrUrl, collapse="")
curl <- getCurlHandle(verbose=TRUE)

# invokeXmlmc - take params, fire off XMLMC call
invokeXmlmc = function(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
  invokeThisURL = paste(xmlmcURL, "/xmlmc/", service, "/?method=", xmethod, sep="")
  
  # Build Headers
  espKeyAuth = paste('ESP-APIKEY ', apiKey, sep="")
  requestHeaders = c('Content-Type'='text/xmlmc',
                     'Cache-control'='no-cache',
                     'Accept'='text/xml',
                     'Authorization'=espKeyAuth)
  
  responseFromURL = getURL(	url <- invokeThisURL,
                         curl = curl,
                         postfields=request,
                         httpheader=requestHeaders,
                         verbose=TRUE,
                         async=FALSE,
                         .opts = list(timeout = 3))
  return(responseFromURL)
}

### Kick off report run, get job ID
# Build XMLMC Request
arrXmlmcParams <- c(	"<params>",
                     "<reportId>", reportID, "</reportId>",
                     "<comment>", reportComment, "</comment>",
                     "</params>")

reportRunResponse <- invokeXmlmc("reporting", "reportRun", arrXmlmcParams)
xmltext  <- xmlTreeParse(reportRunResponse, asText <- TRUE,useInternalNodes=T)
runID <- unlist(xpathApply(xmltext,'//methodCallResult/params/runId',xmlValue))

reportSuccess <- FALSE
reportComplete <- FALSE

if(runID > 0){

  repeat {
    Sys.sleep(suspendSeconds)
    # Check status of report
    arrXmlmcRequest <- c(	"<params>",
                          "<runId>", runID, "</runId>",
                          "</params>")
    xml.request <- paste(arrXmlmcRequest)

    reportRunStatus <- invokeXmlmc("reporting", "reportRunGetStatus", xml.request)
    xmlRunStatus  <- xmlTreeParse(reportRunStatus, asText <- TRUE,useInternalNodes=T)
    runStatus <- unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/status',xmlValue))
    runComp <- grepl(runStatus, "completed")
   
    if ( runComp == TRUE ){
      reportCSVLink <- unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/csvLink',xmlValue))
      reportSuccess <- TRUE
      reportComplete <- TRUE
      break;
    } else if ( runStatus == "failed" ){	
      reportSuccess <- FALSE
      reportComplete <- TRUE
      break;
    }
  }
}

Sys.sleep(1)

if(reportSuccess == FALSE) {	
  stop()
}
# Now go get CSV 
# Build Invoke URL
getDavUrl = paste(xmlmcURL, "/dav/reports/", reportID, "/", reportCSVLink, sep="")
# GET request for report CSV content
espKeyAuth <- paste('ESP-APIKEY ', apiKey, sep="")
requestHeaders <- c('Content-Type'='text/xmlmc',
                    'Authorization'=espKeyAuth)
reportContent <-  getURI(	url <- getDavUrl, httpheader=requestHeaders, async=FALSE)

#Now go delete the report run instance  
if(deleteReportInstance == TRUE) {
  Sys.sleep(1)
  arrXmlmcRequest <- c(	"<params>",
                        "<runId>", runID, "</runId>",
                        "</params>")
  xml.request <- paste(arrXmlmcRequest)
  reportDeleteHist <- invokeXmlmc("reporting", "reportRunDelete", xml.request)
}

## CSV vector in to data frame object
output <- read.csv(textConnection(reportContent), header = TRUE)

 

Link to comment
Share on other sites
Victor Grand Master

Victor

Posted
Posted
9 minutes ago, Dan Munns said:

I created a new key and it works now. 

Good news... have you created it against the same user or... ?

14 minutes ago, Dan Munns said:

why does the @Hornbill Support Team not notify all members of said team

Because it is not configured as such... This user is a remnant from SW and it has not been revamped .... yet

11 minutes ago, Dan Munns said:

Ok so you can ignore me.

Yeah, like this is an option... :P

Link to comment
Share on other sites
Dan Munns Grand Master

Dan Munns

Posted
  • Author
Posted
2 minutes ago, Victor said:

Good news... have you created it against the same user or... ?

Same user. Mine :) 

2 minutes ago, Victor said:

Because it is not configured as such... This user is a remnant from SW and it has not been revamped .... yet

May I add this as a suggestion for the revamp? Or team accounts (SM / PM / etc) to send notifications to the relevant team members (might stop people using @Victor so much :))
 

Quote
  13 minutes ago, Dan Munns said:

Ok so you can ignore me.

Yeah, like this is an option... :P

Well, you could try..... lol :P 

Link to comment
Share on other sites
  • 1 year later...
MizeelA Rookie

MizeelA

Posted
Posted

Hi All, 

I am having the below issue when refreshing my hornbill Power bi report . I have regenerated the keys via systems - organisation data - users - system administrator but after refreshing it, this didn't fix the issue.

The error message from Power query 

DataSource.Error: ADO.NET: R script error.
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: C:/Users/michael.amofa/Documents/R/win-library/3.5/RCurl/etc/ca-bundle.crt
  CApath: none
* error:1407742E:SSL routines:SSL23_GET_SERVER_HELLO:tlsv1 alert protocol version
* Closing connection 0
Error in function (type, msg, asError = TRUE)  : 
  error:1407742E:SSL routines:SSL23_GET_SERVER_HELLO:tlsv1 alert protocol version
Calls: invokeXmlmc -> getURL -> curlPerform -> <Anonymous> -> fun
Execution halted

Details:
    DataSourceKind=R
    DataSourcePath=R
    Message=R script error.
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: C:/Users/michael.amofa/Documents/R/win-library/3.5/RCurl/etc/ca-bundle.crt
  CApath: none
* error:1407742E:SSL routines:SSL23_GET_SERVER_HELLO:tlsv1 alert protocol version
* Closing connection 0
Error in function (type, msg, asError = TRUE)  : 
  error:1407742E:SSL routines:SSL23_GET_SERVER_HELLO:tlsv1 alert protocol version
Calls: invokeXmlmc -> getURL -> curlPerform -> <Anonymous> -> fun
Execution halted

    ErrorCode=(2147467259)
    ExceptionType=Microsoft.PowerBI.Scripting.R.Exceptions.RScriptRuntimeException

I checked the certificate  but it seems this was revoked in 2018 but the report stopped working thsi friday.

*   CAfile: C:/Users/michael.amofa/Documents/R/win-library/3.5/RCurl/etc/ca-bundle.crt

image.png.81087d0231620cbf8aa4998eed84f430.png

Could any please advise?

Regards

MizeelA

Link to comment
Share on other sites
Steve G Mentor

Steve G

Posted
Posted

Hi @MizeelA,

This error is due to some security hardening, where we've switched-off TLS 1.0 & 1.1. To support this change, I've released a new version of those R scripts, which can be found here:

https://github.com/hornbill/rPowerBIHornbillDataSources

There are a couple of other changes in those scripts (different dependencies etc), so please review the readme before attempting to implement.

Kind regards,

Steve

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...