Forum Discussion

NickA01's avatar
NickA01
Icon for Resolver III rankResolver III
2 years ago

Powershell Update report credentials --Service Principle

Hi All. 

I've got 4 workspaces for our Enterprise reports that are enviromentally aligned (DEV, QA, UAT & Prod) 
We do our PowerBi report deployments using an Azure DevOps Deployment pipeline which deploys the reports and sets the dataSource credentails . All works well ( Although we're using SQL Auth to our Az SQL DB sources).  

Now I also have a fabric Lakehouse, the lake house has data and the data is used in some reports along with some data from our Az SQL DB's 

I want to be able to update the credentials for both the Lakehouse dataSource & the Az SQL DataSource to use Service Principle Auth. 

This all works if I do it via the GUI in the power Bi Service but I'd like to be able to do this via a PowerShell script as part of the deployment pipeline.
I've tried the steps in this script and the take over works but not the update on credentials. 
https://github.com/SQLSwimmer/powerbi-admin-scripts/blob/main/TakeOverDatasetAndAssignSPToGatewayDataSource.ps1 
(Thanks SQLSwimmer) 

Any ideas welcome. 

The Nick

If the post helps please give a thumbs up || If it solves your issue, please accept it as the solution to help the other members find it more quickly.

If it's the biggest heap of stinky smelly stuff then I'm sorry .

Need Help

3 Replies

  • NickA01's avatar
    NickA01
    Icon for Resolver III rankResolver III
    2 years ago

    lbendlin  My bad, sorry.  

    Here's by POSH (lots of comments)  but thanks to your prompt, I've noticed it was duplicating the gateway ID. 

    I was getting a body empty error but now I've removed the duplication on the GatewayID, I'm getting a 404 NOT FOUND ERROR  which is progress. 🙂

     



    #Workspace ID
    $GroupId = "xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx"

    #DataSet
    $DataSetId ="yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyy"

    $TakeOverUrl = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/datasets/$DatasetId/Default.TakeOver"
    Invoke-PowerBIRestMethod -Url $TakeOverUrl -Method Post -Verbose
    ########## Above is working #####################


    # Now get all the datasources for the dataSet & GatewayID
    # Get all sources for dataset
    $datasources = Get-PowerBIDatasource -WorkspaceId $GroupId -DatasetId $DataSetId
    $datasources | Format-Table

    $GatewayId =$datasources.GatewayId
    ## Hardcode datasourceID's for test --> need to loop ######
    $GatewayDataSourceId1 = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaa" # populate this from the GetGatewayDatasources.ps1
    $GatewayDataSourceId2 = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbb" # populate this from the GetGatewayDatasources.ps1

    $SetCreds = "https://api.powerbi.com/v1.0/myorg/groups/$GroupId/datasets/$DatasetId/Default.BindToGateway"

    $Body = "{'gatewayObjectId': '$GatewayId', 'datasourceObjectIds': ['$GatewayDataSourceId1', '$GatewayDataSourceId2']}"

    Write-Host $SetCreds

    Write-Host $Body

    Invoke-PowerBIRestMethod -Url $SetCreds -Method Patch -Body $Body -Verbose
    Resolve-PowerBIError

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      What happens when you run that call from the sandbox?