How to bypass the Office 365 authentication in the Powershell Script for Refresh automation

[Anonymous]

Hi,

I am using the MSFT provided powershell script for refresh automation and the below script brings up the Office 365 login prompt which I am trying to avoid. I tried to tweak the code to skip the SSO authentication (while using my own credentials) but now I would like to skip the Office 365 authentication as I am using a service account that is created in the Office 365 AD dedicated to run these jobs. Below is the screenshot of the prompt and also the script that I am using.

Thanks in advance

# 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 = "" # the ID of the group that hosts the dataset. Use "me" if this is your My Workspace
$datasetID = "" # 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 = "" 

#Username and PW of the account that will be executing the refresh
$userName = "svc_pbiadmin@liveperson365.onmicrosoft.com"
$password = "*********"

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

# Calls the Active Directory Authentication Library (ADAL) to authenticate against AAD
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 = "urn:ietf:wg:oauth:2.0:oob"
 
       $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"
	   
	   $authority = "https://login.windows.net/common/oauth2/authorize";
 
       #$authority = "https://login.microsoftonline.com/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)

	   $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

[GilbertQ]

Hi there

I used the above script with a dedicated user account and it did not prompt me for the user login details

[Anonymous]

Hi,

Thanks for the quick response. I did try the script both ways 1) passing the username and password 2) Default script, but unfortunately it's not working for me. I am hoping for someone to point me in the right direction here.

[GilbertQ]

Hi @Anonymous

Could you possibly try this code and change all the sections where it says "FILL ME IN" with the required data

# 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 = "FILL ME IN" # the ID of the group that hosts the dataset. Use "me" if this is your My Workspace
$datasetID = "FILL ME IN" # 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 = "FILL ME IN" 

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

# Calls the Active Directory Authentication Library (ADAL) to authenticate against AAD
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 = "urn:ietf:wg:oauth:2.0:oob"
 
       $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"
 
       $authority = "https://login.microsoftonline.com/common/oauth2/authorize";
 
        #Authentication Added in here
        #=============================
        $userName = "FILL ME IN"

        $password = "FILL ME IN"

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

# 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"
#POST   https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/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  | ConvertTo-Json | Out-File "c:\Intel\somefile.json" #| Out-GridView -PassThru

[Anonymous]

Hi @GilbertQ,

Thanks again for your help. The script is running successfully for couple of times but after a while I am getting the below error saying Token has expired: 

VERBOSE: POST https://api.powerbi.com/v1.0/myorg/groups/25cc4b40-0104-4448-a9f5-035ed20b/datasets/391edc30-e95a-439f-8cd2-39b1eade/refreshes with 0-byte payload
Invoke-RestMethod : {"error":{"code":"TokenExpired","message":"Access token has expired, resubmit with a new 
access token"}}
At C:\Users\rreddy\Documents\Scripts\Refresh.ps1:89 char:1
+ Invoke-RestMethod -Uri $uri -Headers $authHeader -Method POST -Verbos ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMetho 
   d], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCo 
   mmand

Any thoughts on the below error which I got with the "Token expired" error:

Exception calling "AcquireToken" with "3" argument(s): "Federated service at https://autologon.microsoftazure
ad-sso.com/liveperson365.onmicrosoft.com/winauth/trust/2005/usernamemixed?client-request-id=b9ee141b-2b2-415
3-ba42-cb9d1ca22 returned error: Authentication Failure"
At C:\Users\rreddy\Documents\Scripts\PSfromCommunity.ps1:64 char:9
+         $authResult = $authContext.AcquireToken($resourceAppIdURI, $c ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : AdalServiceException
 
You cannot call a method on a null-valued expression.
At C:\Users\rreddy\Documents\Scripts\PSfromCommunity.ps1:73 char:1
+ $authHeader = @{
+ ~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

[GilbertQ]

Hi there,

I'm not sure off hand why that happened. Is your dataset on shared capacity or Premium?
The reason that I ask is because you can only refresh 8 times on shared capacity

[Anonymous]

Hi @GilbertQ,

The datasets are assigned to the premium capacity, I don't think that's the reason. The Token is expiring when I use the dedicated account that is created in the Office 365 directory for this process.

[GilbertQ]

Hi @Anonymous

Oh ok, it is possible that it would expire because you can refresh it as often as you like.

I am sure I have found a way to use an Azure Credential instead of a username and password for authentication. By using the credential it should not expire.

Let me know if this sounds like something you could use and I could find the script.

[Anonymous]

Hi @GilbertQ,

Sure, I can try that. Thanks Gilbert

[GilbertQ]

Hi there, give this a try

https://stackoverflow.com/questions/37249623/how-to-login-without-prompt

[Anonymous]

Hi @GilbertQ,

I did look at this link but not exactly sure where I should be using this code in my script as I am not a PS expert. Any further help would be appreciated.

Thanks

[GilbertQ]

Hi there, if you can try the original script and where it says FILL ME IN, if you can put in the relevant details.

That should ensure that it logs you in automatically and does not prompt you.

[manhoj]

Hi,

If you're using multi factor authentication, it still prompts me for the mobile verfication even after embedding my credentials in the script.

Id there any way to overcome that?

Thanks in advance,

Manhoj

[Anonymous]

Hi @GilbertQ,

If you can please look at my earlier posts the issue is no longer about the prompt, now the challenge is to overcome the "Token Expired" error that I have been getting if I run the same script after an hour. I have gone through some related documentation but not exactly sure how to use them in this script to fix my issue.

Links: 

https://docs.microsoft.com/en-us/azure/active-directory/active-directory-configurable-token-lifetime...

https://docs.microsoft.com/en-us/azure/active-directory/develop/active-directory-v2-tokens

https://technet.microsoft.com/en-us/library/gg188586(v=crm.6).aspx

[GilbertQ]

Hi @Anonymous

Apologies for that.

I have used this in the past to create a credential in which the token should not expire

https://stackoverflow.com/questions/37249623/how-to-login-without-prompt

[Anonymous]

Hi @GilbertQ,

Thanks for the response. I did play around with this before but no luck, can you please share the script with CREDENTIAL that you have used before.

Thanks in advance

[GilbertQ]

Hi there

I currently do not have the script because I followed the steps as per the link and once that was done I have then stored the credentials which are now used in my scripts.

I would suggest completing the detailed steps as per the link