OAuth setup for Jira - Power BI Integration

[ck_data_analyst]

Hi everyone,

I'm looking for some guidance on an OAuth setup issue I'm currently facing for Jira to Power BI integration. I'm in the process of switching my dashboard from Basic Auth to OAuth using Client Credentials. The OAuth token generation is working fine in Postman but I am unable to connect in Power BI. I have cleared the cache credentials and updated the Power Query code to use OAuth (Bearer token handled inside M). If anyone has experience with the OAuth setup for Jira Cloud in Power BI or has dealt with similar issues, I'd really appreciate any pointers on what else I may need to check.

Thanks in advance for your help.

[nielsvdc]

Hi @ck_data_analyst,

Please see attached example file for connecting to Jira using Power Query and basic authentication using a user email address and user API token.

## Basic Authentication Setup

1. JiraEmail
Your Atlassian account email address.

---

2. ApiToken
Go to: https://id.atlassian.com/manage-profile/security/api-tokens
Click Create API token or Create API token with scopes, give it a name, and copy the token.

---

3. CloudId
Go to: https://<tenant_name>.atlassian.net/_edge/tenant_info
Replace <tenant_name> with your Jira subdomain.
Copy the cloudId value from the result.

---

4. JiraConfig
Set AuthMethod = "Basic" in Power Query in the JiraConfig and fill in the three values above.

Unfortunately I have not yet been able to get OAuth 2.0 authentication working.

Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.

[v-aatheeque]

Hi @ck_data_analyst 

As suggested by @nielsvdc is the issue is resolved ?

[ck_data_analyst]

Hi @v-aatheeque ,

Issue is not resolved yet. The method that @nielsvdc shared is Basic Authentication which is not accepted by my client. I have to use only OAuth. So still trying to figure it out. Thanks

[v-aatheeque]

Hi @ck_data_analyst 

