Authentication in Powershell and API to update a dataset.

[Anonymous]

I have a working Powershell script that runs a SQL query and creates/updates results to a PowerBI dataset. The trouble is it uses my own SSO login to do it.

I want to run this as a task/service to update the dataset every X minutes. I'm looking to simplify authentication. Right now it seems I need an O365 account + PowerBI Pro user just to push data up with the API. Is that correct?

On Datasets made thru the Desktop I can change the username and password. But this is not true on datasets made through the API and set to "Push". (Data sets is created using the New-PBIDataSet function from PowerBIPS powershell module.)

Is there a way to do this with a simple user/pass account? Or is there an API key method like in Amazon AWS?

I see threads like this, but they talk about app accesss, not dataset updating.

https://community.powerbi.com/t5/Developer/REST-API-Silent-Authentication-Token/m-p/156004#M5283

[Eric_Zhang]

---

@Anonymous wrote:

I have a working Powershell script that runs a SQL query and creates/updates results to a PowerBI dataset. The trouble is it uses my own SSO login to do it.

I want to run this as a task/service to update the dataset every X minutes. I'm looking to simplify authentication. Right now it seems I need an O365 account + PowerBI Pro user just to push data up with the API. Is that correct?

On Datasets made thru the Desktop I can change the username and password. But this is not true on datasets made through the API and set to "Push". (Data sets is created using the New-PBIDataSet function from PowerBIPS powershell module.)

Is there a way to do this with a simple user/pass account? Or is there an API key method like in Amazon AWS?

I see threads like this, but they talk about app accesss, not dataset updating.

https://community.powerbi.com/t5/Developer/REST-API-Silent-Authentication-Token/m-p/156004#M5283

---

@Anonymous

The dataset updateing is based on the accesstoken. To get the accesstoken in a silent way, I use the way in C# as below. You could follow the same in way in PowerShell.

static string getAccessTokenSilently()
        {

            HttpWebRequest request = System.Net.HttpWebRequest.CreateHttp("https://login.windows.net/common/oauth2/token");
            //POST web request to create a datasource.
            request.KeepAlive = true;
            request.Method = "POST";
            request.ContentLength = 0;
            request.ContentType = "application/x-www-form-urlencoded";

            //Add token to the request header
            request.Headers.Add("Authorization", String.Format("Bearer {0}", token));

            NameValueCollection parsedQueryString = HttpUtility.ParseQueryString(String.Empty);
            //To learn how to get a client app ID, see Register a client app (https://msdn.microsoft.com/en-US/library/dn877542.aspx#clientID)
            parsedQueryString.Add("client_id", clientID);
            parsedQueryString.Add("grant_type", "password");
            parsedQueryString.Add("resource", "https://analysis.windows.net/powerbi/api");
            parsedQueryString.Add("username", username); //your power bi account here
            parsedQueryString.Add("password", password);  //your power bi password here
            string postdata = parsedQueryString.ToString();


            //POST web request
            byte[] dataByteArray = System.Text.Encoding.ASCII.GetBytes(postdata); ;
            request.ContentLength = dataByteArray.Length;

            //Write JSON byte[] into a Stream
            using (Stream writer = request.GetRequestStream())
            {
                writer.Write(dataByteArray, 0, dataByteArray.Length);
                var response = (HttpWebResponse)request.GetResponse();
                var responseString = new StreamReader(response.GetResponseStream()).ReadToEnd();
                dynamic responseJson = JsonConvert.DeserializeObject<dynamic>(responseString);
                return responseJson["access_token"];
            }


        }

[Anonymous]

Yes there are examples of how to do it silently in powershell:

#region username and password
#$user = "username"
#$pass = "password" | ConvertTo-SecureString -AsPlainText -Force

#$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $pass
#$authToken = Get-PBIAuthToken -ClientId "ZZZZZZZZ-XXX-blah-blah-Muffins" -Credential $cred

The issue is what type of user account does it have to be to simply push data to the datasets? And is there a way of doing it without storing the password in the script?

[rossnruthie · Accepted solution]

Hi @Anonymous

I'm currently using PowerShell via SQL Agent to run a .ps1 file that calls the refresh API for some of my datasets.  In order to for the account to access powerBi it must exist in Azure Active Directory and have a PowerBI account (a pro account may be needed depending on what you're doing).  I can't speak for how your organization is structured but for me, I had our Infastructure guys create a service account in our local AD and give it an O365 account.  Once that is done, the account is synced to Azure AD.  I then had them give the account a PBI pro license as it's being used as a service account for embedding.

Then I created a powershell script and I'm using those credentials to create the access token.  As far as not hard coding the PW into the script, I actually created a text file with an encrypted string.  This file is pulled into the script and decrypted at run time.

Here is the code snippet that pulls in the file and decrypts it:

#Get the root folder of this file
$PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent

#Get the fully qualified filename
$pwdFile = "$PSScriptRoot\$pwdFileName"

#Create the key
$key = (1..16)

#Decrypt the pw
$password = Get-Content -LiteralPath $pwdFile | ConvertTo-SecureString -Key $key

Here is a link to my post with some code snippets...Note that in my post I'm still using the hard coded PW.  I changed it to a secure file after my post.

https://community.powerbi.com/t5/Developer/programmatic-data-refresh-using-api/m-p/223051#M7045

[Anonymous]

Ooh, this looks promising! I'll give this a lookover tomorrow. Thanks rossnruthie!