topic Re: Problem to Modify Dataset Credential in Developer

Problem to Modify Dataset Credential

Anonymous — Tue, 16 Jun 2020 14:06:22 GMT

Hello, i hope you´re doing well.

I´m having this issue every time i try to modify credential of the Dataset.

Invoke-PowerBIRestMethod : One or more errors occurred.
At C:\Users\ZN661HL\source\repos\ImportantStaff\CICD\ModifyDatasetCredential.ps1:61 char:16
+ ... $response= Invoke-PowerBIRestMethod -Url "gateways/$($BounGateway.va ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

This is the Code i´m using:

$datasetname = "DummyPBIDataset"

$workspacename = "CIT Datawarehouse - QA"

## user credentials

$username = "xxxxxxxxxxx"

$password = "xxxxxxxxx"

$client_secret_temp = "?xxxxxxxxx" | ConvertTo-SecureString -AsPlainText -Force

$client_id_temp = "xxxxxxxxxxxx"

$tenant_id_temp = "xxxxxxxxxxxxxxxxxx"

#$clientsec = "?YxX3q?f.Gk?OJxR4WUshAKjsB6NA1IJ" | ConvertTo-SecureString -AsPlainText -Force

$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $client_id_temp, $client_secret_temp

Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenant_id_temp

##Getworksapce

$workspace = Get-PowerBIWorkspace -Name $workspacename

# GetDataSets

$DatasetResponse = Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets" -Method Get | ConvertFrom-Json

# Get DataSet

$datasets = $DatasetResponse.value

#Iterate all Datasets to find Id of the DummnyDataset

foreach ($dataset in $datasets) {

if ($dataset.name -eq $datasetname) {

$datasetid = $dataset.id;

break;

}

## Take Over DataSet

Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver" -Method Post

## update data source credentials

#This Api is the recomended for Microsoft

#Retrieve the Dataset Gateway

$BounGateway = Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/datasources" -Method GET | ConvertFrom-Json

$BounGateway

#This call to the Api was commented because the recomendation of microsoft to use another one

#$BounGateway=Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.GetBoundGatewayDataSources" -Method GET | ConvertFrom-Json

$UpdateUserCredential = @{

credentialType ="Basic"

basicCredentials = @{

username= $username

password=$password

}

} | ConvertTo-Json

try

{

$response= Invoke-PowerBIRestMethod -Url "gateways/$($BounGateway.value.gatewayId)/datasources/$($BounGateway.value.id)" -Method PATCH -Body $UpdateUserCredential | ConvertFrom-Json

$response

}

catch

{

Write-Host "An error occurred:"

Write-Host $_

}

finally

{

}

#$response =Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/gateways/$($BounGateway.value.gatewayId)/datasources/$($BounGateway.value.datasourceId)" -Method PATCH -Body $UpdateUserCredential | ConvertFrom-Json

#$response = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/gateways/039da9e0-b0f3-4823-8776-4bec53ac90cb/datasources/30ba4d64-b870-45a3-8db5-3b4b9f5b2f4a" -Method PATCH -Body $UpdateUserCredential | ConvertFrom-Json

#$response

Thank you @Jayendran for any help in advance.

Re: Problem to Modify Dataset Credential

Jayendran — Tue, 16 Jun 2020 14:17:15 GMT

Hi @Anonymous ,

Powershell is poor at giving the exact error especially for REST API calls. Could you please install the fiddler and try to get what is the exact error ? With the exact error only we can able to move forward for the solution.

Re: Problem to Modify Dataset Credential

Anonymous — Tue, 16 Jun 2020 15:16:42 GMT

Sorry, here the error i´m getting :

HTTP/1.1 404 Not Found
Cache-Control: no-store, must-revalidate, no-cache
Pragma: no-cache
Transfer-Encoding: chunked
Content-Type: application/json; odata.metadata=minimal
Strict-Transport-Security: max-age=31536000; includeSubDomains
X-Frame-Options: deny
X-Content-Type-Options: nosniff
RequestId: 52113063-e1ce-45ba-979d-1760cd5e88a4
OData-Version: 4.0
Access-Control-Expose-Headers: RequestId
request-redirected: true
home-cluster-uri: https://wabi-europe-north-b-redirect.analysis.windows.net/
Date: Tue, 16 Jun 2020 14:59:18 GMT

EB
{
"error":{
"code":"","message":"No HTTP resource was found that matches the request URI 'http://wabi-europe-north-b-redirect.analysis.windows.net/v1.0/myorg/gateways/7f7807d7-c24a-42e4-b578-f3fb418980bb/datasources/'."
}
}
0

I´m using this code to retrieve the gateway:

i took this code from your ci cd pipeline @Jayendran

$BounGateway = Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/datasources" -Method GET | ConvertFrom-Json

i already tried using the power bi UI to update credential and i was able to do it, but i realize that gateway.id and datasource id were diferents.

This is what i get from power bi UI.

https://api.powerbi.com/v1.0/myorg/gateways/039da9e0-b0f3-4823-8776-4bec53ac90cb/datasources/30ba4d64-b870-45a3-8db5-3b4b9f5b2f4a"

This is using Invoke-PowerBIRestMethod

gatewayid=7f7807d7-c24a-42e4-b578-f3fb418980bb

datasourceid=846f9253-72f4-4f85-abcd-04ee75e5dce5

completly diferents.

Re: Problem to Modify Dataset Credential

Jayendran — Wed, 17 Jun 2020 07:09:54 GMT

Hi @Anonymous ,

Both gateways are totally different you should not compare it. The boundgateway is used by microsoft behind the screen in other hand the gateway url which you got is the one which you installed it.

What is your data source ? Is that a cloud or on-premises (using any on-premiese datagateway) ?

Re: Problem to Modify Dataset Credential

Anonymous — Wed, 17 Jun 2020 12:43:23 GMT

Hi @Jayendran ,

I´m using Azure Sql Server, i don´t remember having installed a gateway.

might it have installed because i'm using a vpn to connect to azure sql server?

Re: Problem to Modify Dataset Credential

Jayendran — Thu, 18 Jun 2020 06:06:27 GMT

Hi @Anonymous ,

I'm not sure you are using SPN or master account. In either of the case please make sure to give access to the workspace as admin.

Then you make sure to take over the dataset before udpating the credentials ?

As the error saying it's not found. May be you are missing either of the above two.

For AzureSQL i don't think you need on-premises Data gateway. But the bounddefault gateway should always need ,which is quite different from the typical on-premises data gateway.

Re: Problem to Modify Dataset Credential

Anonymous — Thu, 18 Jun 2020 12:17:54 GMT

Thank you @Jayendran , you were right i was missing take over dataset before update credential.

one more time thank you.

Re: Problem to Modify Dataset Credential

Jayendran — Thu, 18 Jun 2020 12:20:38 GMT

@Anonymous glad to help