Power BI - Powershell deploy, update credentials

Data-estDog — Wed, 04 Oct 2023 15:09:18 GMT

I am new to powershell and power bi rest api.

I am attemping to deploy a report, update credentials, update parameter of a .pbix file. The .pbix file uses OAuth2 via personal cloud connect, to point to a Azure SQL DB instance. The environment uses Pro licensing. I am using a user account that has admin rights to the workspace.

I am able to deploy the pbix file to the desired workspace.

I am having trouble finding a solid example of how to issue the REST Api command via powershell to accomplish changing the credentials. The examples I have make use of Invoke-PowerBIRestMethod (see below). I recieve the following error:

An error has occurred!!
Error Line Number : 158
Error Command : Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body ("$ApiRequestBody")
Error Message : One or more errors occurred. ({
"code": "DM_GWPipeline_Gateway_DataSourceAccessError",
"pbi.error": {
"code": "DM_GWPipeline_Gateway_DataSourceAccessError",
"parameters": {},
"details": [],
"exceptionCulprit": 1
}
})

The gateway part is certainly throwing me off. I would think I do not need one but the "Maps to: Personal Cloud Connect" under the "Gateway and cloud connection" section in data set Settings makes me think otherwise. Or my examples just don't fit my scenario. Please advise

#Retrieve the workspace

$WorkspaceObject = (Get-PowerBIWorkspace -Name $WorkspaceName <#-Scope Organization#>)

#Retrieve the report

$PbiReportObject = (Get-PowerBIReport -Workspace $WorkspaceObject -Name $ReportName)

#Retrieve all data sources

$PbiDataSourcesObject = (Get-PowerBIDatasource -DatasetId $PbiReportObject.DatasetId <#-Scope Organization#>)

<#-------------------------------------------------------------------------------------------

Iterate through data sources and update credentials for all SQL Server sources

-------------------------------------------------------------------------------------------#>

foreach ($DataSource in $PbiDataSourcesObject) {

#Store the data source id in a variable (for ease of use later)

$DataSourceId = $DataSource.DatasourceId

#API url for data source

$ApiUrl = "gateways/" + $DataSource.GatewayId + "/datasources/" + $DataSourceId

#$ApiUrl = "groups/" + $WorkspaceObject.Id + "/datasets"

#Format username and password, replacing escape characters for the body of the request

$FormattedDataSourceUser = $DataSourceUser.Replace("\", "\\")

$FormattedDataSourcePassword = $DataSourcePassword.Replace("\", "\\")

#Build the request body

$ApiRequestBody = @"

{

"credentialDetails": {

"credentialType": "OAuth2",

"credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"$FormattedDataSourceUser\"}, {\"name\":\"password\", \"value\":\"$FormattedDataSourcePassword\"}]}",

"encryptedConnection": "Encrypted",

"encryptionAlgorithm": "None",

"privacyLevel": "Organizational"

}

#If it's a sql server source, change the username/password

if ($DataSource.DatasourceType = "Sql") {

#Update username & password

Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body ("$ApiRequestBody")

Write-Output "Credentials for data source ""$DataSourceId"" successfully updated..." `n

}

Re: Power BI - Powershell deploy, update credentials

claireelyse — Tue, 10 Oct 2023 21:51:53 GMT

Hi,

When I run this script under the service account I get the below error:

Invoke-PowerBIRestMethod: One or more errors occurred. ({
"code": "UnknownError",
"pbi.error": {
"code": "UnknownError",
"parameters": {},
"details": [],
"exceptionCulprit": 1
}
})
Invoke-PowerBIRestMethod: Encountered errors when invoking the command: {
"code": "UnknownError",
"pbi.error": {
"code": "UnknownError",
"parameters": {},
"details": [],
"exceptionCulprit": 1
}
}

I get this error anytime I call any of the API's that use a Gateway ID. Is there a permission I am missing on the service account? It works fine for any of the workspace/report API's.

best,

topic Re: Power BI - Powershell deploy, update credentials in Developer

Power BI - Powershell deploy, update credentials

Re: Power BI - Powershell deploy, update credentials