Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
whereismydata
Resolver IV
Resolver IV

Power bi report server password update

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

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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)

View solution in original post

7 REPLIES 7
whereismydata
Resolver IV
Resolver IV

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

d_gosbell
Super User
Super User

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)

Hi @d_gosbell ,

 

thanks for the hint with the api. looks doable.

 

thank you

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

KBO
MVP

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!

Hi @KBO ,

 

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

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.