Forum Discussion

Piotr_J's avatar
Piotr_J
Regular Visitor
3 years ago

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

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 c...
Need Help
Piotr_J's avatar
Piotr_J
Regular Visitor
3 years ago

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's avatar
ams1
Responsive Resident
3 years ago

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's avatar
    Piotr_J
    Regular Visitor
    3 years ago

    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

  • Piotr_J's avatar
    Piotr_J
    Regular Visitor
    3 years ago

    Dear ams1 ,

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

     

    Best Regards

    Piotr