topic Re: Power bi report server password update in Report Server

Power bi report server password update

whereismydata — Tue, 11 Aug 2020 13:47:52 GMT

Hi,

I do have quite a lot dashboards using the same AD credentials. Due to the password policy the password needs to be changed from time to time.

Does anyone have an idea how to set the password for all datasources at once?

thank you

best

Re: Power bi report server password update

KBO — Tue, 11 Aug 2020 14:54:42 GMT

Hi whereismydata ,

may be you ask for a service user for the reports, there you can say that the password didn't expire :). It is also a Best Practise to use technical or service User :).

Best,

Kathrin

If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!

Re: Power bi report server password update

whereismydata — Tue, 11 Aug 2020 15:03:31 GMT

Hi KBO ,

yes, this would be indeed the best solution, but unfortunatelly this is (due to our policy) not possible.

Re: Power bi report server password update

KBO — Tue, 11 Aug 2020 15:06:53 GMT

Hi whereismydata ,

thats a weird policy .... another solution could be diffecult - but may be someone else come with a solution :).

Best,

Kathrin

If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!

Re: Power bi report server password update

d_gosbell — Wed, 12 Aug 2020 01:05:16 GMT

You would have to write a script to loop through all your data sources and update the password one by one. You should be able to do this with Powershell using the Report Services Powershell Tools (https://github.com/microsoft/ReportingServicesTools) or if you want to use another scripting language you could access the REST api directly (see https://app.swaggerhub.com/apis/microsoft-rs/PBIRS/2.0)

Re: Power bi report server password update

whereismydata — Wed, 12 Aug 2020 08:57:27 GMT

Hi d_gosbell ,

thanks for the hint with the api. looks doable.

thank you

Re: Power bi report server password update

whereismydata — Thu, 13 Aug 2020 10:53:13 GMT

Everyone having difficulties with the api approach => https://community.powerbi.com/t5/Report-Server/PBIRS-API-issue/m-p/1290394#M15524

Re: Power bi report server password update

whereismydata — Tue, 01 Sep 2020 09:45:50 GMT

Following up on this:

Although working with the API makes sense for some cases I found another solution.

1) I created a folder named 'Config'. In this I uploaded a blank report with only one datasource.

2) Every time the password needs to be changed, the password is only changed in this report.

3) Once the password is updated I run this script on the SQL db

/****** Update Password for ServiceUser ****** 1) Go to https://<yoururl>/reports/manage/catalogitem/datasources/Config/ServiceUser and change the password 2 Run script --> only updates password, if username changes uncomment section below ******/ USE [pbiReportServer] GO /* declare variables */ DECLARE username VARBINARY(max); DECLARE Anonymous VARBINARY(max); DECLARE @ItemIds table (ItemId uniqueidentifier) /* Select hashed username in ServiceUser report and set it to username */ SELECT username = Username FROM [pbiReportServer].[dbo].[DataModelDataSource] d LEFT JOIN CATALOG c ON d.ItemId = c.ItemID WHERE c.Name = 'ServiceUser' /* Select hashed password in ServiceUser report and set it to Anonymous */ SELECT Anonymous = Password FROM [pbiReportServer].[dbo].[DataModelDataSource] d LEFT JOIN CATALOG c ON d.ItemId = c.ItemID WHERE c.Name = 'ServiceUser' /* Select all items with selected username but ServiceUser Report */ INSERT INTO @ItemIds SELECT d.[ItemId] FROM [pbiReportServer].[dbo].[DataModelDataSource] d LEFT JOIN CATALOG c ON d.ItemId = c.ItemID WHERE c.Name not like 'ServiceUser' and d.Username = username /* Test before change */ --SELECT 'before change' as [Status], * FROM [pbiReportServer].[dbo].[DataModelDataSource] --WHERE ItemId in (SELECT * FROM @ItemIds) /* Update password for selected reports */ UPDATE [pbiReportServer].[dbo].[DataModelDataSource] SET Password = Anonymous WHERE ItemId in (SELECT * FROM @ItemIds) /****** END - Also update username - END *******/ /* Update password for selected reports */ --UPDATE [pbiReportServer].[dbo].[DataModelDataSource] --SET Username = username --WHERE ItemId in (SELECT * FROM @ItemIds) /****** END - Also update username - END *******/ /* Test after change */ --SELECT 'after change' as [Status], * FROM [pbiReportServer].[dbo].[DataModelDataSource] --WHERE ItemId in (SELECT * FROM @ItemIds)

It selects all items where the usernamehash equals the one from the blank report and then updates the password hash