Issue with Automating SQL Server Credentials for Power BI Semantic Model Deployment via Azure DevOps Pipeline

[gpnikhil]

Scenario:

1. I have pushed my Power BI project to an Azure DevOps repository. The repo contains:

   • .Reports folder
   • .SemanticModel folder, which includes the model.bim file with SQL server and database details.

2. I created a deployment pipeline to publish the dashboard to a QA workspace using this sample pipeline approach.

3. Before deployment, I added a step in the pipeline to replace the development SQL connections with QA connections using regex on the model.bim file.

4. After deployment, the report is successfully published, but the SQL credentials (username/password) are missing. I have to manually input them by editing the semantic model settings in app.powerbi.com.

---

Problem:
I want to automate passing the SQL credentials (username/password) during the Continuous Deployment (CD) pipeline process. Here's what I've tried so far:

---

Approach 1:

1. Deployed the dashboard with SQL server details replaced via regex, but without passing the credentials.
2. The deployment succeeded, but I still need to manually input the credentials after publishing.

Approach 2:

1. Deployed the dashboard without altering the SQL details.
2. Created a data connection using Power BI REST API v2 with the following endpoint:
   • https://api.powerbi.com/v2.0/myorg/me/gatewayClusterCloudDatasource
3. Attempted to bind the dataset ID to the semantic model post-deployment.
4. The credentials weren't passed correctly, and I couldn't automate this part.

---

Request:

• How can I pass the SQL credentials (username and password) during the deployment process to avoid manual intervention?

• Is it possible to include the credentials directly in the model.bim or definition.pbism file as part of the CD pipeline?

• Alternatively, is there a way to automate the binding of the new data connection during deployment, perhaps using the Power BI REST API?

---

Any help or guidance on this would be appreciated!

[DarrylHasieber]

If the connection already exists on the Gateway I have used the following to auto select the connection.
Get workspace using
$workspace = Get-PowerBIWorkspace -Name $workspaceName
Get datasets in workspace
$datasets = Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets" -Method Get | ConvertFrom-Json
Then for each dataset, takeover the dataset and then bind to gateway connection

foreach ( $dataset in $datasets.value )
Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.TakeOver" -Method POST
Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/Default.BindToGateway" -Body $body -Method POST | ConvertFrom-Json

As a final step I also refresh the dataset to make sure it processes and has data if a new dataset.
Invoke-PowerBIRestMethod -Url "groups/$($workspace.id)/datasets/$($datasetid)/refreshes" -Method POST