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

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!

Start by reviewing sample-ado-pipelines/DeployPBIP.yml, the model.bim and definition.pbism files, and the documented Power BI REST API endpoint for gatewayClusterCloudDatasource. Determine whether credentials can be represented in deployment files or must be configured through a supported post-deployment connection flow, then validate the documented behavior in a QA pipeline.