How to update Credentials stored securely in the report server?

Question

How to update Credentials stored securely in the report server?

ebooklub

Hall of Fame

Points: 3905
More actions
March 28, 2017 at 9:58 am

#323253

Hi,
SSRS has 150+ data sources where "Credentials stored securely in the report server"
If password for Credentials modified , I would need manually update all 150+ data sources
Is any way to update password "stored securely in the report server" for all 150+ data sources using script (TSQL or powershell)?
Thank you
Alex

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

ebooklub - Tuesday, March 28, 2017 9:58 AM
Hi,
SSRS has 150+ data sources where "Credentials stored securely in the report server"
If password for Credentials modified , I would need manually update all 150+ data sources
Is any way to update password "stored securely in the report server" for all 150+ data sources using script (TSQL or powershell)?
Thank you
Alex

If you need to change the key because you lost the password to restore the exiting one and the report server is still running, you can change the key in Reporting Services Configuration Manager. On the encryption key page is where you can change the encryption key(and then back it up using a new password). The encrypted data will be decrypted and reencrypted so it can take awhile.

Sue

ebooklub Hall of Fame Points: 3905 More actions · Answer 2

Might be I am not clear

let say I created Data source as following

Data source name: Dummy1

Connection string: myserver,1499;inicial catalog =mydb1;uid=mydomain\ssrs_report_user1

Credentials stored securely in the report server
User name: mydomain\ssrs_report_user1
Password:*******
Use as Windows credentials when connecting to the data source

Assuming I changed password for mydomain\ssrs_report_user1 ....
I would need manually retype in data source

User name: mydomain\ssrs_report_user1
Password**********************

Question

can run TSQL or Powershell script to update values in Password ?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

You can update the properties using Powershell or even rs.exe. Not sure you can do it through t-sql - I would guess you could hack into the XML and possibly update it but it wouldn't be supported. Using the APIs is a supported approach - Report Manager uses that itself.
This is an example that uses Powershell - updates it for a Windows account:
PowerShell:Updating Password of SQL Reporting Service Datasource

Sue