Forum Discussion

Falcon's avatar
Falcon
Helper I
4 years ago
Solved

Connect API oauth2 to pull data

Hello Everyone,

I am trying to pull API data using oauth2. I have been following different methods online without success. All I have are

I am able to get the token and pull data in the Postman. However, I am not able to do that in Power BI. Can anyone provide me with a solution? Thanks in advance.

Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Falcon

    I've just noticed that the json was malformed + I've added a relative path for the auth.
    Try this:

    let
    ClientId = "XXXXXXX",
    ClientSecret = "XXXXXXX",
    Uri = "https://secure9.aladtec.com/",
    Body ="{
        ""grant_type"": ""client_credentials"",
        ""client_id"": """ & ClientId & """,
        ""client_secret"": """ & ClientSecret & """
}",
    OAuth2Response = Web.Contents(Uri, [RelativePath = "mohcacc/api/v2/oauth/token", Content=Text.ToBinary(Body)])
in
    OAuth2Response

     

13 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Hi Falcon,

    I'll need to see your actual code to figure it out.
    Instead, have a look at the following code.
    It will generate an OAuth2 access token from AAD, to the Power BI Service, using a service principal.
    You probably need an access token to another scope or not to AAD at all...

    let
    TenantId = "XXXXXX",
    ClientId = "XXXXXX",
    ClientSecret = "XXXXXX",
    Uri = "https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token/",
    Body =
    "scope=https://analysis.windows.net/powerbi/api/.default" &
    "&grant_type=client_credentials" &
    "&client_id=" & ClientId &
    "&client_secret=" & ClientSecret,
    OAuth2Response = Json.Document(Web.Contents(Uri, [Content=Text.ToBinary(Body)])),
    access_token = OAuth2Response[access_token]
in
    access_token

     

    • Falcon's avatar
      Falcon
      Helper I
      4 years ago

      Hello Sparta Bi,

       

      Here is my code by following the above instruction. The "tokenUserName" and "tokenPassword" hold "client_id" and "client_secret" respectively. The "tokenAddress" holds the URL which is "https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token". I don't have TenantId and scope as in your example. How do I get them?

       

      let body = "{
      ""username"" = " & tokenUserName & ",
      ""password"" = " & tokenPassword & "
      }",

      getToken = Web.Contents(
      tokenAddress,
      [Content = Text.ToBinary(body)]
      )
      in getToken

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Hi Falcon,

    The TenantId & scope are relevant for the AAD OAuth2 flow. In your case, you probably do not need them.

    Did you try changing

    let body = "{
""username"" = " & tokenUserName & ",
""password"" = " & tokenPassword & "
}",

    to

    let body = "{
""client_id"" = " & tokenUserName & ",
""client_secret"" = " & tokenPassword & "
}",

    ?

    If this doesn't help, please share the successful "get token" postman request.

    • Falcon's avatar
      Falcon
      Helper I
      4 years ago

      Yes, I did change username and password to client_id and client_secret as you suggested. I just got the error message

       

      DataSource.Error: Web.Contents failed to get contents from 'https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token' (400): Bad Request
      Details:
      DataSourceKind=Web
      DataSourcePath=https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token
      Url=https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token

       

      In Postman, I was able to retrieve the token with the following script in body

       

      {
      "grant_type": "client_credentials",
      "client_id": "xxxxxxxxxxxxxxxx",
      "client_secret": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

      }

       

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        Hi Falcon,

        Try this code

        let
    ClientId = "XXXXXX",
    ClientSecret = "XXXXXX",
    Uri = "https://xxxxxxxxxxx/mohcacc/api/v2/oauth/token",
    Body ="{
        ""grant_type"" = ""client_credentials"",
        ""client_id"" = """ & ClientId & """,
        ""client_secret"" = """ & ClientSecret & """,
}",
    OAuth2Response = Json.Document(Web.Contents(Uri, [Content=Text.ToBinary(Body)]))
in
    OAuth2Response
  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Falcon

    I've just noticed that the json was malformed + I've added a relative path for the auth.
    Try this:

    let
    ClientId = "XXXXXXX",
    ClientSecret = "XXXXXXX",
    Uri = "https://secure9.aladtec.com/",
    Body ="{
        ""grant_type"": ""client_credentials"",
        ""client_id"": """ & ClientId & """,
        ""client_secret"": """ & ClientSecret & """
}",
    OAuth2Response = Web.Contents(Uri, [RelativePath = "mohcacc/api/v2/oauth/token", Content=Text.ToBinary(Body)])
in
    OAuth2Response