​One issue that I have had to deal with recently is upgrading SQL Server from 2005 to 2008 R2.  The actual server upgrade went very smoothly.  However, reporting services was more problematic.

The server was Windows Server 2003 with all of the service packages installed.  The website itself is a .NET 4.0 Web Application running on IIS version 6.0.

The server had SQL Reporting Services 2005 on it and the installation tool upgraded these to 2008 R2.  However, reporting services did not work!  I kept getting either 404 errors ("page not found") or 401 errors ("page could not be displayed").

Please note that all of these things that I tried were suggested by other sites as well as on the MSDN forums.

Since the site uses Transport Layer Security (TLS), I figured that configuring reporting services to run with Secure Sockets (SSL) would do the trick.  I added a user to the server, made it an Administrator, made it a member of all the SQL Server groups, and then used the SQL Server Management Studio to add it as a user to the databases, giving it every permission, including "RSExecRole".  I then used the SQL Server Reporting Services Configuration Manager to add this user as the account that uses and executes SSRS.

When opening a local web browser instance to the secure site, I got the windows login popup.  I entered in the account username and password for the Reporting Services user account above and the dialog would just close down and pop back open.  It did this three times and then displayed a blank page.  I figured maybe the browser security settings may be the culprit so I adjusted those.  It still didn't work.  I tried several different accounts: server administrator, site administrator, site user accounts with permissions to view reports.  No dice.

I thought that maybe the reporting services configuration file (C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config) needed some tweaking so I set the "SecureConnectionLevel" value to the highest possible setting of "3".  No luck.

I also tried setting the "ReportServerUrl" value to the secure server url.  Still no luck.

I opened Regedit and edited the "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa" key by adding the "DisableLoopbackCheck" value and setting it to 1. It didn't work!

I wanted to start from a position of having SSRS working and so I briefly disabled all site security.  I then edited the config file to remove my changes, and ran the SSRS configuration manager to remove all secure sockets and to use the NETWORK SERVICE to run SSRS.  I used SQL Server Management tool to give NETWORK SERVICE all permissions for the report database and the site database.  No luck!! 

I kept editing the web.config files and re-deploying the web application after the urls would change.  I kept resetting IIS and stopping/starting the reporting services in-between tweaks.  I even rebooted the server a few times.

I re-secured the site and then did the following, which ended up working.  I downloaded all of the report definition files (.rdl) from our live/production 2005 SQL Server Report Server (running on another machine) and noted the structure of the setup on the production reporting services site.  That is, I noted the folder structure and which folders contained which structures necessary for SSRS.  Then I completely uninstalled reporting services from our staging server and then rebooted the server for good measure.  Then I re-installed Reporting Services from the SQL 2008 R2 install disc and then configured it normally using SSL.  

It ran fine!  No problems at all.   

I re-created the folder structure from the production version of the site and uploaded the report definition language files.  I edited the web.config files with the secure urls and then re-deployed the website.  Finally, I rebooted the server (which was probably overkill).

Everything ran fine.  The reports showed up with no trouble and everything is as secure as TLS will allow.

Here's the process for upgrading SQL Server Reporting Services from 2005 to 2008 R2 that I recommend (if you can possibly swing it):

1. 1. Before upgrading anything, download all of your report definition files from the existing report server.
2. 2. Note the folder/file structure of the existing server, as you will have to re-create it.
3. 3. Upgrade SQL Server 2005 to SQL Server 2008 R2 (you can do an in-place upgrade).
4. 4. Uninstall reporting services completely and restart the server for good measure if you can.  If not, then try stopping and starting all SQL-related services and running "iisreset" from a command line.
5. 5. Re-install reporting services using the SQL Server 2008 R2 disc.
6. 6. Configure reporting services as desired using the reporting services configuration manager.
7. 7. Running IE locally, open up the report server UI in the browser and reproduce the directory/file structure noted in in step 2 above.
8. 8. Upload the RDL files to the locations noted in step 2 above.
9. 9. Reboot the server if possible.  If not, then run "iisreset" from a command line and stop and start the report service from the reporting services configuration manager.
10. 10. Congratulations, you just upgraded SSRS the easy way.

Your upgrading experience may run smoother than mine did.  However, I think that, if it's at all feasible, I'm going to upgrade SSRS using the above procedure from now on.