Forum Discussion
Authentication in Powershell and API to update a dataset.
Hi Anonymous
I'm currently using PowerShell via SQL Agent to run a .ps1 file that calls the refresh API for some of my datasets. In order to for the account to access powerBi it must exist in Azure Active Directory and have a PowerBI account (a pro account may be needed depending on what you're doing). I can't speak for how your organization is structured but for me, I had our Infastructure guys create a service account in our local AD and give it an O365 account. Once that is done, the account is synced to Azure AD. I then had them give the account a PBI pro license as it's being used as a service account for embedding.
Then I created a powershell script and I'm using those credentials to create the access token. As far as not hard coding the PW into the script, I actually created a text file with an encrypted string. This file is pulled into the script and decrypted at run time.
Here is the code snippet that pulls in the file and decrypts it:
#Get the root folder of this file $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent #Get the fully qualified filename $pwdFile = "$PSScriptRoot\$pwdFileName" #Create the key $key = (1..16) #Decrypt the pw $password = Get-Content -LiteralPath $pwdFile | ConvertTo-SecureString -Key $key
Here is a link to my post with some code snippets...Note that in my post I'm still using the hard coded PW. I changed it to a secure file after my post.
https://community.powerbi.com/t5/Developer/programmatic-data-refresh-using-api/m-p/223051#M7045
Yes there are examples of how to do it silently in powershell:
#region username and password #$user = "username" #$pass = "password" | ConvertTo-SecureString -AsPlainText -Force #$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $pass #$authToken = Get-PBIAuthToken -ClientId "ZZZZZZZZ-XXX-blah-blah-Muffins" -Credential $cred
The issue is what type of user account does it have to be to simply push data to the datasets? And is there a way of doing it without storing the password in the script?
Hi Anonymous
I'm currently using PowerShell via SQL Agent to run a .ps1 file that calls the refresh API for some of my datasets. In order to for the account to access powerBi it must exist in Azure Active Directory and have a PowerBI account (a pro account may be needed depending on what you're doing). I can't speak for how your organization is structured but for me, I had our Infastructure guys create a service account in our local AD and give it an O365 account. Once that is done, the account is synced to Azure AD. I then had them give the account a PBI pro license as it's being used as a service account for embedding.
Then I created a powershell script and I'm using those credentials to create the access token. As far as not hard coding the PW into the script, I actually created a text file with an encrypted string. This file is pulled into the script and decrypted at run time.
Here is the code snippet that pulls in the file and decrypts it:
#Get the root folder of this file $PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent #Get the fully qualified filename $pwdFile = "$PSScriptRoot\$pwdFileName" #Create the key $key = (1..16) #Decrypt the pw $password = Get-Content -LiteralPath $pwdFile | ConvertTo-SecureString -Key $key
Here is a link to my post with some code snippets...Note that in my post I'm still using the hard coded PW. I changed it to a secure file after my post.
https://community.powerbi.com/t5/Developer/programmatic-data-refresh-using-api/m-p/223051#M7045
Ooh, this looks promising! I'll give this a lookover tomorrow. Thanks rossnruthie!