Possible to publish a powerbi report without password?

Question

Good afternoon,

Our developers create some nice report with PBI but the problem is they use a database X. those database contain only Development data. Once they want to publish it, we must adapt their report to use the database Y which content Production data.

Is there a way for the developers to publish their report updating the database from X to Y , BUT NOT ADDING THE CREDENTIALS ? By this way, their report will be refreshed through the Gateway which will be setup to use the UID/PWD link to the DB Y (where the user cannot have access)

Thanks :)

question from:https://stackoverflow.com/questions/66049896/possible-to-publish-a-powerbi-report-without-password

Answer 1

Well, someone knows the password for database Y after all, right? Why not this one open the report in Power BI Desktop, change the datasource, and publish it? But there are options to redirect the report published by your developers while the datasource is still the development database (database X).

First, they can use connection specific parameters to define the datasource. Then, after publishing, the one who knows the credentials update the parameter values to redirect the database to the production database and enter the credentials.

Second one doesn't need any changes to the report. Let your developers publish it, and then use Power BI REST API to change the datasource and patch the credentials. This can be done for example with PowerShell using Power BI Management CmdLets. First, start PowerShell as administrator and install the CmdLets by executing the following script:

Install-Module MicrosoftPowerBIMgmt

If you don't have admin rights on the computer, or for some other reason do not want to install these for all users, you can install them for the current user only:

Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser

You didn't mention what database is used. This is important, so in the following example I will assume it is SQL Server. Update the values in the first few lines to point your production database and published report, then execute the script to change the data source:

# Fill these ###################################################
$workspaceName = "The name of the workspace where the report is published"
$datasetName = "The name of the report"
$sqlDatabaseServer = "Server name"
$sqlDatabaseName = "Database name"
$username = "[email protected]"
$password = "the password of the power bi user" | ConvertTo-SecureString -asPlainText -Force
################################################################

Import-Module MicrosoftPowerBIMgmt

Clear-Host

$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential | Out-Null

$workspace = Get-PowerBIWorkspace -Name $workspaceName

$dataset = Get-PowerBIDataset -WorkspaceId $workspace.Id -Name $datasetName

$datasource = Get-PowerBIDatasource -WorkspaceId $workspace.Id -DatasetId $dataset.Id

# Construct url
$workspaceId = $workspace.Id
$datasetId = $dataset.Id
$datasourceUrl = "groups/$workspaceId/datasets/$datasetId/datasources"

# Call the REST API to get gateway Id, datasource Id and current connection details
$datasourcesResult = Invoke-PowerBIRestMethod -Method Get -Url $datasourceUrl | ConvertFrom-Json

# Parse the response
$datasource = $datasourcesResult.value[0]
$gatewayId = $datasource.gatewayId
$datasourceId = $datasource.datasourceId
$sqlDatabaseServerCurrent = $datasource.connectionDetails.server
$sqlDatabaseNameCurrent = $datasource.connectionDetails.database

# Construct url for update
$datasourePatchUrl = "groups/$workspaceId/datasets/$datasetId/Default.UpdateDatasources"

# create HTTP request body to update datasource connection details
$postBody = @{
  "updateDetails" = @(
   @{
    "connectionDetails" = @{
      "server" = "$sqlDatabaseServer"
      "database" = "$sqlDatabaseName"
    }
    "datasourceSelector" = @{
      "datasourceType" = "Sql"
      "connectionDetails" = @{
        "server" = "$sqlDatabaseServerCurrent"
        "database" = "$sqlDatabaseNameCurrent"
      }
      "gatewayId" = "$gatewayId"
      "datasourceId" = "$datasourceId"
    }
  })
}

$postBodyJson = ConvertTo-Json -InputObject $postBody -Depth 6 -Compress

# Execute POST operation to update datasource connection details
Invoke-PowerBIRestMethod -Method Post -Url $datasourePatchUrl -Body $postBodyJson

# NOTE: dataset credentials must be reset after updating connection details

Now update the values in the beginning of the following script and execute it to patch the credentials for your production database:

# Fill these ###################################################
$workspaceName = "The name of the workspace where the report is published"
$reportName = "The name of the report"
$sqlUserName = "user name"
$sqlUserPassword = "password"
$username = "[email protected]"
$password = "the password of the power bi user" | ConvertTo-SecureString -asPlainText -Force
################################################################

Import-Module MicrosoftPowerBIMgmt

Clear-Host

$credential = New-Object System.Management.Automation.PSCredential($username, $password)

Connect-PowerBIServiceAccount -Credential $credential | Out-Null

$workspace = Get-PowerBIWorkspace -Name $workspaceName

$dataset = Get-PowerBIDataset -WorkspaceId $workspace.Id -Name $reportName

$workspaceId = $workspace.Id
$datasetId = $dataset.Id

$datasources = Get-PowerBIDatasource -WorkspaceId $workspaceId -DatasetId $datasetId

foreach($datasource in $datasources) {

  $gatewayId = $datasource.gatewayId
  $datasourceId = $datasource.datasourceId
  $datasourePatchUrl = "gateways/$gatewayId/datasources/$datasourceId"

  Write-Host "Patching credentials for $datasourceId"

  # HTTP request body to patch datasource credentials
  $userNameJson = "{""name"":""username"",""value"":""$sqlUserName""}"
  $passwordJson = "{""name"":""password"",""value"":""$sqlUserPassword""}"

  $patchBody = @{
    "credentialDetails" = @{
      "credentials" = "{""credentialData"":[ $userNameJson, $passwordJson ]}"
      "credentialType" = "Basic"
      "encryptedConnection" =  "NotEncrypted"
      "encryptionAlgorithm" = "None"
      "privacyLevel" = "Organizational"
    }
  }

  # Convert body contents to JSON
  $patchBodyJson = ConvertTo-Json -InputObject $patchBody -Depth 6 -Compress

  # Execute PATCH operation to set datasource credentials
  Invoke-PowerBIRestMethod -Method Patch -Url $datasourePatchUrl -Body $patchBodyJson
}

$datasetRefreshUrl = "groups/$workspaceId/datasets/$datasetId/refreshes"

Write-Host "Refreshing..."

Invoke-PowerBIRestMethod -Method Post -Url $datasetRefreshUrl