Tuesday, October 18, 2011

Essential Business Server Forefront Fails to Create Reports

The Problem
Error when trying to generate TMG Report
So as part of a need to start seeing who was sucking down my bandwidth than I decided I would take a look into the reporting that is included with Windows Essential Business Server 2008(EBS) on the Security Server which is Forefront Threat Management Gateway. I opened the Forefront TMG Console and after creating a report I then told it to "Generate and View Selected Report" This resulted in the error to the right popping up.

The report Web Usage could not be generated....

After trying various other variations of the report thinking that maybe there was some problematic data it was still giving me issues. At this point I decided to dig a little deeper into the error.

Error when browsing to site. (Click for Larger)
It was giving me a 500. That wasn't really specific so after some additional digging on the net for troubleshooting they recommended opening the URL in IE. and I should get the Report Server interface. Instead of that I got this screen. This was good because it told me it was a 500.0 error as well as gave me the following more info to use in my searching:

HTTP Error 500.0 - Internal Server Error
The page cannot be displayed because an internal server error has occurred.

Detailed Error Information
Module: IsapiModule
Notification: ExecuteRequestHandler
Handler:  AboMapperCustom-2299220
Error Code: 0x800700c1
Requested URL:$ISARS
Physical Path: C:\Program Files(X86)\Microsoft SQL Server\ MSSQL.3\Reporting Services\ReportServer
Logon Method: Neogitate
Logon User: (My Username)

I then started looking around this path and stumbled on a post about Coldfusion running on IIS7 and this error being created almost identically. The problem for that post was that Coldfusion was running as 32bit and IIS7 was 64bit. The Solution was to run a script that would enable 32bit web apps.

Well just glancing at the task manager I could see IIS was running 64bit and that the reporting server was running as 32bit. I also remembered that for the installation of an update that the EBS Blog had me enable and then disable this particular flag Enable32bitAppOnWin64. After hunting I found that entry http://blogs.technet.com/b/essentialbusinessserver/archive/2009/05/06/sql-2005-sp-3-kb-955706-fails-to-install-on-the-ebs-management-server.aspx

I decided to try this solution.

The Solution
Open an Command Prompt with elevated privledges on the security server and run the below script:
cscript %SystemDrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1

After this I restarted the IIS Service. After this when I browsed to the web page it password prompted me. I knew that was a good sign. I then Jumped into the Forefront Report and pulled up my report. This time it worked without an error.

So my problem was either that that flag was set to disable since the beginning(never used the reporting server till now so not sure) or in following their blog entry to install the update for SQL Server it asks to set that flag to disable. They could have meant to just do that on the Management server but since their blog posts are a tad vague and the update was to apply to both so no was y of knowing if it was my goof or theirs.

Either way reporting is working now after hours of time on it. Now on to the next problem.