Use PowerShell to automate Power Bi dataflow refresh without login POPUP.

[SDM_1997]

Hi All,
I have this requirement where I need to automate my dataflow refreshes. Now, using a simple scheduled refresh does not solve the issue because, if one dataflow fails, then the remaining dataflows should not refresh.

For context, 1.) Power Automate Dataflow connectors are blocked by the client's IT team.

                    2.) App registrations in Azure AD are also blocked, so we cannot obtain Client ID and Client secret to use programmatically in C# or any other language.

Now, I found that using PowerShell, it is possible to invoke Power BI REST API without any app registration.
I was following this link for reference. ( powerbi - Login to power bi service online (silently i.e. without the popup) using powershell - Stac... )

Below is the actual test code that I wrote for this.

Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile

$password = "MicrosoftAccountPassword" | ConvertTo-SecureString -AsPlainText -Force
$username = "MyUserName@OrgName.onmicrosoft.com" 
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

#Connect-PowerBIServiceAccount  (LINE 6)

Connect-PowerBIServiceAccount -Credential $credential (LINE 7)

$body = '{
  "notifyOption": "MailOnFailure"
}'
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/42dcdcb4-abc8-4116-bf3f-5e4dd80e2f8d/dataflows/fedc505d-edfc-474c-af53-e5ba735f4dd0/refreshes" -Method POST -Body $body

while($true)
{
    $data = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/42dcdcb4-abc8-4116-bf3f-5e4dd80e2f8d/dataflows/fedc505d-edfc-474c-af53-e5ba735f4dd0/transactions" -Method GET

    $newdata = $data | ConvertFrom-Json

    $flowStatus = $newdata.value.status[0]

    if($flowStatus -eq "Success")
    {
        Write-Host "Completed"
        break;
    }
    else
    {
        Write-Host "Running..."
        continue;
    }
}

Disconnect-PowerBIServiceAccount

 Now, this works perfectly fine, if I am using the 6th line of code. Here, I am getting a popup, where I manually login into my account and the rest of the API invoke works perfectly fine. But to automate this, I wanted to achieve the same thing without the PopUp.
For that, I tried using the 7th line of code. But it is unable to login into the account and I am getting an error.
Below is the error screenshot.

Is there something am I missing or is it really not possible to achieve this without the popup?
As I mentioned earlier, we are not allowed to register an app in Azure AD, so I cannot use the service principal method to log in via PowerShell. So, the only thing that I have is my Microsoft Account Username and Password.
Please let me know any workarounds, if possible.

Thanks in Advance!!

[vasudevp]

The userID credentials you are using may have 2 Factor authentication on, thats why its forcing Pop Up.

If you dont want that pop up the USER credentials you are going to use should be like system account without 2 factor authentication and only username and password.

[Anonymous]

HI @SDM_1997,

 As the document mentions, the '-Credential' parameter works for 'Service Principal', you can't directly use it without adding the '-ServicePrincipal' parameter:

Connect-PowerBIServiceAccount (MicrosoftPowerBIMgmt.Profile) | Microsoft Docs

-Credential

PSCredential representing the Azure Active Directory (AAD) application client ID (username) and application secret key (password) to authenticate with a service principal account (-ServicePrincipal).

Perhaps you can try to add '-CertificateThumbprint' to use the local certificate to verify the connection account steps.

-CertificateThumbprint

Certificate thumbprint of an installed certificate associated to an Azure Active Directory (AAD) application. Certificate must be installed in either the CurrentUser or LocalMachine personal certificate stores (LocalMachine requires an administrator prompt to access) with a private key installed.

Regards,

Xiaoxin Sheng

[SDM_1997]

Hi!
Thanks for the reply.
Yeah, I read on the documentation that we need to input application ID and secret as credentials for using Power Bi APIs. Since app registration in AzureAD is blocked for us, so we cannot obtain the app ID and secret.

If I understand correctly, getting a certificate thumbprint also requires app registration in AzureAD. Correct me, if I am wrong.
In that case, we are stuck as well.

Let's see, if I can ask my client's IT team if they can allow us temporarily to register an app once at least.

Thanks & Regards

sdm