topic programmatic data refresh using api in Developer

programmatic data refresh using api

Anonymous — Wed, 26 Jul 2017 16:00:32 GMT

I have found that with the new PBI API there is an option to trigger a dataset refresh programatically. I was looking at implementing this into a powershell script that could be called at the end of our nightly job loads. I found a open source powershell script that uses the API: https://github.com/Azure-Samples/powerbi-powershell

I filled in the script with the info needed and it works. However when i run the script I am forced to manually sign in via AAD to generate the required token used in the API call. The plan is to run this powershell script in a job automatically so I would need the login to be handled automatically.

How can I make the login automatic so that I do not need to manually log in each time i run this script?

Without knowing much about powershell scripting I'm assuming that the following code needs to be changed:

       $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

Rather than passing in "Auto", I would assume that there is some way to pass in user credentials instead.

Any help would be appreciated.

Thanks!

Re: programmatic data refresh using api

Eric_Zhang — Thu, 27 Jul 2017 08:53:04 GMT

@Anonymous wrote:

I have found that with the new PBI API there is an option to trigger a dataset refresh programatically. I was looking at implementing this into a powershell script that could be called at the end of our nightly job loads. I found a open source powershell script that uses the API: https://github.com/Azure-Samples/powerbi-powershell

I filled in the script with the info needed and it works. However when i run the script I am forced to manually sign in via AAD to generate the required token used in the API call. The plan is to run this powershell script in a job automatically so I would need the login to be handled automatically.

How can I make the login automatic so that I do not need to manually log in each time i run this script?

Without knowing much about powershell scripting I'm assuming that the following code needs to be changed:
       $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")
Rather than passing in "Auto", I would assume that there is some way to pass in user credentials instead.

Any help would be appreciated.

Thanks!

@Anonymous

Based on my test, I can get the access token in a silent way without any popup login window. Just ensure that the account used in this demo is granted permission in that AAD application.

 static string getAccessTokenSilently()
        {
            string resourceUri = "https://analysis.windows.net/powerbi/api";
            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"; 

            NameValueCollection parsedQueryString = HttpUtility.ParseQueryString(String.Empty);
            parsedQueryString.Add("client_id", clientID);
            parsedQueryString.Add("grant_type", "password");
            parsedQueryString.Add("resource", resourceUri);
            parsedQueryString.Add("username", username);
            parsedQueryString.Add("password", password);
            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"];
            }


        }

Re: programmatic data refresh using api

Anonymous — Tue, 01 Aug 2017 12:42:01 GMT

Thanks for the response @Eric_Zhang, it let me know that what I was trying to achieve could be done. I did some more digging and was able to modify the function in the PS script to use supplied credentials:

function GetAuthToken
{
    $adal = "${env:ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
 
    $adalforms = "${env:ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
 
    [System.Reflection.Assembly]::LoadFrom($adal) | Out-Null
 
    [System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null
 
    $redirectUri = "https://login.live.com/oauth20_desktop.srf"
 
    $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"
 
    $authority = "https://login.windows.net/common/oauth2/authorize";
       
    $userName = "MyUN@tenant.com"

    $password = "MyPW"

    $creds = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential" -ArgumentList $userName,$password
 
    $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

    $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $creds)
   

    return $authResult

}

Obviously I won't be hardcoding these credentials in the script but for testing purposes it works!

EDIT: it looks like my code block has been invaded with emoji's. If anyone is wondering you would replace the emoji with a colon ":" and the letter "P".

Re: programmatic data refresh using api

mcguija — Mon, 15 Jan 2018 15:37:07 GMT

Hi rossnruthie,

I think I am using the same basic code as you to do refresh. I too am being prompted to login to PowerBI and a windows security login.

Where in powershell code would I put the code you have below? Or you post your entire script?

Here is my working script:

###########################################################################################

# This sample script calls the Power BI API to progammtically trigger a refresh for the dataset
# It then calls the Power BI API to progammatically to get the refresh history for that dataset
# For full documentation on the REST APIs, see:
# https://msdn.microsoft.com/en-us/library/mt203551.aspx

