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

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  March 28, 2017 at 11:41 am

  #1935341

  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

  March 28, 2017 at 12:32 pm

  #1935362

  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

  March 28, 2017 at 12:47 pm

  #1935366

  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