Rest API integration with Service Now getting refreshed tokens.

[richardc1]

I have been havin trouble connecting Power BI to Service Now using the Rest API, and bearer tokens.

Please note that I cannot use Basic Auth, and am required to use oauth authority to connect.

I have successfully connected via REST to the data tables, with a token generated from Postman, so the integration works.

But in order to utilise the integration, I am required to create a toekn dynamically.

I have two examples from my extensive research:

Generate dynamic token & get data from Custom API in Power BI (advaiya.com)

And

Some data sources may not be listed because of han... - Microsoft Fabric Community

Both use credentials to generate a token, and then the query is trasnformed into a function to be used thereafter to refresh the token.

Whilst a direct query to a data table in Service Now works with an externally sourced token:

let
Source = Json.Document(Web.Contents("https://INSTANCE.service-now.com/api/now/table/incident?sysparm_limit=1000", [Headers=[Authorization="Bearer TOKEN FROM POSTMAN"]])),
#"Converted to Table" = Table.FromRecords({Source}),

...

Trying to use either:

() =>
let
     body = "grant_type=Password&client_id=CLIENTID&client_secret=PWD&username=USER&password=PWD",
    Data=Json.Document(Web.Contents("https://service-now.com/oauth_token.do",[Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])),
    access_token = Data[access_token]
in
    access_token

Or:

let

url = "https://INSTANCE.service-now.com/oauth_token.do",

headers = [#"Content-Type" = "application/json"],

postData = Json.FromValue([username="USERNAME",

password= "PASSWORD", client_id="CLIENT ID",client_secret = "CLIENT SECRET"]),

response = Web.Contents(

url,

[

Headers = headers,

Content = postData

]

),

Data = Json.Document(response),
access_token=Data[TOKEN]

fail to authenticate (using Anonymous, as the only accepted credentials in the query)

We couldn't authenticate with the credentials provided. Please try again.

These same credentials work perfectly in Postman, the Power BI query can connect to the tables with the token thus generated, but the GetToekn query fails.

Again, I cannot use Basic Auth, 99% of the guids out there utlise basic Auth to a local Service now account, I cannot use this solution. 

I need to authorise wiht a token generated in a query.

Any help appreciated.

[novelmayank]

Hey @richardc1 ,

I am also facing similar issue. I am able to fetch the token and convert it into function but when I replace the hardcoded bearer toekn with the function in main query it starts giving me authentication error.

Any help will be highly appriciated.!

[richardc1]

So when you run the function, it spits out a bearer token no worries?

If so, then how are you calling that function in your fetch query?

[richardc1 · Accepted solution]

Managed to resolve this myself:

Added the Authorization = to body. 

body = [grant_type="password", client_id=clientid, client_secret=clientsecret, username=un, password=pw, Authorization=Token],
Data=Json.Document(Web.Contents("https://[instance].service-now.com/oauth_token.do",[Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(Uri.BuildQueryString(body))])),
access_token = Data[access_token]
in
access_token