topic Re: Power BI REST API via Powershell: Create Datasource in Developer

Power BI REST API via Powershell: Create Datasource

Sokon — Mon, 10 Sep 2018 13:47:16 GMT

I'm trying to automate gateway data source management using PowerShell. Sadly, the new MicrosoftPowerBIMgmt-Cmdlets don't provide commands for the Gateway-REST-APIs. Instead there's Invoke-PowerBIRestMethod that I managed to use for almost all of the Gatewaway-APIs.

However, the most important commands "Create Datasource" and "Update Datsource" resist my efforts. As seems to be the way of REST APIs, I don't get any useful error information, even when using Resolve-PowerBIError.

Here is my Powershell code that tries to create a Dummy Web DataSource without any credentials (should be easy, right?):

#Import-Module -name microsoftpowerbimgmt
Connect-PowerBIServiceAccount
######## Create Datasource ########
$gatewayId = "<your GatewayId goes here>"
$URL = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources"
$Body = @{
    dataSourceType = "Web"
    datasourceName = "Dummy Web Connection"
    connectionDetails = "{`"path`":`"https://de.wikipedia.org/wiki/Liste_der_L%C3%A4nder_nach_Bruttoinlandsprodukt_pro_Kopf`"}"
    credentialDetails = @{
        credentialType = "Anonymous"
        credentials = "{`"credentialData`":`"`"}"
         encryptedConnection = "Encrypted"
        encryptionAlgorithm = "None"
        privacyLevel = "None"
    }
} | ConvertTo-Json
$Body
$response = Invoke-PowerBIRestMethod -Url $URL -Method POST -Body $Body

The $Body objects holds the following JSON which has the same format like shown in this example:

{
    "datasourceName":  "Dummy Web Connection",
    "credentialDetails":  {
                              "encryptedConnection":  "Encrypted",
                              "credentialType":  "Anonymous",
                              "credentials":  "{\"credentialData\":\"\"}",
                              "privacyLevel":  "None",
                              "encryptionAlgorithm":  "None"
                          },
    "dataSourceType":  "Web",
    "connectionDetails":  "{\"path\":\"https://de.wikipedia.org/wiki/Liste_der_L%C3%A4nder_nach_Bruttoinlandsprodukt_pro_Kopf\"}"
}

However, I always get a "Bad Request" answer.

Does anybody know how to fix my code to get the example running?

Any hint is greatly appreciated! Thanks!

Re: Power BI REST API via Powershell: Create Datasource

Ronnie7 — Tue, 11 Sep 2018 17:31:16 GMT

Hi, i know this is an old post... did you ever get it resolved?

I'm facing the exact same issue and have been begging for help, but no success...

Re: Power BI REST API via Powershell: Create Datasource

Sokon — Wed, 12 Sep 2018 08:11:07 GMT

@Ronnie7: This is actually a brand new post!

Nothing changed since, apart from a new idea: If we knew the URL to the YAML file we could use Swagger ourselves to get a possibly updated documentation...

Re: Power BI REST API via Powershell: Create Datasource

Ronnie7 — Wed, 12 Sep 2018 13:33:41 GMT

@Sokon i'm trying to create a new data source on a gateway via REST API POST Method.

Here's the body of the request in JSON format:

$body2='{
"dataSourceType": "SQL",
"connectionDetails": "{\"server\":\"test2\",\"database\":\"MyDatabase\"}",
"datasourceName": "Sample Datasource",
"credentialDetails": {
"credentialType": "Basic",
"credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"Myuser\"},{\"name\":\"password\", \"value\":\"***\"}]}",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "None"
}
}'

