Forum Discussion

mike9999's avatar
mike9999
Advocate I
1 year ago

POST/API Nonce Token with Gateway

Hi-

I'm trying to figure this out.

 

  • I have an on-prem resource.
  • I have a gateway that uses the Basic authentication method.
  • I use the Basic authentication to get a NONCE token using GET. (this works)
  • I then need to POST using this NONCE token to get the report contents for PowerBI. (this doesn't work in PowerBI but does work in python/POSTMAN)

The issue-

Web.Contents only wants to send POST requests using the anonymous authentication method. I see other examples online of people using custom powerquery however none seem to be going through a gateway. 

"An error occurred in the ‘ReportExecute’ query. DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously."

 

How do other users get reports into PowerBI from APIs that require a token and POST?

 

PowerQuery Code/Functions:

 

let
    GetCsrfToken = () as text =>
    let
        // HTTP Basic Authentication details (replace with actual username and password)
        AuthHeader = "Basic " & Binary.ToText(Text.ToBinary(Username & ":" & Password), BinaryEncoding.Base64),
        // URL
        BaseUrl = "https://INTERNALRESOURCE",
        CsrfTokenEndpoint = "/odataENDPOINTPATH/GetCSRFToken",
        TokenResponse = Web.Contents(
            BaseUrl,
            [
                RelativePath = CsrfTokenEndpoint,
                Headers = [Accept = "application/json", Authorization = AuthHeader]
            ]
        ),
        TokenJson = Json.Document(TokenResponse),
        CsrfToken = try TokenJson[NonceValue] otherwise error "CSRF token not found"
    in
        CsrfToken
in
    GetCsrfToken
let
    ExecuteReport = (ReportId as text, Parameters as text, CsrfToken as text) as record =>
    let
        // HTTP Basic Authentication details (replace with actual username and password)
        AuthHeader = "Basic " & Binary.ToText(Text.ToBinary(Username & ":" & Password), BinaryEncoding.Base64),

        // Base configuration
        BaseUrl = "https://MYINTERNALRESOURCE",
        RelativeEndpoint = "/odataENDPOINTPATH/Reporting",
        ReportExecutionEndpoint =  RelativeEndpoint & "/Reports('{report_id}')/SOMEBOILERPLATEFUNCTIONExecuteReport",
        Endpoint = Text.Replace(ReportExecutionEndpoint, "{report_id}", ReportId),
        ReportResponse = Web.Contents(
            BaseUrl,
            [
                RelativePath = Endpoint, 
                Headers = [
                    Accept = "application/json",
                    CSRF_NONCE = CsrfToken,
                    Authorization = AuthHeader
                ],
                Content = Text.ToBinary(Parameters),
                ManualStatusHandling = {400, 401, 403, 500} // Handle errors explicitly
            ]
        ),
        ReportJson = Json.Document(ReportResponse)
    in
        ReportJson
in
    ExecuteReport

 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi mike9999 ,

    Please update your Power Query code by removing the manual authorization headers and then verify if it produces the expected results. Before proceeding, ensure that the gateway data source is configured to use Basic authentication with the correct credentials. Additionally, confirm that the BaseUrl in your code precisely matches the URL configured in the gateway, including any ports and paths.

    let
        GetCsrfToken = () as text =>
        let
            BaseUrl = "https://INTERNALRESOURCE", // Match gateway data source URL
            CsrfTokenEndpoint = "/odataENDPOINTPATH/GetCSRFToken",
            TokenResponse = Web.Contents(
                BaseUrl,
                [
                    RelativePath = CsrfTokenEndpoint,
                    Headers = [Accept = "application/json"] // Gateway adds Authorization
                ]
            ),
            TokenJson = Json.Document(TokenResponse),
            CsrfToken = TokenJson[NonceValue]
        in
            CsrfToken
    in
        GetCsrfToken
    let
        ExecuteReport = (ReportId as text, Parameters as text, CsrfToken as text) as record =>
        let
            BaseUrl = "https://INTERNALRESOURCE",
            RelativeEndpoint = "/odataENDPOINTPATH/Reporting",
            ReportExecutionEndpoint = RelativeEndpoint & "/Reports('{report_id}')/ExecuteReport",
            Endpoint = Text.Replace(ReportExecutionEndpoint, "{report_id}", ReportId),
            ReportResponse = Web.Contents(
                BaseUrl,
                [
                    RelativePath = Endpoint,
                    Headers = [
                        Accept = "application/json",
                        CSRF_NONCE = CsrfToken // Gateway adds Authorization
                    ],
                    Content = Text.ToBinary(Parameters),
                    ManualStatusHandling = {400, 401, 403, 500}
                ]
            ),
            ReportJson = Json.Document(ReportResponse)
        in
            ReportJson
    in
        ExecuteReport

    Best Regards

    • mike9999's avatar
      mike9999
      Advocate I

      Thanks for the suggestion. Removing the authorization headers, allows the GetCsrfToken to work but the ExecuteReport still doesn't work. Web.Contents with the Content option doesn't want to use basic auth.

       

      Error:

      An error occurred in the ‘ReportExecute’ query. DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.
      Details:
      DataSourceKind=Web