Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rossnruthie
Resolver I
Resolver I

programmatic data refresh using api

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!

1 ACCEPTED SOLUTION

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".

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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

 

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 ?

evtrifonov
New Member

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

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

Eric_Zhang
Employee
Employee


@rossnruthie 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!


@rossnruthie

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


        }

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".

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:

Prompt1.pngPrompt2.png

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

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.

 

Anonymous
Not applicable

This really helped me to skip SSO authentication step.

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'.

same

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.