Issue with SSRS 2005 while migrating to New server and its Resolution

We had an issue where reporting services was working fine but reports are not online..we tried connecting the reporting services and database engine using SSMS but no issues.. haven't seen any kind of login failure/any clue of what could have caused this issue in reports log files..

Current setup of reporting server(Server A):

1. OS: windows 2003 R2
2. Reporting Services/SQL Server: SQL Server 2005 SP3

It seems to be like .Net framework issue/asp.net issue..so they tried to uninstall the .Net framework but IIS also needs to be uninstalled..hence we recommended below steps..

1.       Uninstall IIS v6.0  - SA

2.       Uninstall .Net framework 2.0.50727 and other .Net Framework, then reboot box - SA

3.       Reinstall .Net Framework 2.0.50727 and IIS v6.0 - SA

4.       Configure SQL web-based reporting in IIS v6.0- DBA

SA had some troubles uninstalling the .Ne framework and as usual SA screwed up the server which results in reporting services not coming online..finally we have to rebuilt the server to migrate the reports to the new server..{{ business team is jumping on as usual as they want to look at the month end reports }}

IMPORTANT: Always make sure to backup Encryption key in reporting services before migration..

In my case we forget to backup this key before uninstalling .Net framework and after screwed up uninstallation, reporting services was not coming online, so there is no way we can backup encryption key using RSKeyMgmt or Reporting services config. File name will be extension snk.

we performed the below steps to bring the reporting services back online..

1. Built a new server named Server B with windows 2008 R2 SP1
2. if you install IIS on windows 2008 R2, by default it install IIS 7.5 version.. There is option to enable the IIS 6.0 compatibility to make sql server 2005 RS works fine. sql server 2005 reporting services doesn't recognize IIS 7.5.

Here is the link to install IIS 6.0 compatibility and register ASP.NET in IIS

http://geekswithblogs.net/jimhlavin/archive/2008/04/01/installing-sql-server-2005-reporting-services-on-windows-server-2008.aspx

3. Install sql server 2005 SP1 database engine and reporting services on Server B. Make sure to select install reporting services with no default settings..because this reporting services can be configured later using RS config.. My company uses only default settings, so it doesn't matter..if you go with configure default settings, reportserver and reportservertempdb will be created in sql server 2005.

4. Once installation is successful, go with configuring the Reporting services..
Follow this link to do.. also make sure to take snapshot of your RSconfigs in Server B and enter the data accordingly..

http://www.mssqltips.com/tip.asp?tip=1453

5. Once you configure this at the step Database setup, click on New that way reportserver and reportservertempdb database will be created in sql server 2005

6. Make sure to take the backup of this freshly created reportserver and reportservertempdb, which may be needed if we want to rever to fresh configs.. you can create fresh db using Database setup also..

7. Once you configure all settings now do initialization in RSConfig for Server B that will create entry in ReportServer.Keys table with ServerB as machine name..backup this row..

you will see application pools, reportserver, reports default website also created in IIS. IIS/ASP.NET should use only NetworkService account.. connection to reporting services/db can use sql server domain account..

8. Stop the reporting services on Server B

9. Now restore the backed up Server A - reportserver and reportservertempdb(optional) on Server B with replace option that way all reports and key of the old servers will be restored..

10. Delete the row entry for old machine name(Server A) in reportserce.Keys table

9. Now insert the backed up rows from step 7 in reportserver.keys table

10. Start the reporting services and initialization will show only one entry..

11. Open IIS management console 6.0, then browse reports you can see all reports but if you run,  it will fail with error saying either to restore the backed up encryption key or delete the encryption contents..

12. we don't have encryption key available, so we have deleted the contents of encryption key using RSConfig tool. This will delete only connection string/credentials declared in data source(data source name won't be deleted) or any subscriptions, reports(rss), permissions, models won't be affected at all..that's good..

13. Update all data sources with appropriate connection string/credentials and you are all set..

Now all the reports were fine without any issues. :)

In case if you have latest encryption key backup(snk file) available, then restore it in step-12, then you don't have edit any data sources..