Manage password change for multiple reports

[MarkJZ]

I have recently begun publishing Power BI reports to my organization and have run into what is going to be a major roadblock. Our reports pull from an onprem SQL server using the data gateway and are set in the service to refresh regularly.
I am required to regularly change my password. When this happens, I need to open each report in desktop, update credentials and republish them to the service.
Very soon I could have over 100 of these reports and getting everything back online is going to get very difficult.
I suppose we can create an account with minimal access only to the SQL server but if there is another way to update credentials I would like to try.

[v-kkf-msft · Accepted solution]

Hi @MarkJZ ,

Based on my testing, we can modify the gateway credentials directly without re-binding the gateway and datasource for these datasets.

In addition to directly modifying the gateway credentials as @SpartaBI described, you can also call the Gateways - Update Datasource API  to programmatically modify multiple data source credentials at the same time.

For more information, please refer to:

Configure credentials programmatically for Power BI 
How to update credentials for an on-prem Power BI data source using PowerShell 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpartaBI]

Hi @MarkJZ,
The credential used to refresh the data while developing the model in Power BI Desktop are not being used to refresh the dataset once it was published to the Power BI Service. There is no reason to republish the PBIX files.
In your case, the credentials that are being used in the Power BI Service are stored in the data gateway itself.

You should create a single datasource in your gateway that will connect to the SQL server, and use it for all datasets. This way, you will only have a single place to update the password.
Note that in order to connect to a single gateway datasource, the connection strings need to be identical. So if you use different SQL databases on the same server, you should connect to the server without specifying a database.

Actually, If you'll use a single connection string, you will not need to update the password more than once in Power BI Desktop for local development (It is saved in your windows user profile).

Regardless, I would recommend creating a dedicated user for the development and service refreshes of the Power BI Datasets.