Background

Recently, I came across with a requirement to change our SSRS (Reporting Services) report data sources from SQL Authentication to Windows Authentication on one legacy server. This requirement came from compliance findings whereby we needed to phase out SQL logins and all applications along with services should be, but were not, using domain accounts (Windows authentication).

Problem

Report Manager is a report access and management tool that allows administration of reports and relevant objects. While going through the datasources of reports in order to change credentials, the SSRS datasource configuration page (as shown in image below) provides four options to choose from:

1. Credentials supplied by the user running the report - if this option is selected, user will be prompted for credentials, and the credentials that are entered will be used until the session ends.
2. Credentials stored securely in the report server - Credentials (either SQL or Windows login) can be stored securely on the report server. These credentials enable other users to run the report without providing credentials for the underlying data connection.
3. Windows integrated security - In this case, the user is not prompted to type a user name or password. The report server passes the authentication token to the server hosting the data source. The delegation/impersonation feature should be enabled. This is mostly valid when all services are hosted on one computer.
4. Credentials are not required - Self-explanatory, this option is available if tje data source doesn’t require any credentials. 

As our requirement was to replace the existing SQL account with a domain account, the second option was the most appropriate. While applying the domain accounts suddenly an error arose.

“The password is not valid. Please retype the password”,

This appeared whenever a domain account name was entered in user name field. I tried the other options to reset the error and allow me to test the connection but was repeatedly prompted for credentials with the same error even if the correct credentials are entered.

Workaround

While searching for different options for a quick workaround, there were two choices: 

1. Create a new report datasource with domain account credentials and link this new datasource to all dependent reports individually. 
2. Create a new report datasource with domain account credentials and link this new datasource using update in table (Datasource) that exist in report server database. 

There were hundreds of reports that were depended on the specific datasource in my circumstances, so I chose the  second option to update the datasource table. I noticed two tables (Catalog, Datasource) in the ReportServer_RS01 database (here RS01 is SSRS instance name). Querying the “catalog” table with different criteria for the “type” column, I found the “ItemID”, “Path” and type of objects as below, which means that this table holds information related to the different types of objects and associates them with an "ItemID".

• 1 = Folder
• 2 = Report
• 3 = Resources
• 5 = Data Source
• 8 = Shared Dataset

The datasource table also has “ItemID” column that can be joined with the Catalog table. The “Link” Column keeps a reference for the datasource, referenced in the Catalog table. A simple query gave me required information.

select DS.ItemID,C.Path, CC.Path [Datasource]
 from DataSource DS inner join Catalog C on DS.ItemID=C.ItemID
 left join  Catalog CC on DS.link=CC.ItemID

It was now easy to create a new data source and just update the reference of new datasource for all dependent reports using below.

Update datasource 
 Set link=<"ITEMID of New datasource"> 
 Where link=<"ITEMID of old datasource">

After updating the datasource reference, all the respective reports and dataset were using new datasourse. 

Reference

https://blogs.technet.microsoft.com/dbtechresource/2015/04/04/retrieve-ssrs-report-server-database-information/