Avoiding free text credentials in Power BI query.

[richardc1]

I have a Perfectly working query that extract data from Service now via the REST API integration.

()=>
let
clientid = "[clientid]",
clientsecret = "[shhh]",
un = "POWERBIACC",
pw = "[password]",
Token = "Bearer [blah blah blah]",

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

The issue is, my company won't accpet saved credentials in the PBIX.

Even removing them and saving once the file is published isn't acceptable, as "human's make mistakes"

It was suggested that prompring for the credenials when run would be acceptable, but I do not know if this is even possible.

Is there anyway to run this code without using saved free-text credentials?

[richardc1 · Accepted solution]

Thanks for your reply.

"Get data from the source and don't load the data in the report, just use it to log in the API. That way you are not adding text at PowerBi. "

I am not sure how I would do this, how would Power BI 'know' I had authenticated?

[ibarrau]

Hi. If the problem is about inserting the password manually at PowerBi, you could just save the credentials in a file at sharepoint, file at blob storage or even a database. Get data from the source and don't load the data in the report, just use it to log in the API. That way you are not adding text at PowerBi. 

If you want to deep dive in the scenario, you could try a custom connector for connecting to Azure Key Vaults. That might require a little more experience like working with custom connectors and creating a Key Vault, you can check it here: https://www.bibb.pro/post/securing-your-api-power-bi-data-with-azure-key-vault#:~:text=Using%20Azure...

I hope that helps,

[richardc1 · Accepted solution]

Thanks for your reply.

"Get data from the source and don't load the data in the report, just use it to log in the API. That way you are not adding text at PowerBi. "

I am not sure how I would do this, how would Power BI 'know' I had authenticated?

[ibarrau · Accepted solution]

Let me write an example. If you create a table at excel sharepoint with the following columns: 

clientid | clientsecret | user | password

Get data from the excel file. Then you can get the string of each column to authenticate at the API. You are not writing text at the powerbi literally and you can get data from the API.

Once you have connected to the API and before close & apply, click on the excel and "don't load" the table. That way it won't be visible for the model.

I hope that make sense

[richardc1]

That worked!

awesome, thanks, you've really helped me here!