why Excel doesn't succeed to make correct authorization on Jira ?

[Laurent Martin]

Hello forum

Since some days one of my macro (which should get back several tickets in an Excel file in order to set some stats and graphics), doesn't run any more and I don't found root cause ! Please is there is someone who could help me ?

Below an axtract of my source code :

Set oJiraService = New MSXML2.XMLHTTP60

With oJiraService
.Open "GET", jqlQuery, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Basic ..."
.send
sOutput = .responseText
sStatus = .Status & " | " & .statusText
End With

The token API for authorization is not written here of course :-) and variable jqlQuery is a string which represent query to get back my ticket depending several criterias.

So, when my macro execute this part, it isn't seen with correct authentification, seen as an unknown user... Consequence is : not possible to get back result...

When I execute same query on Postman, no problem, I get back my result normally. When I copy past my query in Chrome browser, it runs normally. When I copy past my query in private Chrome window, I have same error message saying tha user is not known...

I really don't understand why it doesn't run any more and moreover this same source code runs perfectly in another PC than mine...

Is there someone who have already seen that and could help me ? I guess that something is "broken" on m PC but I really don't know what... Do you have an idea ?

Thanks by advance

Lolo

[Laurent Martin]

Hello everybody,

Sorry for this answer so late, but my PC has been fully re-installed from scratch not so long ago...

Well, good news ! As I would hope, after a fully re-installed of my PC from scratch (OS, pack Office, etc...), my macro runs absolutely normally now. It was due to something, not clearly identified, located on my PC.

Thanks a lot to everybody who try to help me.

This ticket can be closed without any problem

Lolo

[Andy Heinzer]

Hi,

Given your description, the fact this works in a browser session, and the very recent Deprecation notice - Basic authentication with passwords and cookie-based authentication, it very well could be a problem with the way you have formatted that authorization header.  But it's not clear to me yet that is the problem here.  You have obscured enough information that I can't say that is the cause for sure here.

I am more concerned right now to learn the specific endpoint you are actually calling here.  I ask that because I noticed you seem to pass two headers in your call of :

.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"

If you're calling the endpoint: GET /rest/api/3/search then I would only expect to see the latter header and not the content-type.  I'm not sure what might happen to include a header that is not actually required.  It could be a factor here.   If that isn't a factor here, then I would want to further investigate your specific authorization header.  If you're calling a different endpoint please let me know so we can make sure you're supplying the correct headers for that endpoint.

Atlassian Cloud recently deprecated the use of passwords or cookie based authentication in the basic auth for REST APIs.  You can still use the basic auth method, but you have to first create an API token on https://id.atlassian.com You did mention using a token here, but I'm not clear exactly what steps you might have taken to utilize that token in the call itself.

There is a slightly different usage of this API token from a password or cookie in these kinds of API calls.  Please see Basic auth for REST APIs for details.  From that page:

Supplying basic auth headers

If you need to, you may construct and send basic auth headers yourself. To do this you need to perform the following steps:

1. Generate an API token for Jira using your Atlassian Account: https://id.atlassian.com/manage/api-tokens.
2. Build a string of the form useremail:api_token.
3. BASE64 encode the string.
4. Supply an Authorization header with content Basic followed by the encoded string. For example, the string fred:fred encodes to ZnJlZDpmcmVk in base64, so you would make the request as follows:

curl -D- \

-X GET \

-H "Authorization: Basic ZnJlZDpmcmVk" \

-H "Content-Type: application/json" \

"https://your-domain.atlassian.net/rest/api/2/issue/QA-31"

You need to use the email_address:API_Token into a string that is then base64 encoded and then use that encoded string in that basic auth header.  Maybe you are already doing that, but it isn't clear so far if you are.

Regards,

Andy

[Laurent Martin]

First thanks Andrew for your time on this ticket ! :-)

Well... To be more clear, when I speak about my "token API" I have get it back thanks to exactly same process you mentionned in your URL "https://id.atlassian.com/manage/api-tokens", no problem with that.

But, I have try your idea, suppress one tag "Content-type"... Unfortunately I have exactly same behavior... I'm sure that something (dll, lib, exe, etc...) is broken on my PC, something precisely used by my Excel macro. It's the reason for that I say with exactly same source code and exactly same user on another PC, it's ok, it run normally !

I ask fully erased and reinstall of my PC to IT team, as soon as it will be done, of course I try another time my macro and I will keep you inform to give result of this last chance attemp :-(

Best regards,

Lolo