Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Set data source credentials on Power BI Report Server through REST API to create 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 ...
PowerShell
REST PBIRS
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    I've just done some tests and the following payload worked for me doing a PATCH http request against http://localhost/Reports/api/v2.0/PowerBIReports( {id} )/DataSources. Note I've changed the CredentialRetrieval value to "store" and I found I had to add the username and password both in the CredentialsInServer object and as the username/secret in the DataModelDataSource (if I did not so this second bit the scheduled refresh would not work). I've also stripped out the creation and modification fields as I don't think you can alter those from the client side anyway.

     

    [
    {
        "Id": "dcf1ca8d-4320-e911-bbac-94b86df86523",
        "Name": null,
        "Description": null,
        "Hidden": false,
        "Path": "",
        "IsEnabled": true,
        "DataSourceSubType": "DataModel",
        "DataModelDataSource": {
            "Type": "Import",
            "Kind": "SQL",
            "AuthType": "Windows",
            "SupportedAuthTypes": [
                "Windows",
                "UsernamePassword"
            ],
            "Username": "domain\\user",
            "Secret": "MyPassword",
            "ModelConnectionName": ""
        },
        "IsReference": false,
        "DataSourceType": "SQL",
        "ConnectionString": "localhost\\sql17;AdventureWorksDW2017",
        "IsConnectionStringOverridden": true,
        "CredentialRetrieval": "store",
        "CredentialsInServer": {
            "UserName": "domain\\user",
            "Password": "MyPassword",
            "UseAsWindowsCredentials": true,
            "ImpersonateAuthenticatedUser": false
        },
        "CredentialsByUser": null
        
    }
]

     

MRIZ's avatar
MRIZ
Helper II
2 years ago

I wrote belo script in Powershell to achieve this:

$urlapiEndpoint = "/datasets/$($datasetObject.Id)/datasources"

$apiResponse = Invoke-PowerBIRestMethod -Url $urlapiEndpoint -Method Get

Write-Host "API Response:`n$apiResponse"

$parsedJSON = $apiResponse | ConvertFrom-Json

Write-Host "Parsed Response:`n$parsedJSON"

# Extract values from the API response
$datasourceId = $parsedJson.value[0].datasourceId
$gatewayId = $parsedJson.value[0].gatewayId

# Output the extracted values
Write-Host "DatasourceId: $datasourceId"
Write-Host "GatewayId: $gatewayId"

#region Initialize

# EncryptGatewayCredentials script path

# Update datasource API details
$GatewayId = $gatewayId #"918adac3-337a-4023-8550-706671a65fd9"
$DatasourceId = $datasourceId #"e36df50d-ba00-4087-a34f-b5a86482c0d1"
$UpdateDatasourceUrl = "/gateways/$GatewayId/datasources/$DatasourceId"
$GetGatewayUrl = "/gateways/$GatewayId"

## Datasource details
##SQL/Windows
# username = SQL user in case of basic credentials, or windows user in case of windows credentials
$username = $dbuser
# password = SQL user password in case of basic credentials, or windows user password in case of windows credentials
$password = $dbpass
#endregion

 

# Get gateway public key
$GatewayObject = Invoke-PowerBIRestMethod -Url $GetGatewayUrl -Method Get | ConvertFrom-Json

$GatewayObject

$GatewayPublicKey = $GatewayObject.publicKey
$gatewayExponent = $GatewayPublicKey.exponent
$gatewayModulus = $GatewayPublicKey.modulus

# Encrypt basic credentials using EncryptGatewayCredentials script
Import-Module $EncryptCredentialsScriptPath


function EncryptBasicCredentials {
param (
[Parameter(Mandatory=$True,Position=1)]
[String]$Username,
[Parameter(Mandatory=$True,Position=2)]
[String]$PasswordAsString,
[Parameter(Mandatory=$True,Position=3)]
[string]$GatewayExponent,
[Parameter(Mandatory=$True,Position=4)]
[string]$GatewayModulus
)

write-host "In the funcion "

# Create the objects to perform the necessary encryption on the credentials. Again, since I'm using basic credentials, I'm constructing a new BasicCredentials class. Other classes can be found here: https://github.com/microsoft/PowerBI-CSharp/tree/bf7cdf047a0218f7a8555fa7966445812a043955/sdk/PowerBI.Api/Extensions/Models/Credentials
$gatewayKeyObj = [Microsoft.PowerBI.Api.Models.GatewayPublicKey]::new($GatewayExponent, $GatewayModulus)
$credentialsEncryptor = [Microsoft.PowerBI.Api.Extensions.AsymmetricKeyEncryptor]::new($gatewayKeyObj)
$basicCreds = [Microsoft.PowerBI.Api.Models.Credentials.BasicCredentials]::new($username, $PasswordAsString)

# Construct the CredentialDetails object. The resulting "Credentials" property on this object will have been encrypted appropriately, ready for use in the request payload.
$credentialDetails = [Microsoft.PowerBI.Api.Models.CredentialDetails]::new(
$basicCreds,
[Microsoft.PowerBI.Api.Models.PrivacyLevel]::Organizational,
[Microsoft.PowerBI.Api.Models.EncryptedConnection]::Encrypted,
$credentialsEncryptor)

# Construct the body for the API request.
$body = @{
credentialDetails = @{
credentialType = "Basic";
credentials = $credentialDetails.Credentials;
encryptedConnection = "Encrypted";
encryptionAlgorithm = "RSA-OAEP";
privacyLevel = "Organizational";
}
}
$bodyJson = $body | ConvertTo-Json

Write-Output $bodyJson
}


$encryptedCredentials = EncryptBasicCredentials -Username $username -PasswordAsString $password -GatewayExponent $gatewayExponent -GatewayModulus $gatewayModulus

$encryptedCredentials
$UpdateDatasourceUrl

Invoke-PowerBIRestMethod -Url $UpdateDatasourceUrl -Method Patch -Body $encryptedCredentials