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

     

d_gosbell's avatar
d_gosbell
Super User
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
        
    }
]

 

ClaraPS33's avatar
ClaraPS33
Regular Visitor
4 years ago

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