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.
Best Answer
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 ofUSE [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.aspxHere are a few more hints:
BEGIN TRAN
/COMMIT TRAN
and running it withsqlcmd -b
, etc.