VBA can't log in Jira

[sdeclercq]

Hello Jira Community

I am trying to develop a little Excel tool to access to Jira's table and hopefully be able to modify several records at once depending on epic and other criterias.

But for the moment I can't log in to the database.

it always return to VBA "Login Failed" even if I followed the code proposed here : https://community.atlassian.com/t5/Jira-questions/Access-JIRA-with-VBA-Excel-2010/qaq-p/590003

Is it possible that there is a setting that has to be enabled to authorize external connections? (this is a cloud jira)

Best regards

Seb

[Aditya]

Hi @sdeclercq 

The password to authenticate in a Jira Cloud is your JIRA API Token and not your enterprise password. Similarly, your username is your Email Address that you used to login into Jira Cloud.

You can generate one here:

https://id.atlassian.com/manage-profile/security/api-tokens

Pass this token as your password & your email address as your username.

Let me know if this helps.

Thanks

- Aditya

[Gustavo Miller]

I know this issue was posted a while back, but it is worth a shot.  Aditya, you mentioned that the pwd = JIRA API Token and the user the email address. Does this information needs to be "enconded" as well?

[Aditya]

Yes, you'll need to do a base64 encoding for both.

token = Base64(Email Address + ":" + Jira API Token)

Pass the token generated above in the Authorization header.

[sdeclercq]

thank you for replying to my old question.

After some tweaking I succeeded to connect last year and indeed, I had to use the base64 encoding.

Here is my code (I use the "interface" worksheet to store my URL, ID and token.)

--------------------------------------

Dim JiraService As New MSXML2.XMLHTTP60
Dim JiraAuth As New MSXML2.XMLHTTP60
Dim Réponse As String

Dim Fint As Worksheet
Set Fint = ThisWorkbook.Worksheets("Interface")
Dim XURL As String
Dim XLogin As String
Dim XToken As String
XURL = Fint.Range("URL")
XLogin = Fint.Range("Login")
XToken = Fint.Range("Token")

With JiraAuth

.Open "GET", XURL & "/rest/api/2/search?jql=filter=" & XFiltre & "&startAt=" & StartAt, False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "User-Agent", "machin" 'ajouté par moi pour que ca marche
.setRequestHeader "Authorization", "Basic " & EncodeBase64(XLogin & ":" & XToken)
.send

Réponse = .responseText
If .Status <> "200" Then
      MsgBox ("Erreur " & .Status)
      Exit Sub
End If

------------------------

and the encodebase 64  est cette fonction:

----------------

Public Function EncodeBase64(srcTxt As String) As String
Dim arrData() As Byte
arrData = StrConv(srcTxt, vbFromUnicode)
Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMElement

Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData

EncodeBase64 = objNode.Text

Set objNode = Nothing
Set objXML = Nothing
End Function

[Gustavo Miller]

Thank you very much for spending time to send me this information. It works like charm!

At first I thought it didn't, but then I realized data is in JSON. My application is XML ready; I have all kinds of methods and processes to read XML files but not JSON.

So my question would be, is there a way to retrieve data XML format? or... should i look into building a mechanism to read JSON.

Nevertheless I appreciate your help immensely.

[sdeclercq]

I made a mechanism to read Json, so I don't know about the XML.. sorry

[Gustavo Miller]

No need to be sorry. You have already helped me a lot. It got my code going and my brain is in working mode. There is an XML API request to which I am looking at:

[JRASERVER-67095] JIRA XML API takes a long time to return XML data (search request) - Create and track feature requests for Atlassian products.

So far I am in reading mode... when I get it going I'll post it! I'll make sure I'll mention your code!

Cheers!

[sdeclercq]

To write, you will need this approach: (this one is used to change the status of a task identified by its key)

