Authentication in Powershell and API to update a dataset.

---

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"];
            }


        }