They say that Data is the new oil. What they mean is that nowadays, data is currency. Hence, going by the human tendency, there will attempt to steal data (popularly called hacking). Hence, data security is a major concern for organizations across the spectrum. One of the security loopholes any organization can have is stale credentials. Let me explain what do I mean by that with an example of a Power BI data source.
Power BI can connect to a variety of data sources. One of them is Azure SQL Database. As a best practice, organizations use SQL authentication with service users. However, over time, if the usernames and passwords are stagnant, there is a possibility of accidental or deliberate leakages. Thus, it is good practice to have the passwords updated at regular intervals.
Nonetheless, from a DevOps standpoint, it would be desirable to have this process automated. And, as far as automation is concerned, what else is better than shell scripting and in the case of Microsoft stack, it is Powershell.
Also read: Conditional formatting in PowerBI
Pre-requisites
- A windows system with Powershell installed.
- Powershell module ‘Az’
- Powershell module ‘MicrosoftPowerBIMgmt’
For more on the module MicrosoftPowerBIMgmt, refer to this link.
Steps to update Power BI Data Source Credentials
- Extract Authorization Token
- Get workspace details.
- Get Dataset Object
- Find Data Source Object
- Update credentials
Step 1: Extract Authorization Token.
Before doing anything else, we need to extract authorization to Power BI. The following code will help you achieve the same:
$PbiUserName = <MyUserName> $PbiPassword = <MyPassword> $PbiSecurePassword = ConvertTo-SecureString $PbiPassword-Force -AsPlainText $PbiCredential = New-Object Management.Automation.PSCredential($PbiUserName , $PbiSecurePassword) $PBIcredential = New-Object System.Management.Automation.PSCredential($PbiUserName , $PbiSecurePassword) Connect-PowerBIServiceAccount -Credential $PBIcredential $authToken= Get-PowerBIAccessToken -AsString
Step 2: Get workspace details.
Before one gets to update the data source credentials, it is imperative to connect to the workspace. The following cmdlet will fetch the PowerBI workspace details for you.
$WorkspaceObject = (Get-PowerBIWorkspace -Scope Individual -Name '<My Workspace>')
Step 3: Get Dataset details.
Once you have the Workspace object details, the following cmdlet will fetch the requisite PowerBI dataset details for you.
$PbiDatasetObject = (Get-PowerBIDataset -Name '<My Dataset>' -WorkspaceId $WorkspaceObject.Id)
Step 4: Get Data Source details.
Next, we need to find the underlying data source details for the requisite details. The following cmdlet will fetch the requisite Datasource details for you.
$PbiDataSourcesObject = (Get-PowerBIDatasource -DatasetId $PbiDatasetObject.Id -Scope Individual)
Step 5: Update Power BI Data Source Credentials
Finally comes the most important step, i.e. updating credentials. Here we use the Power BI REST API with the PATCH method:
#Creating Auth headers
$headers=@{'authorization'=$authToken}
#Getting Datasource and Gateway ID
$DataSourceId = $PbiDataSourcesObject.DatasourceId
$GatewayId=$PbiDataSourcesObject.GatewayId
$DSAuthenticationMethod = "Basic"
#Patch API
$patch_api_credentials = "https://api.powerbi.com/v1.0/myorg/gateways/{gateway_id}/datasources/{datasource_id}";
$patch_api_credentials_updated = patch_api_credentials.replace("{gateway_id}",$GatewayId).replace("{datasource_id}",$DataSourceId);
#User Name and Password
$DSUserName = "<SQL User Name>"
$DSPassword = "<New SQL Password>"
$credProperties = @{
username= $DSUserName ;
password = $DSPassword ;
}
$cred = New-Object PSObject -Property $credProperties
$request = @{
credentialType = $DSAuthenticationMethod ;
basicCredentials = $credObject
}
$requestBody = new-object psobject -Property $request
$requestBodyJson = $requestBody | ConvertTo-Json;
Invoke-WebRequest -UserAgent "" -DisableKeepAlive -Uri $patch_api_credentials_updated -Method Patch -ContentType "application/json" -Body $requestBodyJson -Headers $headers -UseBasicParsing
Pro Tip: Avoid the $ symbol in your credentials
Conclusion
Hope this article helps. Please note that this article is only for information purposes. We don’t claim and guarantees or liabilities whatsoever.
