Login-PowerBI -Credential $cred does not work in Azure Automation

[furmangg]

This code doesn't work in Azure Automation: Login-PowerBI -Credential $mycreds

The reason it doesn't work is that it's calling WindowsAuthenticationFactory.InitializeCache which tries to start a process. https://github.com/microsoft/powerbi-powershell/blob/master/src/Common/Common.Authentication/WindowsAuthenticationFactory.cs

That may make sense for interactive login popups, but for passing -Credential it shouldn't be interactive. (I don't think -Credential currently supports multi-factor auth logins.)

To prove launching a process is the culprit, the following code in a Azure Automation Runbook (PowerShell) will cause it to fail and be suspended after 3 retries just like the Login-PowerBI -Credential $cred code does:

$ps = new-object System.Diagnostics.Process
$ps.StartInfo.Filename = "ipconfig.exe"
$ps.StartInfo.Arguments = " /all"
$ps.StartInfo.RedirectStandardOutput = $True
$ps.StartInfo.UseShellExecute = $false
$ps.start() | Out-Null
$ps.WaitForExit()
[string] $Out = $ps.StandardOutput.ReadToEnd();
$Out

To also prove you can't run an executable inside Azure Automation, see this link:

https://feedback.azure.com/forums/246290-automation/suggestions/31957750-can-we-execute-exe-or-bat-files-inside-runbook

The reason I'm trying to do credential auth is because service principal auth doesn't currently have access to classic workspaces I believe.

Please enhance the Login-PowerBI cmdlet to avoid launching a process when you use the -Credential parameter only.

[furmangg]

Maybe instead of executing the AzureADWindowsAuthenticator.exe you can simply run a few lines of PowerShell when the -Credential is specified (meaning non-interactive login):

$body=@{
    "grant_type"="password";
    "resource"="$resourceAppIdURI";
    "client_id"=$environment.AzureADClientId;
    "username"=$username;
    "password" = $password;
}

$authority = "https://login.windows.net/$tenant/oauth2/token";

$result=Invoke-RestMethod -Uri $authority -Method POST -Body $body 
$result.access_token

(Edit: As a side note, this code snippet only seems to work with a cloud-only user, not with an on-prem user which is synced to AAD or federated authentication through ADFS or other tools. And to be clear, this was just a suggestion to the authors of this project on how they might accomplish sign in without shelling out to a command line... I haven't attempted to integrate this code snippet into Power BI Powershell cmdlets myself. I've also seen strange issues where PowerShell sticks a few strange characters before the password, so if you get auth failures with this code snippet, monitor what it sends with Fiddler and see whether the password it sent is what you intended.)

[RenjuRNair]

@furmangg Are you able to do Login-PowerBI -Credential $cred from runbook? Can you please explain more on the solution you provided above?

[furmangg]

No. Login-PowerBI -Credential $cred fails from a runbook.

The “solution” was a suggestion for the authors of these Power BI cmdlets to make it compatible with runbooks not a workaround for people using them.

[danieljohansso]

I have also been trying this and have not been able to solve it or find a workaround. Is there any update regarding this.

I'm running an Azure Runbook with the following code:

    $Credentials= Get-AutomationPSCredential -Name 'Credentials'

    Connect-PowerBIServiceAccount -Credential $Credentials

Is there any known workarounds? This feature is critical! Running Powershell local is not a solution..

[melmsater]

Any updates on this issue? Running into the same problem in Azure Runbook.

[bee911]

any update as still hangs in azure runbook at Connect-PowerBIServiceAccount -Credential so i am unable to automate pbi!

[alliedcvil]

Any update on this issue? We can't do much of anything as far as automation without this functionality since we utilize Azure Automation.

[bee911]

Any update on this issue? We can't do much of anything as far as automation without this functionality since we utilize Azure Automation.

since msft silent as usual, i opened support ticket with azure few weeks which of course unresolved. they escalated to engineering and came back with below response: lol

"We suggest you use a hybrid runbook worker to execute your script, because the cmdlets with troubles, are requiring elevated access and aren't supported to run in cloud job to provide necessary security & isolation to the SQLPAL instance allocated for a job to run. "