# Instructions:
# 1. Install PowerShell (https://msdn.microsoft.com/en-us/powershell/scripting/setup/installing-windows-powershell) and the Azure PowerShell cmdlets (https://aka.ms/webpi-azps)
# 2. Set up a dataset for refresh in the Power BI service - make sure that the dataset can be
# updated successfully
# 3. Fill in the parameters below
# 4. Run the PowerShell script

# Parameters - fill these in before running the script!
# =====================================================

# An easy way to get group and dataset ID is to go to dataset settings and click on the dataset
# that you'd like to refresh. Once you do, the URL in the address bar will show the group ID and
# dataset ID, in the format:
# app.powerbi.com/groups/{groupID}/settings/datasets/{datasetID}

$groupID = "me" # the ID of the group that hosts the dataset. Use "me" if this is your My Workspace
$datasetID = "d234581a-734c-473c-be6c-e72f54ff9058" # the ID of the dataset that hosts the dataset

# AAD Client ID
# To get this, go to the following page and follow the steps to provision an app
# https://dev.powerbi.com/apps
# To get the sample to work, ensure that you have the following fields:
# App Type: Native app
# Redirect URL: urn:ietf:wg:oauth:2.0:oob
# Level of access: all dataset APIs
$clientId = "fbeb6bda-74a4-4a51-9c92-38a22c172a49"

# End Parameters =======================================

# Calls the Active Directory Authentication Library (ADAL) to authenticate against AAD
function GetAuthToken
{
$adal = "C:\Program Files\On-premises data gateway\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"

$adalforms = "C:\Program Files\On-premises data gateway\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"

[System.Reflection.Assembly]::LoadFrom($adal) | Out-Null

[System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null

$redirectUri = "urn:ietf:wg:oauth:2.0:oob"

$resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

$authority = "https://login.microsoftonline.com/common/oauth2/authorize";

$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

return $authResult
}

# Get the auth token from AAD
$token = GetAuthToken

# Building Rest API header with authorization token
$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$token.CreateAuthorizationHeader()
}

# properly format groups path
$groupsPath = ""
if ($groupID -eq "me") {
$groupsPath = "myorg"
} else {
$groupsPath = "myorg/groups/$groupID"
}

# Refresh the dataset
$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes"
Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST –Verbose

# Check the refresh history
$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes"
Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET –Verbose

###########################################################################################

Here are my two user prompts:

Re: programmatic data refresh using api

Anonymous — Wed, 17 Jan 2018 17:20:01 GMT

Hi @mcguija

It looks like you are just using the redirect for the AcquireToken call. This is what will pop up your login screen. What you need to do is modify your AcquireToken to pass in a predefined user credential. This will make the authentication silent and prevent the login from popping up.

Here is an example:

$groupID = "12345" 
$datasetID = "54321" 
$clientId = "67890" 

#Username and PW of the account that will be executing the refresh
$userName = "username@yourdomain.com"
$password = "userPasswordValue"
# End Parameters =======================================

function GetAuthToken
{
 $adal = "${env:ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
 
 $adalforms = "${env:ProgramFiles(x86)}\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
 
 [System.Reflection.Assembly]::LoadFrom($adal) | Out-Null
 
 [System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null
 
 #Don't need this
 #$redirectUri = "https://login.live.com/oauth20_desktop.srf"#"urn:ietf:wg:oauth:2.0:oob"
 
 $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"
 
 $authority = "https://login.windows.net/common/oauth2/authorize";

 $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

 $creds = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential" -ArgumentList $userName,$password 

 $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $creds)

 return $authResult

}

# Get the auth token from AAD
$token = GetAuthToken
 
$token

Here I am creating an active directory user credential and assigning the UN/PW

$creds = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential" -ArgumentList $userName,$password

Here I am taking the credentials I created in the previous step and passing it into the AcquireToken call. This is different from what you are doing.

#My AcquireToken Call passing in the user credential i created:
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $creds)

#Your AcquireToken Call using the redirect and forcing a sign in pop-up:
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

Hope this helps.

Re: programmatic data refresh using api

Anonymous — Fri, 09 Mar 2018 17:48:55 GMT

This really helped me to skip SSO authentication step.

Re: programmatic data refresh using api

HTAN — Fri, 13 Apr 2018 23:28:32 GMT

