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

[Anonymous]

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!

[MRIZ]

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/PowerB...
$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

[d_gosbell · Accepted solution]

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
        
    }
]

[ClaraPS33]

Hi @d_gosbell 

I am trying to change the credentials of a datasource with the power bi server api but I get the error "(400) Bad Request".

The difference is that when I make the call to get the details from the datasource, this is the only thing that is returned( there are many properties that you indicate in your call and  I dont have the information):
{
Id=XXXXXXXXXXXXXXXXXX
Name=;
Description=;
Path=;
Type=DataSource;
Hidden=False;
Size=0;
ModifiedBy="XXXXXX";
ModifiedDate=2022-07-13T12:17:24.08+02:00;
CreatedBy="XXXXXX";
CreatedDate=2022-07-13T12:16:38.52+02:00;
ParentFolderId=;
IsFavorite=False;
ContentType=;
Content=;
IsEnabled=True;
ConnectionString=dwh_pro;
DataSourceType=;
IsOriginalConnectionStringExpressionBased=False;
IsConnectionStringOverridden=False;
CredentialRetrieval=prompt;
IsReference=False;
DataSourceSubType=DataModel;
Roles=System.Object[];
CredentialsByUser=;
CredentialsInServer=;
DataModelDataSource=
}

About datasource: it is a connection to Oracle with Basic Autehntication (no windows like the example): Username and Password.

I detail the script to see if you can help me:

$payload =
@"
{
"Id": "XXXXXXXXXXXXXXXXXX",
"Name": "",
"Description": "",
"Path": "",
"Type": "DataSource",
"Hidden": False,
"Size": 0,
"ModifiedBy": "XXXXXXXX",
"ModifiedDate": "2022-07-13T12:17:24.08+02:00",
"CreatedBy": "XXXXXXXX,
"CreatedDate": "2022-07-13T12:16:38.52+02:00",
"ParentFolderId": "XXXXXXXXXX",
"ContentType": "",
"Content": "",
"IsFavorite": False,
"IsEnabled": true,
"ConnectionString": "dwh_pro",
"DataModelDataSource": {
"AuthType": "Unknown",
"SupportedAuthTypes": [
""
],
"Kind": "Oracle",
"ModelConnectionName": "",
"Secret": "XXXXX",
"Type": "Import",
"Username": "XXXXXX"
},
"DataSourceSubType": "DataModel",
"DataSourceType": "Oracle",
"IsOriginalConnectionStringExpressionBased": False,
"IsConnectionStringOverridden": true,
"CredentialRetrieval": "store",
"CredentialsByUser": {
"DisplayText": "",
"UseAsWindowsCredentials": false
},
"CredentialsInServer": {
"UserName": "XXXXX",
"Password": "XXXXX",
"UseAsWindowsCredentials": false,
"ImpersonateAuthenticatedUser": false
},
"IsReference": false

}
}
"@
$restApiUri = "https://XXXXX" + "/api/v2.0/PowerBIReports(id)/DataSources"
$user = "XXXXX"
$pass= "XXXXXX"
$proxy = "http://XXXXXX"
$proxycred = "XXXXX"
$secpasswd = ConvertTo-SecureString $pass -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($user, $secpasswd)
$inicio = (get-date).ToString('f')

Invoke-RestMethod -Uri $restApiUri -Method 'Patch' -Body $payload -ContentType "application/json" -Credential $credential -Proxy $proxy -ProxyCredential $proxycred -UseBasicParsing -Verbose

Thanks you very much

[Anonymous]

Thank you very much, worked perfectly!