PowerQuery - Unable to authenticate using HTTP Basic Authentication, POST method & Anonymous access

[Piotr_J]

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

[Piotr_J]

@v-jingzhang @ams1 

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.

[ams1]

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?

[Piotr_J]

Dear @ams1 ,

is Power Query capable of performing POST method and HTTP Basic Auth?

Best Regards

Piotr

[Piotr_J]

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

[v-jingzhang]

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.

[ams1]

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.