Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
I have created a Power Query to send a request via POST method using HTTP Basic Authentication and Anonymous access, however I'm still getting an error "We couldn't authenticate with the credentials provided. Please try again."
I'm 100% sure that I am using correct credentials and API access is enabled on the server side.
The response from the server is 401 - Authentication failure.
Guidelines from API documentation:
General info
UTF-8 encoding is used in every request and server response
Only JSON format is used in every request and response
Every request should be made using HTTPS
Authentication
Every method in this API is authenticated using HTTP Basic Authentication.
As username use "calamari".
As password use API_KEY_FROM_CONFIGURATION_PANEL.
My Power Query:
let
// Define variables for the API URL and credentials
apiUrl = "https://test.calamari.io/api",
username = "calamari",
password = "my_api_key...",
// Define the request body in Json format
requestBody = "{ ""from"": ""2020-01-10"", ""to"": ""2020-01-15"" }",
// Define the options for the HTTP request
options = [ Headers = [ #"Content-Type"="application/json" ],
Content = Text.ToBinary(requestBody),
ManualStatusHandling = {400..599},
IsRetry = true,
Timeout = #duration(0, 0, 30, 0)
],
// Make the HTTP request with basic authentication
response = Web.Contents(apiUrl, [
Headers = [
#"Authorization"="Basic " & Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64)
],
Content = options[Content],
ManualStatusHandling = options[ManualStatusHandling],
IsRetry = options[IsRetry],
Timeout = options[Timeout],
Query=[#"key"="value"],
RelativePath="clockin/timesheetentries/v1/balance"
]),
// Convert the response from Json to a table
json = Json.Document(response),
table = Record.ToTable(json)
in
table
Has anyone had any experience using Power Query with the POST method and HTTP Basic Auth and ran into similar issue?
I would be grateful for any tips.
I have looked through many articles and threads, but none of the previous solutions have allowed me to successfully authenticate.
Best Regards
Piotr
Thank you for the clue.
I managed to connect to the API using Postman and the same credentials.
It seems that Power Query sends a wrong type of HTTP request using Anonymous method.
let
url = "https://test.calamari.io/api/clockin/timesheetentries/v1/balance",
body = "{""from"": ""2023-01-01"", ""to"": ""2023-01-31""}",
headers = [ #"Content-Type" = "application/json", #"Authorization" = "Basic xdgredhpdsadasdaADouio67sfsdfsdfsdf4DSAdaDA8678" ],
options = [ Headers = headers, Content = Text.ToBinary(body)],
response = Web.Contents(url, options)
in
response
I simplified the query to make it more clear.
#"Authorization" = "Basic xdgredhpdsadasdaADouio67sfsdfsdfsdf4DSAdaDA8678" header is the Base64 encoding of "username:password" (not the real one :)).
Unfortunately, I'm unable to authenticate through Power Query using the correct credentials using Anonymous methos for the data source.
Hi @Piotr_J
Great that you managed to confirm using Postman as it's a solid base for investigations.
Now, could you please share with us the cURL generated by Postman after you remove confidential information from it?
Hello @ams1 ,
please see the cURL code from Postman:
curl --location 'https://test.calamari.io/api/clockin/timesheetentries/v1/balance' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic xxXXXXXxxxxXXXXxxxxXXXXXxxxxxxxxxxxXXXXXXXXXXXXXXXXXXXXX' \
--data '{
"from": "2023-01-01",
"to": "2023-01-31"
}'
I simplifid my Power Query:
let
actualUrl = "https://test.calamari.io/api/clockin/timesheetentries/v1/balance",
options = [Headers =[#"Authorization"="Basic xxXXXXXxxxxXXXXxxxxXXXXXxxxxxxxxxxxXXXXXXXXXXXXXXXXXXXXX",
#"Content-Type" = "application/json",
#"Accept" = "application/json"
]],
result = Json.Document(Web.Contents(actualUrl, options))
in
result
The #"Authorization" header contains Base64 value copied from the Postman, so the credentials are correct.
Nevertheless, I'm still unable to authenticate using this query and Anonymous method.
Best Regards
Piotr
Hi @Piotr_J
The syntax of the query looks correct. One thing is that you didn't pass the parameter #"Content-Type"="application/json" to the header in Web.Contents(). But I think this is perhaps not the main reason. Please first use other tools like Postman to test the request. Once the request returns the response correctly, we can then compare its settings to this query to check whether this query has missed some settings.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Piotr_J
Have you tried to connect to the API using something else except PowerQuery? Ex. using Postman or cURL?
If you successfully connect using Postman or cURL (etc.), only then IMO we can be 100% sure the credentials are OK. Until then it's max 99% 😁
Please mark this as answer if it helped.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.