Texte = "{""transition"":{""id"":""" & Nbstatus & """}}"
.Open "POST", XURL & "/rest/api/2/issue/" & key & "/transitions", False ' & "/transitions?expand=transitions.fields", False
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "User-Agent", "machin" 'ajouté par moi
.setRequestHeader "Authorization", "Basic " & EncodeBase64(XLogin & ":" & XToken)
.send Texte

'error management
If Err.Number = -2147467260 Then
Err.Clear
GoTo suite
End If
Réponse = .responseText

If .Status <> "204" Then
.Login = True
MsgBox (Réponse & Chr(10) & "Erreur " & .Status)
Exit Sub
End If

suite:

[David Semmens]

Hello - found the above information really useful but struggling to get it working with my code. I have duplicated the above and am still getting an error returned that says the project (in my JQL) does not exist in the projects list. I am assuming that the authorization has failed so I cannot access the data.

The URL I generate works fine and returns valid data when I just enter it directly into the Google top bar. I have also tried creating / using multiple tokens.

I just need to check firstly that nothing has changed with Cloud over the last 6 months that would stop the above working? Also - I am a read only account on the Jira I am accessing - would that have any impact?

The tokens I have created are both showing "Never Accessed" - so I can only assume something is going wrong wit the authorize.

Thanks in advance for anyone who can help.

[Linna Tomlinson]

Hi the above code has stopped working for us after Jira extended the token length On Jan 17, 2023.   If the API was generated before with the shorter length, then it is authenticating fine.  However all new API with the longer length is not working.  

I've updated the VBA code to ensure the variable is 512 characters and even tried 1024 characters and still doesn't work.

David Semmens not sure if that is possibly the issue you are facing.

Does anyone have any idea how to fix this?

[Chuck Schneider]

Linna,

I had the same problem. The following two things fixed it:

1. Make sure your url is using "https" not "http"
2. If you receive a "Browser not supported by JIRA" html document response, then add the following:
   1. .setRequestHeader "User-Agent", "Edg / 90.0.818.46"
      
      This tells JIRA cloud that you are using Edge version 90.0.818.46. Credit for this goes to: https://community.developer.atlassian.com/t/jira-issue-retrieval-in-excel-vba-returns-browser-not-supported-by-jira-html-document/45923/8

[David Semmens]

@Linna Tomlinson Sorry - very late to reply and trying to remember what the problem / fix was. I will post it here in case it is of use to others.

The problem was when I encoded the email + token it was inserting a line feed into the string. I used something similar to below to fix the string:

strEnc = Replace(Text, vbLf, "")

It has been working fine for a year or so now

Dave.

[allanon]

Hi @Linna Tomlinson 

Did you manage to find a solution? I have encounter the same problem

Earlier JIRA token used to look like (24char): voK7SpftuIXqJ45QLipj4D36

Now, new JIRA token are (192char): ATATT3xFfGF0XWxRGBzUCzeeOQBhLfUoUFMXN3-Y-2_mG652ZzPf5gtSmAHUEuEzkESG-XbS3nqQKdnUDbhKWEr0C8SwahBO3QgrtuEO_O12q88CIs12dVeFc1SfARqBJxy1l1NzAOpfhzpklBGI-6RR_tD4bSxGIzUjfgdtjvxjOuUBgqN-RNs=1C64EED3

and authentication does not seem to go through

Public Function UserPassBase64(UserName As String, Token As String) As String
On Error Resume Next
Dim objXML As MSXML2.DOMDocument60
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte
Dim UserNameP As String

'UserNameP = "JiraEmail:Token"
UserNameP = UserName & ":" & Token
'UserNameP = Replace(UserNameP, vbLf, "")
arrData = StrConv(UserNameP, vbFromUnicode)
Set objXML = New MSXML2.DOMDocument60
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
UserPassBase64 = objNode.text
End Function

No error in the abover code, but the following will return a status 400 (instead of 200)

Public Function GetJIRAIssues(Query As String, UserPass As String) As String
On Error Resume Next
Dim JiraService As MSXML2.XMLHTTP60
Dim json As Object

If JiraService Is Nothing Then Set JiraService = New MSXML2.XMLHTTP60

With JiraService
.Open "GET", Query
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Basic " & UserPass
'.setRequestHeader "User-Agent", "Edg / 90.0.818.46"
.send
Do Until .readyState = 4: DoEvents: Loop
If .status = "401" Then
GetJIRAIssues = ""
Else
GetJIRAIssues = JiraService.responseText
End If
End With
End Function

[allanon]

@Linna Tomlinson , just found a solution.

The problem is with objNode.text which return break line every 55ch

https://stackoverflow.com/questions/68970639/vba-encodebase64-inserts-newline-chars-every-55-characters

By introducing

UserPassBase64 = Replace(objNode.text, vbLf, "")

It will work