{
Invoke-PowerBIRestMethod -url "https://api.powerbi.com/v1.0/myorg/gateways/$GatewayID/datasources" -Method post -body $body2

I get error bad request 400... when i try to add this data source manually from the PowerBI administration GUI it's working.
If i'm using the GET method it's working as well and i can see all the data sources on that gateway...

Any ideas?

Re: Power BI REST API via Powershell: Create Datasource

Sokon — Wed, 12 Sep 2018 13:57:37 GMT

@Ronnie7 There are some things I learned from experimenting with the (now working) PS-Code for the "Add Datasource Users"-Command I can share:

Parameters seem to be case sensitive. "READ" is wrong "Read" worked. When you run the "Get Datasource" command, it will return "Sql" as dataSourceType and not "SQL".
I can add Datasource Users only if the email addresses were valid. Fake or dummy addresses provoked an error. Perhaps that's the same with datasources. That's the reason why a tried to create a Web datasource with an anonymous authentication
Here is a similar thread where someone wants to use C# instead of PowerShell. Nevertheless, look at the part/discussion about the credentials-tag: It's some kind of encrypted string that needed some clarification. Perhaps you understand that better than me?

Re: Power BI REST API via Powershell: Create Datasource

Ronnie7 — Wed, 12 Sep 2018 14:11:17 GMT

@Sokon thanks for the info.

I have seen that thread that you mentioned, and I also struggle to understand it. Looks like we are missing something there...
I'll actually try to create a new web data source with anonymouts login and see if that will work.

if so, i'll share the code.

Re: Power BI REST API via Powershell: Create Datasource

Anonymous — Fri, 21 Dec 2018 17:41:12 GMT

@Sokon @Ronnie7 did this work please share the code if yes i m facing similar issue

Re: Power BI REST API via Powershell: Create Datasource

Sokon — Wed, 02 Jan 2019 13:11:35 GMT

No progress here, sorry. But I posted an idea where you can vote for the documentation to be updated!

Re: Power BI REST API via Powershell: Create Datasource

Anonymous — Wed, 02 Jan 2019 15:30:08 GMT

{
"datasourceType": "WEB",
"connectionDetails": "{\"url\":\"http://app.powerbi.com\"}",
"datasourceName": "teststratumwebdatasource",
"credentialDetails": {
"credentialType": "Windows",
"credentials": "{\"credentialData\":[{\"name\":\"username\", \"value\":\"username\"},{\"name\":\"password\", \"value\":\"password\"}]}",
"encryptedConnection": "Encrypted",
"encryptionAlgorithm": "None",
"privacyLevel": "None"
}

I tried to create datasource for web thru rest api which failed. does anyone else has an working example in this group for creating datasource using powerbi rest api calls

Re: Power BI REST API via Powershell: Create Datasource

sm3 — Tue, 28 May 2019 14:54:47 GMT

BUMP

I've found that there are lack of specification of parameters in body in documentation
For examaple what should the body look like when we want setting new datasource of SQL in gateway?
Here are some threads without reply (last link is mine):

https://community.powerbi.com/t5/Developer/Problem-with-creating-datasource-under-gateway-through-API/td-p/414814
https://community.powerbi.com/t5/Developer/PowerBI-REST-API-error-Need-help/td-p/509452

https://community.powerbi.com/t5/Developer/Creating-new-SQL-Datasource-for-Gateway-with-Powershell/td-p/695814/jump-to/first-unread-message

If any of you know the answer please share correct examples.
MS please make up your documentation: https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/createdatasource.

Greetings

Re: Power BI REST API via Powershell: Create Datasource

Anonymous — Mon, 07 Oct 2019 08:04:22 GMT

The credentials from credentialDetails need to be encripted unsing RSA algorithm before calling the api. I've used an alghorthim posted in https://community.powerbi.com/t5/Developer/How-do-I-update-the-Credentials-after-I-upload-a-report-to-POWER/td-p/223243 and converted it to powershell. The algorithm from https://docs.microsoft.com/en-us/power-bi/developer/encrypt-credentials is also good. Also the server name needs to have double the amount of "\" charahters to work.

function CreateDatasourceForGateway([guid]$GatewayId, 
                                    [string]$DatasourceName, 
                                    [string]$TenantServer, 
                                    [string]$TenantDatabase,
                                    [string]$TenantDataSourceUser,
                                    [string]$TenantDataSourcePassword) {   

    $gateway = Invoke-PowerBIRestMethod `
                -Url "https://api.powerbi.com/v1.0/myorg/gateways/$GatewayId" `
                -Method GET ` | ConvertFrom-Json                

    $datasourceDetails = ConvertTo-Json -Depth 4 -InputObject $(@{
        dataSourceType = "Sql"
        connectionDetails = '{"server":"'+$TenantServer.Replace("\","\\")+'","database":"'+$TenantDatabase+'"}'
        datasourceName = $DatasourceName
        credentialDetails = @{
            credentialType = "Basic"
            credentials = Encript `
                            -Username $TenantDataSourceUser `
                            -Password $TenantDataSourcePassword `
                            -GatewayExponent $gateway.publicKey.exponent `
                            -GatewayModulus $gateway.publicKey.modulus
            encryptedConnection = "Encrypted"
            encryptionAlgorithm = "RSA-OAEP"
            privacyLevel = "None"
        }
    })

    $result = Invoke-PowerBIRestMethod `
            -Url "https://api.powerbi.com/v1.0/myorg/gateways/$GatewayId/datasources" `
            -Method POST `
            -Body $datasourceDetails
}

function Encript([string]$Username,[string]$Password,[string]$GatewayExponent,[string]$GatewayModulus) {
    $segmentLength = 85
    $encryptedLength = 128
    $plaintTxt = '{"credentialData":[{"value":"'+$Username+'","name":"username"},{"value":"'+$Password+'","name":"password"}]}'
    $rsa = New-Object System.Security.Cryptography.RSACryptoServiceProvider ($encryptedLength * 8)
    $parameters = $rsa.ExportParameters($false)
    $parameters.Exponent = [System.Convert]::FromBase64String($GatewayExponent)
    $parameters.Modulus = [System.Convert]::FromBase64String($GatewayModulus)
    $rsa.ImportParameters($parameters)
    $plainTextArray = [System.Text.Encoding]::UTF8.GetBytes($plaintTxt)    
    $hasIncompleteSegment = $plainTextArray.Length % $segmentLength -ne 0

    $segmentNumber = If (-not $hasIncompleteSegment) {[int]($plainTextArray.Length / $segmentLength)} Else {[int]($plainTextArray.Length / $segmentLength) + 1}
    $encryptedData = [System.Byte[]]::CreateInstance([System.Byte],$segmentNumber * $encryptedLength)
    [int]$encryptedDataPosition = 0;

    For ($i=0; $i -lt $segmentNumber; $i++) {
        $lengthToCopy = If ($i -eq ($segmentNumber - 1) -and $hasIncompleteSegment) {$plainTextArray.Length % $segmentLength} Else {$segmentLength}
        $segment = [System.Byte[]]::CreateInstance([System.Byte],$lengthToCopy)
        [System.Array]::Copy($plainTextArray,$i*$segmentLength,$segment,0,$lengthToCopy)
        $segmentEncryptedResult = $rsa.Encrypt($segment, $true)
        [System.Array]::Copy($segmentEncryptedResult,0,$encryptedData,$encryptedDataPosition,$segmentEncryptedResult.Length)
        $encryptedDataPosition += $segmentEncryptedResult.Length;
    }

    return [System.Convert]::ToBase64String($encryptedData)
}

Connect-PowerBIServiceAccount

CreateDatasourceForGateway `
    -GatewayId $GatewayId `
    -DatasourceName "$TenantReportPrefix Datasource"`
    -TenantServer $TenantServer `
    -TenantDatabase $TenantDatabase `
    -TenantDataSourceUser $TenantDataSourceUser `
    -TenantDataSourcePassword $TenantDataSourcePassword

Re: Power BI REST API via Powershell: Create Datasource

pbiembed4 — Mon, 02 Dec 2019 14:09:38 GMT

Thanks a LOT !

Got stuck couple of hours only to found out what you suggested:

--> Also the server name needs to have double the amount of "\" charahters to work.

Re: Power BI REST API via Powershell: Create Datasource

Anonymous — Thu, 17 Sep 2020 12:53:05 GMT

Anyone figured out how to add a datasource without encryption and without credentials like the checkbox "skip test connection"?

I tried using "anonymus" credential type but that did not work yet in my tests.

Re: Power BI REST API via Powershell: Create Datasource

niravmodi — Sat, 10 Oct 2020 03:04:13 GMT

Hi Alex,

I am getting this weird error message when used your code.

Invoke-PowerBIRestMethod : One or more errors occurred.
At line:29 char:15
+ $result = Invoke-PowerBIRestMethod `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

With resolve-azerror -last

WARNING: Breaking changes in the cmdlet 'Resolve-AzError' :
WARNING: - The `Resolve-Error` alias will be removed in a future release. Please change any scripts that use this alias to use `Resolve-AzError` instead.

WARNING: NOTE : Go to https://aka.ms/azps-changewarnings for steps to suppress this breaking change warning, and other information on breaking changes in Azure PowerShell.

HistoryId: 7

Message : Response status code does not indicate success: 400 (Bad Request).
StackTrace : at System.Net.Http.HttpResponseMessage.EnsureSuccessStatusCode()
at Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod.<InvokeRestMethod>d__31.MoveNext()
Exception : System.Net.Http.HttpRequestException
InvocationInfo : {Invoke-PowerBIRestMethod}
Line : $result = Invoke-PowerBIRestMethod `

Position : At line:29 char:15
+ $result = Invoke-PowerBIRestMethod `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
HistoryId : 7

The Azure PowerShell team is listening, please let us know how we are doing: https://aka.ms/azpssurvey?Q_CHL=FEEDBACK.

Do I need to have Power Bi Admin permissions ?

I have full administrative permissions on Power Bi gateway where trying to build this data source.