Forum Discussion
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
GetCsrfTokenlet
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
- AnonymousNot 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 GetCsrfTokenlet 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 ExecuteReportBest Regards
- mike9999Advocate 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