Search code examples
powershellrestpower-bi-report-server

Set report data source and credentials on Power BI Report Server through REST API to establish a scheduled refresh

My task is the following: using the Power BI Report Server (PBIRS) REST API, upload an embedded report (embedded meaning that the data model is integrated in the report itself - a simple import from an SQL Server database, hence no live connection or DirectQuery etc.) to PBIRS, set the credentials (ConnectionString, Windows Authentication username and password) to access the data source (the database) and set up a daily scheduled refresh.

With a Powershell script I'm able to upload the report with no problem, however complications arise when I try to set the credentials to the data source. Here is the code I currently have to accomplish this:

$payload0 = 
'
{
    "Name": "Data Source test",
    "Description": "string",
    "Path": "/Valid/Path",
    "Type": "DataSource",
    "Hidden": false,
    "Size": 0,
    "ModifiedBy": "string",
    "ModifiedDate": "2020-01-13T15:51:04Z",
    "CreatedBy": "string",
    "CreatedDate": "2020-01-13T15:51:04Z",
    "IsFavorite": false,
    "IsEnabled": true,
    "ConnectionString": "valid.connection\\string",
    "DataModelDataSource": {
      "AuthType": "Windows",
      "SupportedAuthTypes": [
        "Windows"
      ],
      "Kind": "SQL",
      "ModelConnectionName": "string",
      "Secret": "",
      "Type": "Import ",
      "Username": "myUserName"
    },
    "DataSourceSubType": "DataModel",
    "DataSourceType": "SQL",
    "IsOriginalConnectionStringExpressionBased": false,
    "IsConnectionStringOverridden": false,
    "CredentialRetrieval": "prompt",
    "CredentialsByUser": {
      "DisplayText": "someText",
      "UseAsWindowsCredentials": true
    },
    "CredentialsInServer": {
      "UserName": "myUserName",
      "Password": "myPassword",
      "UseAsWindowsCredentials": true,
      "ImpersonateAuthenticatedUser": true
    },
    "IsReference": false
  }
'

$restApiUri = $ReportPortalUri + "/api/v2.0/DataSources"

Invoke-RestMethod -Uri $restApiUri -Method Post -Body $payload0 -ContentType "application/json" -UseDefaultCredentials -UseBasicParsing -Verbose

After running the code I get a 201 Created response JSON that indicates the successful creation of the data source and its access credentials. If I check on the Manage pane of the report on PBIRS I don't see any changes made to the data sources.

My next step was to set the credentials manually by entering my username and password on the report server and then executing the following code to set up a scheduled refresh:

$payload1 = 
'
{
    "ParameterValues":[ 

    ],
    "Description":"testSchedule",
    "CatalogItemPath":"/Valid/Path",
    "EventType":"DataModelRefresh",
    "Schedule":{ 
        "Definition":{ 
            "EndDate":"0001-01-01T00:00:00Z",
            "StartDateTime":"2019-01-15T02:00:00Z",
            "EndDateSpecified":false,
            "Recurrence":{ 
                "DailyRecurrence": { 
                "DaysInterval":1
                }
            }
        }
    }
}
'

$restApiUri = $ReportPortalUri + "/api/v2.0/CacheRefreshPlans"

Invoke-RestMethod -Uri $restApiUri -Method Post -Body $payload1 -ContentType "application/json" -UseDefaultCredentials -UseBasicParsing -Verbose

Running this code results in the successful creation of the scheduled refresh given that I set up the credentials to the data source manually beforehand.

So my question in short: how to set up the credentials (Windows Authentication and username/password) of a data source (the SQL Server connection defined by a valid ConnectionString) with the PBIRS REST API?

Any help would be appreciated!

Solution

  • It turns out that there is another way to approach the task which is to use a different API altogether. Using the ReportingServicesTools methods we can upload the report to the PBIRS and set its data source credentials the following way:

    # create PBIRS session
Write-Output "Creating a session to the Report Server $ReportPortalUri"
$session = New-RsRestSession -ReportPortalUri $ReportPortalUri        

# upload report with overwrite
write-output "Upload report with overwrite"
Write-RsRestCatalogItem -WebSession $session -Path $FilePath -RsFolder $RsFolder -Description $Description -Overwrite 

# get report name
$reportName = [System.IO.Path]::GetFileNameWithoutExtension($FilePath)

# get existing data source properties
$dataSource = Get-RsRestItemDataSource -WebSession $session -RsItem "$RsFolder/$reportName"

# setting up data source credentials
$dataSource.DataModelDataSource.AuthType = "Windows"
$dataSource.CredentialRetrieval = "Store"
$dataSource.DataModelDataSource.UserName = "myUsername"
$dataSource.DataModelDataSource.Secret = "myPassword"

# applying credentials
Set-RsRestItemDataSource -WebSession $session -RsItem "$RsFolder/$reportName" -RsItemType "PowerBIReport" -DataSources $dataSource