cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sokon
Advocate IV
Advocate IV

Power BI REST API via Powershell: Create Datasource

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!

 

 

13 REPLIES 13
Anonymous
Not applicable

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-t... 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

 

 

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. 

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.

 

 

 

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.

Ronnie7
Helper II
Helper II

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...

@Ronnie7: This is actually a brand new post! Smiley Wink

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...

 

@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?

@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?

@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. 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

{
"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 

sm3
Frequent Visitor

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-AP...
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/t...

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



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors