I've had a devil of a day diagnosing problems that practically piled faster than I found I could fix! Ok, enough of the silly alliteration...
For some time now I've been assembling an application in C# 2.0 that relies on RDLC documents (Report Definition Language - Client-side). I've had a very enjoyable time doing it (the details of which are forthcoming in a follow-up post). That aside, I ran into an issue today which had me completely baffled. All of the sudden today my project in VS.NET stopped compiling. It had been working flawlessly for weeks (even this morning at 4:00 AM it was working). When I opened the laptop on the plane it stopped working. VS.NET was reporting “An internal error occurred on the report server.” Lots of good that did me. Well, the error message led me to believe that there was an issue with my Sql Reporting Server installation.
Sure enough, when I started going down the path of diagnosing that, I saw that my http://localhost/ReportServer virtual directory would not come up for me, giving me an error along the lines that my permissions were “insufficient for performing this operation.” Inspecting the logs led me to believe that there was an error authenticating to the SQL reporting services databases. I thought, “alright, the Windows user account's password associated with the SQL Server service might have expired.” That turned out to be wrong. “Ok,” I mused, “Maybe I mistakenly installed a time-bombed version of SQL Server (something I've never done - but hey, I was grasping at straws here).” That, too, turned out to be wrong.
I went so far as resetting the user's password, connecting to the SQL Server database as that user via RunAs (which worked flawlessly). As it turns out, I figured it out on the last thing I tried (of course, I didn't need to try any further). :)
Log on to your local machine as your administrator user (because you're not running as an administrator user, of course), and browse to http://localhost/Reports. Grant your user account 'Browser' rights. Alright, that fixed my SQL Reporting services issue. Great. Go back to Visual Studio and recompile...BZZZZZZZZT! Same error! It turns out that I simply had never configured my user account with SQL Reporting Services permissions....
I was pretty irked by this time because most of my day was already shot diagnosing an issue that wasn't the root of the problem. I then decided to backtrack through what I had last done in the project by removing the last two reports from the application. When I did this, I found that I had some compile-time errors in my code. I fixed them, brought the reports back in and it worked great! At least I now know that if you're getting this strange “internal error” during a compile, chances are it's an error in your code and not the report server.
With all this mayhem, I then decided to go back and run my application (which relies on SQL Express User Instances) and lo and behold, I get a new error message “Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance.” Man! The fun just never ends! I suspect this is happening because I was futzing around with my SQL Server configuration, changing passwords, etc. Long story short, I found the solution while browsing around online.
SQL Express User Instances rely on content found in the “C:\Documents and Settings\[USER]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS” folder. Simply delete this folder (you'll have to shut down the SqlServer process first) and reboot your machine. SQL Express will then repopulate this folder with the appropriate Master, Model, TempDb, etc when the next User Instance database is requested. In fact, that's probably why SQL Express takes a little extra time to load on the first time for a given user - because it has to copy files into this folder at startup. This can lead to timeouts on first load. Subsequent loads, however, should go more smoothly.
So there were three issues resolved today:
PROBLEM: Permissions “insufficient for performing this operation”
FIX: Grant user 'Browser' permissions via http://SERVER/Reports virtual directory
PROBLEM: “An internal error occurred on the report server.”
FIX: Temporarily remove the .RDLC reports, recompile, fix compile errors, add reports back (or recreate them)
PROBLEM: “Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance.”
FIX: Delete “C:\Documents and Settings\[USER]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS”