Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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.
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!
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:
@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.
No progress here, sorry. But I posted an idea where you can vote for the documentation to be updated!
{
"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
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
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
18 | |
11 | |
5 | |
4 | |
3 |