SQL 2008 R2 - Reporting Services, using Data Model with Report Builder 3 over internet causes crash, any ideas?

Background:

• I have a Windows 2008 R2 box set up with SQL 2008 R2 both the Data Engine and Reporting Services.
• I have configured Reporting Services to use custom authentication (FormsAuthentication) that I wrote.
• The custom authentication gets passed the name of a user to treat as the admininistrator assuming they login correctly in the Reporting Services configuration files.
• The custom authentication when queried by Reporting Services about the current users permissions will always return true when logged in as the user configured as the administrator.
• I have uploaded a Data Model to Reporting Services (using the built in Report Manager app) which uses a Data Source I added (also using built in Report Manager app) which connects to a database on the same box.
• I have a ASP.NET MVC3 web app (also on same box) that is configured to use the Reporting Services web service to do things like list existing reports, run existing reports and a link to start Report Builder 3.
• The ASP.NET MVC3 web app shares it user logins with Reporting Services, i.e. the custom authentication used by Reporting Services verifies user details by looking at the same data as the web app.
• The ASP.NET MVC3 web app is available externally.

Problem:

• If I log into the web app remotely, start Report Builder 3 via link, login as UserA, use the Report Wizard with options >> Create dataset >> select Data Model (see above) as the source of data >> choose 1 table of data (e.g. Organisations) >> click button to preview data >> click next -- BANG - REPORT BUILDER 3 HANGS.
• If I log into the web app locally on the server hosting everything, start Report Builder 3 via link, login as UserA, use the Report Wizard with options >> Create dataset >> select Data Model (see above) as the source of data >> choose 1 table of data (e.g. Organisations) >> click button to preview data >> click next >> choose row/column groups and values, report style and click finish. I can then run the report and save it. -- EVERYTHING WORKS!

Things I looked开发者_运维问答 at:

• If I do a SQL Profiler Trace against the both the Reporting Services database and the database that the Data Model is using in the case where it hangs it appears Reporting Services is getting into some kind of loop continuously asking the Reporting Services databases if there are any running jobs. When it works it never asks the Reporting Services about Running jobs at this point.
• If I use the Data Source the Data Model uses in Report Builder 3 as the data source then it does work. HOWEVER this then prompts for a SQL Server login and requires the you open the SQL Server port on both the server and the remote machine! Not good.

This is driving me crazy. Any body with elite skills in the black magic of using Reporting Services 2008 R2 and Report Builder 3 that can help be figure this out will be deserving of everying computing award going.

EDIT: I found this while Googling again (Forum post, Google Cache) and got really excited but couldn't quite make sense of what the poster was saying and changes I made trying to follow it just broke all Reporting Services access so I rolled back the changes. Unfortunately the forum is archieved (and a bit rubbish) so I can't contact or leave a message for the poster.

---

Peter, my experience with Reporting Services is extremely limited; I've been building reports for SQL Azure Reporting Services using BIDS.

I had a problem whereby BIDS was crashing when I attempted to preview a report and it took me hours to work out that my reports weren't checked out of version control and BIDS couldn't save them. Rather than giving me any kind of meaningful error message, Visual Studio simply crashed on me.

Maybe, just maybe, your issue is that Report Builder can't save the report and is crashing for a similar reason. However, from your description, I'd suggest that if it is the case, it's likely to be a permissions issue rather than a read-only one. I'd check that the security context for the access of data is as you expect when logging in remotely.

Sorry that I can't give you a definitive answer, but maybe it will suggest somewhere new to look.

---

It turns out that this is caused by a school boy error by Microsoft of using one connection and not opening/closing it appropriately. MS were able to give us this workaround when we called them:

Basically after you've got the Report Builder 3 clickOnce app installed, locate it on your computer and then inside the system.net tag of the app.config file add the tags below.

<connectionManagement>
      <add address="*" maxconnection="1024"/>
</connectionManagement>