Reporting server 2012 configuration for SharePoint 2010

This article describes the problems I faced when migrating a SharePoint 2010 environment with SQL Server 2008 R2 Reporting server to an SQL Server 2012 Reporting server.

First thing to do after the installation of the SQL Server 2012 is install the SPRS service into SharePoint. This is perfectly described in the following technical article

http://msdn.microsoft.com/en-us/library/gg492276.aspx

Important to note is that the new Reporting module in SharePoint is converted to a Service application. So after you run the PowerShell commands you also need to create a Service Application for the reporting service. This is also perfectly described in the following article

http://msdn.microsoft.com/en-us/library/gg492276.aspx

However after upgrading we needed to change the software and SharePoint configuration a bit. Here below the changes that we needed to do.

Web.config from SharePoint Webservices seems to be reset

It looks like the web.config of the SharePoint Webservices is reset. All our previously registered membership providers and role providers are removed. Strange thing is that the connectionsstrings section still contains the connections (preserved). Also the appsettings section is cleared. I observed this behaviour on two different servers and needed to reconfigure.

SharePoint still uses on some locations the old reporting web part that fails

On some pages (especially custom site pages) where we used the reporting web part from version 2008 R2 we get the following exception (sorry english people, the error is in dutch because the SharePoint environment is dutch, and yes I am also anoyed that they try to translate errors to Dutch).

Fout in webonderdeel:Een webonderdeel of besturingselement voor een webformulier op deze pagina kan niet worden weergegeven of geïmporteerd. Het type Microsoft.ReportingServices.SharePoint.UI.WebParts.ReportViewerWebPart, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 is niet gevonden of het niet is geregistreerd als veilig. Solution

It tells us that the old web component (10.0.0.0) is not found. Our solution was to replace these webparts by hand with the new Reporting component (11.0.0.0). We could not edit the page in SharePoint Designer because of the error here above so we needed to change all pages by using the Edit Page option within the SharePoint website.

The solution was to remove the webpart from the page and add the new reporting webpart and reconfigure.

Webservices changed

Some reports we retrieve by using the webservices from reporting service did not work anymore.

Before Reporting Server 2012 you needed to run a Reporting service in SharePoint integration mode to get reporting working. Reporting server 2012 however integrates fully with SharePoint and has its own SharePoint application. This means that the extra Reporting service is not needed anymore.

In older versions of Reporting server when we connected to de webservices we directly connected to the reporting service url provided by the reporting configuration. In the new version this does not exist and you have an new address where you need to connect to for the webservices. This address is within the SharePoint and can be found at

/_vti_bin/ReportServer/ReportExecution2005.asmx (or watever webservice you are using)

Also note that the ReportExecution2005.asmx is obsolote. The service is still working but you should replace it by the ReportService2010.asmx that has the same functionality.

An other thing to note is that when you use forms authentication within your SharePoint web the anonymous user is used for login in to the datasources that the reportin service uses (including its own reporting database). Because it is not fully clear to us which database rights this user needed we turned on impersonation on the website and used the app pool identity as authentication for forms authentication. This solved our problem because this was a user that was known by the reporting server (IUSR or Anonymous user is not).

Advantages of SQL Server 2012 in combination with SharePoint 2010

Of course there are also advantages when using SQL Server 2012. Here a few that I noticed

Performance reports in SharePoint

The reports that are running in SharePoint feel much faster, I do not know yet if it is just my gut feeling but they respond faster. When I also hear this from end users I will add this to this blog.

Integration SharePoint is better

Configurating Reporting server for SharePoint is in 2012 much easier. You need less configuration to set up a reporting service and installation is easier within SharePoint. Also the reporting services are not needed anymore. It is however tricky when you do a migration because a lot is changed.