let

    ClientId        = client_id,
    ClientSecret    = client_secret,
    BoardId         = "856",
    MaxResults      = 100,

    TokenUrl  = "https://auth.atlassian.com/oauth/token",

    TokenBody =
        "grant_type=client_credentials" &
        "&client_id=" & ClientId &
        "&client_secret=" & ClientSecret &
        "&scope=" & Uri.EscapeDataString("read:jira-work read:jira-user"),

    TokenResponse =
        Json.Document(
            Web.Contents(
                TokenUrl,
                [
                    Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
                    Content=Text.ToBinary(TokenBody)
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],
    CloudList =
        Json.Document(
            Web.Contents(
                "https://api.atlassian.com/oauth/token/accessible-resources",
                [
                    Headers=[
                        Authorization="Bearer " & AccessToken,
                        Accept="application/json"
                    ]
                ]
            )
        ),

    CloudId = CloudList{0}[id],

    GetPage = (startAt as number) as record =>
        let
            QueryString = Uri.BuildQueryString(
                [
                    maxResults = Text.From(MaxResults),
                    startAt    = Text.From(startAt)
                ]
            ),

            FullUrl =
                "https://api.atlassian.com/ex/jira/" &
                CloudId &
                "/rest/agile/1.0/board/" & BoardId & "/issue?" &
                QueryString,

            Raw =
                Web.Contents(
                    FullUrl,
                    [
                        Headers=[
                            Accept="application/json",
                            Authorization="Bearer " & AccessToken
                        ]
                    ]
                ),

            Json = Json.Document(Raw)
        in
            Json,

    First     = GetPage(0),
    PageSize  = First[maxResults],
    Total     = First[total],

    PageCount = Number.RoundUp(Total / PageSize),
    IndexList = {0 .. PageCount - 1},

    AllPages =
        List.Transform(
            IndexList,
            each GetPage(_ * PageSize)[issues]
        ),

    AllIssues = List.Combine(AllPages),

    Table0 = Table.FromList(AllIssues, Splitter.SplitByNothing(), {"Column1"}),
    Final  = Table.ExpandRecordColumn(Table0, "Column1")
in
    Final

FYI : As Suggested In the above screenshot to Replace with your the client_id , client_secret and BoardId.

Hope this helps!!

Thank You.

[nielsvdc]

@v-aatheeque Your code is not correct. The Uri "https://api.atlassian.com/oauth/token/accessible-resources" does not return the cloudid (anymore). For this you need the Uri "https://" & TenantName & ".atlassian.net/_edge/tenant_info"

I did some more testing and it seems OAuth 2.0 is not working for Power Query in combination with Jira. This is because Jira is expecting 3LO (= Three-Legged OAuth: user login + consent + temporary auth code + access token), which is not supported by Power Query.

Therefor with Power Query you keep running into this problem.

[v-aatheeque]

Hi @nielsvdc 

Thanks again for pointing this out and clarification will definitely help others in the community facing similar issues. If you were able to proceed using one of the alternatives, feel free to share your approach so it can benefit others as well.

[v-aatheeque]

Hi @ck_data_analyst 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[v-aatheeque]

Hi @ck_data_analyst 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.

[ck_data_analyst]

Hi @nielsvdc,

My issue is not related to that topic(pagination). I am trying to get the data from Jira using OAuth to PowerBI. Eventhough I have given the correct credentials it says we couldn't authenticate with the credentials provided. Also I set both https://auth.atlassian.com and Jira base URL to Anonymous + Organizational.

I have used the below code: 

let
    //--------------------------------------------
    // CONFIG
    //--------------------------------------------
    JiraBaseUrl     = #"Jira instance URL", 
    BoardId         = "856",
    jira_expand     = "renderedFields",
    jira_maxResults = 1000,

    //--------------------------------------------
    // OAUTH TOKEN
    //--------------------------------------------
    ClientId        = ClientID,
    ClientSecret    = Client_Secret,

    TokenUrl  = "https://auth.atlassian.com/oauth/token",
    TokenBody =
        "grant_type=client_credentials" &
        "&client_id=" & ClientId &
        "&client_secret=" & ClientSecret,

    TokenResponse =
        Web.Contents(
            TokenUrl,
            [
                Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
                Content=Text.ToBinary(TokenBody)
            ]
        ),
    AccessToken = Json.Document(TokenResponse)[access_token],

    //--------------------------------------------
    // SERVICE SAFE CALL
    //--------------------------------------------
    GetJson = (startAt as number) =>
        let
            QueryString =
                Uri.BuildQueryString(
                    [
                        expand     = jira_expand,
                        maxResults = Text.From(jira_maxResults),
                        startAt    = Text.From(startAt)
                    ]
                ),

            FullUrl =
                JiraBaseUrl &
                "/rest/agile/1.0/board/" & BoardId & "/issue?" &
                QueryString,

            Raw =
                Web.Contents(
                    FullUrl,
                    [
                        Headers=[
                            Accept="application/json",
                            Authorization="Bearer " & AccessToken
                        ]
                    ]
                ),

            Json = Json.Document(Raw)
        in
            Json

Alternately if I change the TokenUrl="https://auth.atlassian.com"

Then I am getting error as DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.

Not able to understand how to fix this.

Any help would be greatly appreciated. Thanks in advance

[v-aatheeque]

Hi @ck_data_analyst 

Atlassian does support Client Credentials (2‑legged OAuth / 2LO) for service accounts BUT Jira Cloud does NOT allow you to use that token directly against your site’s base URL:

https://yourdomain.atlassian.net/rest/agile/1.0/...

Client‑credentials tokens only work with the Atlassian Cloud (Unified) API:
https://api.atlassian.com/ not with yourInstance.atlassian.net.

Please follow below steps.

OAuth 2.0 flow for Jira Cloud (Client Credentials).

1. Get Access Token.
2. Get your CloudID
Call:
GET https://api.atlassian.com/oauth/token/accessible-resources
Authorization: Bearer <access_token>

This returns:
[
{
"id": "your-cloud-id",
"url": "https://yourdomain.atlassian.net",
"name": "Jira Software",
...
}
]

3. Call Jira APIs using the Cloud API gateway, NOT your Jira URL

https://api.atlassian.com/ex/jira/{cloudId}/rest/agile/1.0/board/{boardId}/issue?...


Please refer below M code.

let
//--------------------------------------------
// CONFIG (EDIT THESE)
//--------------------------------------------
ClientId = "YOUR_CLIENT_ID",
ClientSecret = "YOUR_CLIENT_SECRET",
BoardId = "856",
JiraScope = "read:jira-work read:jira-user", // adjust if needed

//--------------------------------------------
// TOKEN ENDPOINT (Atlassian OAuth 2.0)
//--------------------------------------------
TokenUrl = "https://auth.atlassian.com/oauth/token",
TokenBody =
"grant_type=client_credentials" &
"&client_id=" & ClientId &
"&client_secret=" & ClientSecret &
"&scope=" & Uri.EscapeDataString(JiraScope),

TokenResponse =
Json.Document(
Web.Contents(
TokenUrl,
[
Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(TokenBody)
]
)
),

AccessToken = TokenResponse[access_token],

//--------------------------------------------
// STEP 2: GET CLOUD ID
// Required because Jira Cloud APIs must be called via api.atlassian.com
//--------------------------------------------
CloudList =
Json.Document(
Web.Contents(
"https://api.atlassian.com/oauth/token/accessible-resources",
[
Headers = [
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
),

CloudId = CloudList{0}[id],

//--------------------------------------------
// PAGED CALL FUNCTION
//--------------------------------------------
PageSize = 100,
GetPage = (StartAt as number) as record =>
let
Url =
"https://api.atlassian.com/ex/jira/" &
CloudId &
"/rest/agile/1.0/board/" & BoardId &
"/issue?maxResults=" & Number.ToText(PageSize) &
"&startAt=" & Number.ToText(StartAt),

Response =
Json.Document(
Web.Contents(
Url,
[
Headers = [
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
)
in
Response,

//--------------------------------------------
// LOOP THROUGH ALL PAGES
//--------------------------------------------
FirstPage = GetPage(0),
Total = FirstPage[total],
Pages = List.Generate(
() => FirstPage,
each _ <> null and Record.HasFields(_, "issues"),
each
let
NextStart = _[startAt] + _[maxResults],
NextPage = if NextStart < Total then GetPage(NextStart) else null
in
NextPage
),

//--------------------------------------------
// COMBINE ALL ISSUES INTO A TABLE
//--------------------------------------------
AllIssues = List.Combine(
List.Transform(
List.RemoveNulls(Pages),
each _[issues]
)
),

IssuesTable = Table.FromList(AllIssues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expanded = Table.ExpandRecordColumn(IssuesTable, "Column1")
in
Expanded


Note: Permissions / Privacy Levels in Power BI:

Use:
Authentication: Anonymous
Privacy Level: Organizational

[ck_data_analyst]

Hi  ,Thank you for the response.

I have tried the method as you said but still it says we couldn't authenticate the credentials provided.

Below is the code that I tried:

let
    //--------------------------------------------
    // CONFIG
    //--------------------------------------------
    JiraBaseUrl = #"Jira instance URL",   
    BoardId     = "856",
    MaxResults  = 100,

    ClientId     = client_id,
    ClientSecret = client_secret,

    //--------------------------------------------
    // STEP 1 — OAUTH 2.0 TOKEN
    //--------------------------------------------
    TokenUrl  = "https://auth.atlassian.com/oauth/token",

    TokenBody =
        "grant_type=client_credentials" &
        "&client_id=" & ClientId &
        "&client_secret=" & ClientSecret &
        "&scope=" & Uri.EscapeDataString("read:jira-work read:jira-user"),

    TokenResponse =
        Json.Document(
            Web.Contents(
                TokenUrl,
                [
                    Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
                    Content = Text.ToBinary(TokenBody)
                ]
            )
        ),

    AccessToken = TokenResponse[access_token],

    //--------------------------------------------
    // STEP 2 — GET CLOUD ID  
    //--------------------------------------------
    CloudList =
        Json.Document(
            Web.Contents(
                "https://api.atlassian.com/oauth/token/accessible-resources",
                [
                    Hea...

    CloudId = CloudList{0}[id],

    //--------------------------------------------
    // STEP 3 — PAGED CALL FOR ISSUES
    //--------------------------------------------
    GetPage = (startAt as number) =>
        let
            QueryString =
                Uri.BuildQueryString(
                    [
                        expand     = "renderedFields",
                        maxResults = Text.From(MaxResults),
                        startAt    = Text.From(startAt)
                    ]
                ),

            // CALL THROUGH CLOUD API GATEWAY
            FullUrl =
                "https://api.atlassian.com/ex/jira/" &
                CloudId &
                "/rest/agile/1.0/board...

            Raw =
                Web.Contents(
                    FullUrl,
                    [
                        Headers = [
                            Accept = "application/json",
                            Authorization = "Bearer " & AccessToken
                        ]
                    ]
                ),

            Json = Json.Document(Raw)
        in
            Json,

    //--------------------------------------------
    // STEP 4 — PAGINATION
    //--------------------------------------------
    First = GetPage(0),
    PageSize = First[maxResults],
    Total    = First[total],

    PageCount = Number.RoundUp(Total / PageSize),
    IndexList = {0 .. PageCount - 1},

    AllPages =
        List.Transform(
            IndexList,
            each GetPage(_ * PageSize)[issues]
        ),

    AllIssues = List.Combine(AllPages),

    //--------------------------------------------
    // STEP 5 — TABLE + FIELD EXPANSION (YOUR ORIGINAL FIELDS)
    //--------------------------------------------
    Table0 = Table.FromList(AllIssues, Splitter.SplitByNothing(), {"Column1"}),

Also I have added permissions as:Authentication: Anonymous
Privacy Level: Organizational

Thanks

@v-aatheeque

[nielsvdc]

Hi @ck_data_analyst,

Did you have a look this post https://community.fabric.microsoft.com/t5/Service/Export-Jira-V3-api-data-to-PowerBI-dataflow/m-p/48... ?

And if you are willing to pay for a Power BI connector, have a look at this: Power BI Connector for Jira | Atlassian Marketplace