Need assistance with automating MS Authentication in a PowerShell script

Question

I am working on a Powershell script that reads from the Power BI Activity log to retrieve audit information on Power BI usage. I plan on running this script in Windows Task Scheduler daily to output a JSON file from which I will load the data into an SQL Server database.

Firstly, in order to run the script, it must be logged on to Power BI and the account must have Power BI Administrator permissions. This is the command to log on to Power BI:

Connect-PowerBIServiceAccount

When I run it manually in Power BI, an MS authentication window comes up and I must sign on. How can this be done automatically? Is there any way to use a stored credential?

Here is the rest of the script:

$startDateToExtract = '2023-03-09T00:00:00.000'
$endDateToExtract = '2023-03-09T23:59:59.999'

#Optional parameters -ActivityType, -User, -ResultType

###########################
$activities = Get-PowerBIActivityEvent -StartDateTime '2023-03-15T00:00:00' -EndDateTime '2023-03-15T23:59:59' | Set-Content C:\Temp\Bucket\PBI_Track7.json

$activities.Count
for (($i = 0); ($i -le $activities.Count); $i++)
{
$activities[$i] | >> C:\Temp\Bucket\PBI_Track7.json

}

$activities.Count

#################################

I have manually entered the start dates. Is there a way to get the date and put it in the start and end dates in the format above?

StartDate: 'YYYY-MM-DDT00:00:00.000'

EndDate: 'YYYY-MM-DDT23:59:59.999'

I have the output file manually set as PBI_Track7.json. How can I have the file generated with the date the script is run? Example, 20160316.json

Any help and advice would be greatly appreciated.

alex_rechs · Answer

$User = "email address removed for privacy reasons"
$PWord = ConvertTo-SecureString -String "password_blablah" -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $PWord
Connect-PowerBIServiceAccount -Credential $Credential

andysvints · Answer

Hello&nbsp;Lorenz33,In addition to suggestions mentioned by Alex_Rechs, you can also look into 2 extra options:(1) using ServicePrinciple with ClientSecretPrerequisites: Register App in AAD and create ClientSecret.Code to authenticate will be something like this:$TenantId="[TenantId]"
$ClientId="[YourRegisteredAADAppClientId]"
$ClientSecret="[YourRegisteredAADAppClientSecret]"

$PWord = ConvertTo-SecureString -String $ClientSecret  -AsPlainText -Force

$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $ClientId, $PWord

Connect-PowerBIServiceAccount -Tenant $TenantId -ServicePrincipal -Credential $Credential&nbsp;(2) using ServicePrinciple with CertificatePrerequisites: Register App in AAD and upload certificate (self signed will suffice).Authentication code will looks like this:$AppId= "[YourRegisteredAADAppClientId]"
$Cert="[Thumbprint]"

Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint $Cert -ApplicationId $AppIdReferences:Connect-PowerBIServiceAccountCreate an Azure Active Directory application and service principal that can access resourcesHope that helps.&nbsp;&nbsp;&nbsp;

lorenz33 · Answer

Thanks Alex and Andy. That definitely helps. I have tested it and it works. One more quick question: it is not a good practice to keep the password in the file like that where it can be exposed to whoever is on the server. Is there any way it can be encrypted? Possibly stored in secure encrypted format in another file which can be retrieved and then unencrypted?

andysvints · Answer

Hello&nbsp;Lorenz33,You can look into Microsoft.PowerShell.SecretManagement&nbsp;( provides a convenient way for a user to store and retrieve secrets), which supports multiple secret vault types. For starters, you can use Microsoft.PowerShell.SecretStore ( Local secure store extension vault).&nbsp;Hope that helps.

Forum Discussion

Need assistance with automating MS Authentication in a PowerShell script

4 Replies

Resources