Power BI - Powershell deploy, update credentials

[Data-estDog]

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

        }

    }

}

[claireelyse]

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,