This is just a post to inform all the people out there who may be struggling to implement Sql Server Reporting Services 2005 (SSRS) in a SharePoint 2007 site using Forms Based Authentication (FBA), when configured in the new SharePoint Integrated Mode (SP2).
After months of trying to get this working and dealing with Microsoft and getting bounced back between the Sql and the SharePoint teams, we finally have an admission that this is a Microsoft bug on the SQL side. Believe me guys, this is not an easy thing to come by...
So, for the people who have been struggling on this issue like us, we suggest simply waiting for a while, as there is nothing you can really do about this currently. Microsoft has informed us that this is targeted for a Sql Server SP3, although they do not have a date for the SP3 release. In the meantime, we are targeting them to give us a hotfix that will hopefully get us over this major hurdle.
Here's a portion of the actual e-mail we got from MS (dated June 04, 2007)
Hi Michael, Rajiv –
After further research and consultation within my team, it turns out that this issue is related to a BUG in SQL Server 2005 reporting services and is currently scheduled to be fixed in the SQL Server 2005 Sp3. And at this point we don’t have any date for SQL Server 2005 Sp3.
If the issue is urgent and important for you and you would like to have a hotfix/QFE before the release of the service pack, please let me know. The final decision on whether a hotfix request will be accepted comes from our escalation team and product team – but if I clearly understand the ‘business Impact’ of this issue for you, I can convey your request to our Escalation and Product Team.
...... rest edited ...
Just wanted to get the word out to the SharePoint community. I am not sure if we will get the hotfix for our business use yet, but will update this item on any progress.
Perhaps if the SharePoint community complains a lot, this will get more exposure in Microsoft?
-- Rajiv
===================
Update as of June 25, 2007:
Microsoft has reported they have been able to reproduce this issue with FBA identities not working with SSRS in a local environment, and have fully understood the source of the issue. It turns out that a piece of code they have "wipes out" the forms identity cookie that is correctly being passed from SharePoint to the SQL engine, and therefore returns a "user not found" message, which then throws an exception within SharePoint.
They have started working on a fix for this currently, and will be promoting it to the public in the next cumulative build, release date still TBD.
(Good news for us though, is that we have been promised to be part of a test team for a "private build", to ensure this bug has been resolved to our satisfaction and won't have to wait for the SP3 release. Fantastic news!)
More updates as we get them.
- RC
===================
Update as of July 16, 2007:
MS gave us a "hotfix" (along with 6 other priority customers, they say - who probably reported the issue with SSRS and FBA like us) on July 12, 2007. A couple of things to note here:
i) The hotfix is for the file "SharePointRS.msi" (new file size: 37,458KB), which is a replacement for the SSRS Add-In for SharePoint 2007.
ii) When I specifically asked the MS rep for which file(s) within it were new, he told me that the only replacement was for : "Microsoft.ReportingServices.SharePoint.UI.WebParts.dll"
In any case, I had the opportunity to test this new hotfix this morning and the following are my findings:
i) The install can simply be made over the older Add-In install without un-installing. Seems to go smoothly. I would ensure, however, that all the web.configs on the web Server do have write-access.
ii) Though the MS rep claimed that the "Microsoft.ReportingServices.SharePoint.UI.WebParts.dll" was replaced, it seems as if the version on the dll was not updated, since the web.config still pointed to the version prior to the hotfix.
<SafeControl Assembly="Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.ReportingServices.SharePoint.UI.WebParts" TypeName="*" Safe="True" />
I thought this was interesting.
---
It turns out that the SSRS reports can only be deployed to a DEFAULT zone (not any extended zone in a SharePoint app). So I had to flip my Authentication Provider for my default zone in my SharePoint Application to my custom FBA provider, and was able to deploy the SSRS reports from the BI Studio 2005 (after getting a prompt for FBA credentials).
The reports deployed without issues, and I was able to pull them up in the FBA zone of my SharePoint application, which is great news. Do note, however, that if you have extended zones, reports will still only be accessible through the DEFAULT zone. This has not been addressed with this hotfix.
--
For all the other people out there trying to resolve this issue, the MS rep mentioned to me that this hotfix will be part of a "cumulative update" to SQL Server 2005, that will be released in August 2007, and you will not have to wait for a release of SP3! Also, according to the rep I talked with, all of the initial testers of the hotfixes (including us) have not reported any major issues with the fix issued.
I hope that this brings some closure to all those who have encountered a similar issue with SSRS and FBA.
Thanks,
Rajiv
===================