Why does my Microsoft SQL Server Reporting Services 2005 Report Server's Report Manager tell me “Login failed for user” (rsRPCError) after Windows Update to SP2?

Question

I updated an existing SQL Server 2005 Reporting Services instance to SP2 via Windows Update. Now when I hit my Report Manager, I get the message “Login failed for user 'Report Server (Sandbox)'. (rsRPCError)”.

“Report Server (Sandbox)” is a SQL Server login that my Report Server uses to connect to its housekeeping database (which is also named “Report Server (Sandbox)”). Reporting Services could connect successfully before. That account’s password has not changed; I can connect successfully with its name and password via e.g. sqlcmd.

When I run the Reporting Services Configuration Manager and check the Database Connection page (on Database Setup), things look okay. The Database Version reported is C.0.8.40. I have tried fiddling with the credentials to encourage them to “reset” somehow. This doesn’t seem to help, but the tool keeps prompting me to upgrade my database. (I keep telling it to go ahead.)

What gives? Do I need to completely wipe and reinstall this Report Server (or at least its database)? I guess that wouldn’t be such a horrible thing – it’s broken currently anyway, and naturally all our report definitions are organized and source-controlled for easy reconstruction/redeployment. Still, I dread hassling with all that stuff again.

Answer 1

Ultimately this turned out to be a database version mismatch, not at all an authentication/authorization problem as the “failed login” message indicates.

I found that there is a bug in the database-version upgrader: it emits scripts that do not properly quote database names, so for database names like mine which tend to have lots of spaces etc. it emits statements like USE Report Server (Sandbox)TempDB (instead of USE [Report Server (Sandbox)TempDB]), and the upgrade fails. The resolution is to have the tool emit the upgrade script, manually fix it up, and apply it yourself. See http://www.eggheadcafe.com/software/aspnet/30860451/database-problem.aspx

Here are a few more hints:

• When the Configuration Manager gives you “messages” at the bottom of a page, you can click on a message to see more details. (There is nothing to clue you in to this fact; it took me quite a while to realize that the tool was willing to explain to me that the database was out of date, if only I magically clicked on something that doesn’t invite clicking.)
• When you’re asking it to generate an upgrade script for you (click the Script… button, then click “Upgrade Database Script” at the left) it prompts you for “Server Version”. This is actually asking for the version of SQL Server Reporting Services that corresponds to the database version you are trying to upgrade from, which is an awkward thing to come up with. It seems that database version C.0.8.40 corresponds to Reporting Services 2005, while database version C.0.8.43 corresponds to Reporting Services 2005 SP1. (C.0.8.54 corresponds to Reporting Services 2005 SP2.)
• The generated script seems to be reasonably robust against partially-applied updates, but on the other hand it will happily partially update your database version. So you might do well to take precautions like beginning and committing trivial marked (named) transactions in the housekeeping DBs before you run the update script (for easier point-in-time restore should something go pear-shaped), wrapping the entire script in a BEGIN TRAN/COMMIT TRAN and running it with sqlcmd -b, etc.