In Power BI Service, to set up the credentials for a data source in various environments such as development, testing, and production, you should adhere to the following best practices:
1. Use Parameterized Data Source Connections
When working in Power Query, create a parameter (EnvironmentURL) to hold the URL or server name of the data source.
Modify the queries to make dynamic use of this parameter
Source = Sql.Database(EnvironmentURL, "DatabaseName")
With Power BI Service, the parameter values of each environment can be updated without report modification.
2. Configure Gateway and Credentials for Each Environment
When connecting to on-premise databases, create multiple gateways or data sources in the On-Premises Data Gateway for each environment.
In Dataset Settings → Gateway Connection, each dataset should be mapped to the appropriate environment.
Separate credentials must be maintained for each environment so that one environment's security is not compromised via another.
3. Automate Deployment and Credential Management
The use of Deployment Pipelines in Power BI Service allows the movement of reports from one environment to the next with different credentials.
If working with cloud-based sources (like Azure SQL and APIs), credentials should be stored in Azure Key Vault and retrieved dynamically in Power Query.
For OAuth flow-type sources, ensure refresh tokens are secured, and credentials are never hard-coded in reports.
