Azure Powershell automatic log-in to refresh Power BI

[pedromasou]

I'm currently developing a script to automatically refresh one of my Power BI datasets using Power BI's REST API. This script is a .ps1 file, called from my Python code as you can see below. I followed step-by-step from this tutorial, while my **refresh.ps1** file was developed from this official MS source. There were just some minor adaptations to make it run, so if anyone wants to test it, I suggest using the code I'm posting at the end of this question.

However, besides both codes work well and I they allow me to refresh my dataset, **every time they run I have to manually sign in in my Azure account through a prompted GUI**. This makes this script useless to automate tasks, such as in my case. See the picture of the GUI below:

I did some resource, but couldn't find any similar case, in which Python was also being used. About Powershell, I read about using ADAL, but couldn't really get what ADAL is.

So far, my Python script is:

import subprocess, sys

def powershell(file):

    p = subprocess.Popen(["powershell.exe", file], stdout=sys.stdout)
    p.communicate()

MS.powershell(r"'C:\blablabla\refresh.ps1'")

While my `refresh.ps1` file has the content shown below:

$groupID = "MY_GROUP_ID"
$datasetID = "MY_DATASET_ID"
$clientId = "MY_CLIENT_ID"

function GetAuthToken

{
if(-not (Get-Module AzureRm.Profile)) {
Import-Module AzureRm.Profile
}

$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
}

$token = GetAuthToken

$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$token.CreateAuthorizationHeader()
}

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

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

[v-jiascu-msft · Accepted solution]

Hi @pedromasou,

I would suggest you use pure Python and ADAL instead. Please refer to azure-activedirectory-library-for-python and active-directory/develop/v1-oauth2-implicit-grant-flow. 

If your Azure account applies compulsory two-factor authentication, I'm afraid user action is necessary. If not, the implicit grant flow could be the solution. You can get the access token with a username and a password. 

Please also refer to this Python script.

# adal is the azure-activedirectory-library-for-python. install it first.
from adal import AuthenticationContext
import requests
import json


# get access token implictly with username and password.
def get_token():
    client_id = "a30***1ae1dcde27e"
    resource_uri = "https://analysis.windows.net/powerbi/api"
    user_name = "d**.com"
    user_password = "password"

    auth_context = AuthenticationContext("https://login.microsoftonline.com/your_tenant_name")
    token_response = auth_context.acquire_token_with_username_password(resource_uri, user_name, user_password, client_id)
    token = "Bearer " + token_response["accessToken"]
    return token


def refresh():
    token = get_token()
    headers = {"Authorization": token, "Content-type": "application/json"}
    post_url = "https://api.powerbi.com/v1.0/myorg/groups/d002f024-*******bc75fb104/datasets/bbd609a0-*******c-c949d8a8ff9d/refreshes"
    body = {
      "notifyOption": "MailOnCompletion"
    }

    r = requests.post(post_url, headers = headers, data = json.dumps(body))
    return r.status_code

	
if __name__ == "__main__":
	refresh()

Best Regards,
Dale