Post reply

Losing Datasource credentials when restoring reports server database from backup

  • May 5, 2015 at 3:14 pm

    #296702

    I have a server that is running SQL Server 2008 R2 SP3 with a ReportServer Database containing about 400 Reports.

    I am in the process of trying to migrate the SQL Server Database and Report Server to another machine but cannot seem to get my Report Server Reports to retain the Encrypted data.

    after running through a list of pertinent posts from a variety of forums I have not been able to find any solutions that resolve my problem.

    I have restored the ReportServer database on the new machine and ensured all the logins and passwords have been duplicated properly.

    After restoring the database and trying to access the URL I get the message 'Report Server not Initialized'.

    This has been documented in a variety of posts with the suggestion to delete and restore the encrypted content using an encryption key file.

    After doing this, I am able to see the all the ReportServer reports from the URL and Report Builder.

    However, even after restoring the encryption key, the Datasource credentials (username/password) are still not restored to my reports.

    When I edit the reports in Report Builder, the Datasource credentials are set to 'Prompt For Credentials'.

    As a result, all of the reports fail when run from my application.

    I desperately need a resolve to this issue as otherwise I will have to manually edit all 400 reports to re-establish the credentials.

    Any and all suggestions are greatly appreciated.

    Thanks,

    Leonard

  • May 6, 2015 at 2:31 pm

    #1796063

    I discovered what I was doing wrong. Through all my trial and error I guess I managed to get it in my head that I had to 'Delete Encrypted Content' prior to restoring the backup encryption key. Of course, deleting the encrypted content did just that. So the username/passwords no longer existed in the reports. Restoring the encryption key does not restore this information to the reports.

    I finally started from scratch again and just restored the reports server database and reportservertempdb, connected to the restored database using the Reporting Services Configuration Manager and then restored the encryption key.

    All worked fine when I followed these steps.

Viewing 2 posts - 1 through 1 (of 1 total)

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