I have this error message. And I have download and replace with older version of dll files but I still get same error. Can anyone help? Thanks in advance.

Method invocation failed because [Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext] does not contain a method named 'AcquireToken'.

Re: programmatic data refresh using api

duncfair — Fri, 20 Apr 2018 15:59:45 GMT

same

Re: programmatic data refresh using api

evtrifonov — Wed, 27 Feb 2019 14:10:53 GMT

Thank you for your advices, but I getting this error too:

New-Object : Cannot find an overload for “PSCredential” and the argument count: “2”

Re: programmatic data refresh using api

Anonymous — Thu, 05 Dec 2019 13:34:08 GMT

Use the following scripts which work perfect,

# Parameters - fill these in before running the script!

# =====================================================

# An easy way to get group and dataset ID is to go to dataset settings and click on the dataset

# that you'd like to refresh. Once you do, the URL in the address bar will show the group ID and

# dataset ID, in the format:

# app.powerbi.com/groups/{groupID}/settings/datasets/{datasetID}

$groupID = "me" # the ID of the group that hosts the dataset. Use "me" if this is your My Workspace

$datasetID = "d9b4fd0c-7ac9-4e5d-be42-76686ce6b2db" # the ID of the dataset that hosts the dataset

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# To get the sample to work, ensure that you have the following fields:

# App Type: Native app

# Redirect URL: urn:ietf:wg:oauth:2.0:oob

# Level of access: all dataset APIs

$clientId = "Client ID"

# End Parameters =======================================

# Calls the Active Directory Authentication Library (ADAL) to authenticate against AAD

# update the dll files path if the versions are different like 5.1.2

function GetAuthToken

{

$adal = "C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.1\Automation\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"

$adalforms = "C:\Program Files\WindowsPowerShell\Modules\Azure\5.1.1\Networking\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"

[System.Reflection.Assembly]::LoadFrom($adal) | Out-Null

[System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null

$redirectUri = "urn:ietf:wg:oauth:2.0:oob"

$resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

$authority = "https://login.microsoftonline.com/common/oauth2/authorize";

$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

$UserCred = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList “UserName@domain.com“, “myPassword”

$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $clientId, $UserCred).Result

return $authResult

}

# Get the auth token from AAD

$token = GetAuthToken

# Building Rest API header with authorization token

$authHeader = @{

'Content-Type'='application/json'

'Authorization'=$token.CreateAuthorizationHeader()

}

# properly format groups path

$groupsPath = ""

if ($groupID -eq "me") {

$groupsPath = "myorg"

} else {

$groupsPath = "myorg/groups/$groupID"

}

# Refresh the dataset

$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes"

Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST –Verbose

# Check the refresh history

$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes"

Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET –Verbose

Re: programmatic data refresh using api

prisam505 — Tue, 04 Feb 2020 15:38:37 GMT

Hi,

I have written the follwing code:

function GetAuthToken
{
$adal = "C:\Program Files (x86)\Microsoft SDKs\Azure\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
$adalforms = "C:\Program Files (x86)\Microsoft SDKs\Azure\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
[System.Reflection.Assembly]::LoadFrom($adal) | Out-Null

[System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null

$redirectUri = "urn:ietf:wg:oauth:2.0:oob"

$resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

$authority = "https://login.microsoftonline.com/common/oauth2/authorize";

$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

$UserCred = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList “username@domain.com“, “pwd”

$authResult = $authContext.AcquireTokenAsync($resourceAppIdURI, $clientId, $UserCred).Result

return $authResult
}

I get the following error:

New-Object : Cannot find an overload for "UserCredential" and the argument count: "2".

If i replace it with UserPasswordCredential then it throws an error as:

Cannot find an overload for "AcquireTokenAsync" and the argument count: "3".

I have installed the latest version (5.2.6) of Microsoft.IdentityModel.Clients.ActiveDirectory.dll and followed steps to register for ClientID in https://dev.powerbi.com/apps

Any suggestions how to correct it ?

Re: programmatic data refresh using api

jbruns2020 — Wed, 08 Jul 2020 16:51:42 GMT

Is 'myorg' truly supposed to be part of the URL or is it standing for ones own comany id of some sort?