Parametrize a connection for pipeline deployments

[AmiraBedh]

I am using 2 parameters to connect to my SQL Server database :

• Database
• Server

Each param contains a list of databases names and servers links to 3 different stages : DEV, TST and PROD:

Here is an example :

 

so in the 1st step I create the query and then in the source step :

= OleDb.DataSource("provider=SQLNCLI11.1;initial catalog="&Database&";data source=

"&Server&";trust server certificate=True", [Query=qry])

I am using a deployment pipeline with rules on the semantic model to switch from a stage to another.

The problem that in each stage we have a specific user and password when connecting to the database.

I found my self connecting to DEV and publish to the DEV workspace and then I need to go back to Power Query and when I change the parameters to make them point to the TST I get an error saying that the current user doesnt' have access to the database.

Here is how we are configuring our data source connection :

[amitchandak]

@AmiraBedh , Based on what I got

Once you migrate and edit the parameter, it should ask for credentials. If not, you can go to the data source settings and update the credentials there

[AmiraBedh]

This is what I get in the credentials :

[AmiraBedh · Accepted solution]

I found it in the documentation :

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

If authentication fails, make sure you selected the correct authentication method and used an account with database access. In test environments, you might use Database authentication with an explicit username and password. In production environments, you typically use Windows authentication. For more assistance, see Troubleshoot refresh scenarios, or contact your database administrator.