Dataset refresh using Powershell without hardcoding password in the script

[Anonymous]

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

[V-lianl-msft]

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.h... 

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