Dataset refresh using Powershell without hardcoding password in the script

Anonymous — Tue, 22 Mar 2022 12:59:45 GMT

I am trying to refresh a PowerBI dataset using a powershell script. But everytime I execute the script, it is asking for the credentials to sign in.

My requirement is to refresh the dataset using the powershell script which is triggered from the SQL Server Agent Job. I have created a proxy user account in SQL Server to execute the powershell script and I want the same credentials to be cascaded to the powershell script. I am not allowed to hardcode the password in the powershell script or read it from an encrypted file.

When I am manually executing the script and enter the credentials in the pop-up window, the dataset is refreshed properly. But since i need to execute it from the SQL Server Agent Job, I need to avoid getting the pop-up asking for signing in.
I am using the below Powershell script.

$groupID = "XXXXXXXXXXXXXXXXXXXX" $datasetID = "XXXXXXXXXXXXXXXXXXXX" $clientId = "XXXXXXXXXXXXXXXXXXXX" 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 } # 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

Re: Dataset refresh using Powershell without hardcoding password in the script

V-lianl-msft — Fri, 25 Mar 2022 02:38:25 GMT

Hi @Anonymous ,

You can try to use a service principal to refresh the dataset with rest api.

https://www.nickyvv.com/2020/06/refresh-a-power-bi-dataset-with-powershell-and-a-service-principal.html

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: Dataset refresh using Powershell without hardcoding password in the script in Developer

Dataset refresh using Powershell without hardcoding password in the script

Re: Dataset refresh using Powershell without hardcoding password in the script