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