this is crazy as i am not standing up an azure vm or on-prem vm to run to run automation hybrid. i agree would work perfectly since running win os but that is way to costly

even though case still open, i am not getting getting anywhere and needed solution so went in totally different direction...what i did is created an azure function (consumption based) with version of 2, set execution to be powershell, set function to import powerbi modules and then created timer function. i ported code to azure function and can attest it works perfectly. so i'd recommend doing that as clearly will not work in automation.

[yuanman2016]

I searched a long time of how to bypass interactive login to PowerBI with a user but no lucky. service principle is an option but need to enable in Power BI tenant level which doesn't work for me as my company doesn't enable it. Even token works, but to get an AAD token also involve interactive login with PowerBI service. The way I am having now is running a front PowerShell script with while(1) to keep processing PowerBI tasks such as refresh dataset within interval you set

[furmangg]

Is there a cmdlet which takes a token?

[yuanman2016]

There are some powerbi modules have command to get PowerBI auth token, such as POWERBIPS which has a command call Get-PBIAuthToken. Or you can use Connect-PowerBIServiceAccount which belongs to the module: MicrosoftPowerBIMgmt.Profile. After you run the connect command, you actually don't need a token then can do some PowerBI tasks such as get workspace, dataset etc.

[furmangg]

@yuanman2016 I was just responding to your comment “Even token works, but to get an AAD token also involve interactive login with PowerBI service.” and wondering if we were able to get a login token in a non-interactive manner if that would help. I wasn’t aware of a cmdlet to login by passing in a token. And I think you have confirmed one doesn’t exist. Thanks.

[fredrikheden]

I'm facing this issue too, even when trying connect using ServicePrincipal: Connect-PowerBIServiceAccount -ServicePrincipal -ApplicationId $connection.ApplicationID -CertificateThumbprint $connection.CertificateThumbprint Any plans fixing this?

[furmangg]

Don’t you have to include the -Tenant switch when you use a service principal? https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/Connect-PowerBIServiceAccount?view=powerbi-ps

Off the top of my head you could probably either put your login email suffix (like joe@parts.com would put parts.com) or you could lookup your tenant ID here: https://www.whatismytenantid.com/

[fredrikheden]

@furmangg You are absolutely right. When the tenant is added it works with a service principal inside a runbook. Thanks!

[kartikjindgar]

@fredrikheden Can you please share the piece of code you used to login using service principal. I have been trying to login using Client credentials and Tenant ID but have not been able to make it work

@furmangg Any help would be really appreciated!

[furmangg]

@kartikjindgar i assume that you gave the service principal access to the workspace and you also changed the Power BI admin setting allowing this (or allowing all) service principals to connect?

If you are using a secret to authenticate then:

Connect-PowerBIServiceAccount -Credential MyPSCredential -ServicePrincipal -Tenant yourTenantID

Here is a more complete example: https://github.com/microsoft/PowerBI-Tools-For-Capacities/issues/14#issuecomment-652384543

If you are using a certificate then post back.

[kartikjindgar]

@furmangg thanks for such a prompt response. I was running the following command -

Connect-PowerBIServiceAccount -Credential MyPSCredential -ServicePrincipal -Tenant yourTenantID

It runs fine on my local machine but when I try to run it through a gitlab cicd pipeline it fails. I am getting the following error-

Connect-PowerBIServiceAccount : Failed to populate environments in settings + Connect-PowerBIServiceAccount -ServicePrincipal -Credential $creds -T ... + CategoryInfo : WriteError: (Microsoft.Power...IServiceAccount:ConnectPowerBIServiceAccount)[Connect-PowerBIServiceAccount], Exception + FullyQualifiedErrorId : Failed to populate environments in settings, Microsoft.PowerBI.Commands.Profile.ConnectPowerBIServiceAccount

Any clues how to solve this?

[fredrikheden]

@kartikjindgar I'm using the following two lines to authenticate from a runbook:

$connection = Get-AutomationConnection -Name AzureRunAsConnection
Connect-PowerBIServiceAccount -Tenant "4********************5" -ServicePrincipal -ApplicationId $connection.ApplicationID -CertificateThumbprint $connection.CertificateThumbprint

[kartikjindgar]

@fredrikheden I was simply using

Connect-PowerBIServiceAccount -ServicePrincipal -Credentials PSCredentials -Tenant $tenantID

But this seems to not be working from a GitLab pipeline. I am getting the error mentioned here- https://github.com/microsoft/powerbi-powershell/issues/190#issuecomment-702112170

Can you let me know where I am going wrong?

[Mimiix]

Is there any progress on this issue? I can't use a serviceprincipal as the API that i use (https://docs.microsoft.com/en-us/rest/api/power-bi/imports/postimportingroup) doesn't support it.

[khouloudbelhaj]

Any update on this issue?

[yuanman2016]

Any update on this issue?

Just received this update, want to share my findings:

I recently just use below way can by passing MFA:

$Cred = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $user, $pw Connect-PowerBIServiceAccount -Environment Public -Credential $Cred

I am using a service account which is an email enabled account in AAD. After connecting successfully, I can do all APIs work which PowerBI provided

[furmangg]

@yuanman2016 where are you running that from? From Azure Automation Runbooks?

[yuanman2016]

Powershell and azure devops

Greg Galloway @.***> 于 2021年5月17日周一 下午6:48写道：

@yuanman2016 https://github.com/yuanman2016 where are you running that from? From Azure Automation Runbooks?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/microsoft/powerbi-powershell/issues/190#issuecomment-842760980, or unsubscribe https://github.com/notifications/unsubscribe-auth/AEUTEBG6FJSEPGVUC34E3VDTOHBNPANCNFSM4I5LS7SQ .

[khouloudbelhaj]

@yuanman2016 Can I have more details please about the method you are referring to?I tried to execute the code you posted in a runbook but it still doesn't work for me.

[khouloudbelhaj]

@fredrikheden : I tried to work with the code you proposed to resolve the problem but It's not possible for my case to execute this part of the code : $connection = Get-AutomationConnection -Name AzureRunAsConnection , Is there any alternative?

[yuanman2016]

@khouloudbelhaj , I am using a service account, not a real user account which maybe a bit different. And I am running in local powershell, and Azure DevOps Azure Cli activity. I haven't tried Runbook yet.

[khouloudbelhaj]

@yuanman2016 : Do you have more details please about Azure Develops Azure Cli activity? I have tried to work with runbook but it failed to connect to power BI. I switched to Azure functions and the AAD approach . The connexion work locally via service principal but failed when it comes to function.

[CattieCat]

See if it works for you now since we released a new version 1.2.0 with MSAL.

[khouloudbelhaj]

@CattieCat Yes it worked for me , thank you for the job you have done! This update will solve many problems.

[yuanman2016]

@naraghavan , here is my code worked in 2021 when I posted, I didn't use it for a long time so not sure if it is still working, please test yourself:

param ( [parameter(Mandatory = $false)] [String] $pbixPath, [parameter(Mandatory = $false)] [String] $groupId, [parameter(Mandatory = $false)] [String] $datasetName )

install module

Install-Module MicrosoftPowerBIMgmt.Profile -force -Scope CurrentUser

Import-Module MicrosoftPowerBIMgmt.Profile

Install-Module PowerBIPS -force -Scope CurrentUser

Import-Module PowerBIPS

$user = "user" $pw = pwd $Cred = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $user, $pw Connect-PowerBIServiceAccount -Environment Public -Credential $Cred

$token = Get-PowerBIAccessToken

$strToken = ($token.Values -join " ").Replace("Bearer ","").trim()

Get-PBIWorkspace -authToken $strToken

$dataset = Import-PBIFile -authToken $strToken -file $pbixPath.Replace("/","\") -dataSetName $datasetName -groupId $groupId -nameConflict CreateOrOverwrite

$dataset

refresh data source credential

$dataset = Get-PBIDataSet -authToken $strToken -name $datasetName -groupId $groupId

Get-PBIDatasources -dataset $dataset.id -groupId $groupId -authToken $strToken