SharePoint 2010 Access Services Reporting with SQL Server 2012

I ran into this today at a customer, where they had SharePoint 2010 with PowerPivot/Analysis Services and Reporting Services installed on SQL Server 2012.  They were trying to view an Access Services Report, which takes advantage of the SharePoint Reporting Services integration – this was throwing an error in the UI.  However, as they were on SQL 2012, the advice from all the online articles about how to set this up wasn’t working as anticipated, as all those articles are based on SQL 2008 / R2. 

The issues the client was seeing in their Access Services Report was as follows.  (This was found in the ULS logs)

“An error has occurred during report processing. (rsProcessingAborted) An attempt has been made to use a data extension ‘ADS’ that is either not registered for this report server or is not supported in this edition of Reporting Services. (rsDataExtensionNotFound)”

This article is the most logical starting point, however, it only describes how to handle it for SQL 2008 and 2008 R2, not 2012.

http://technet.microsoft.com/en-us/library/ee662542.aspx

Where you need to look for SQL 2012 is the following blog post.

http://blog.mikehacker.net/2012/05/16/sharepoint-2010-access-services-reporting-with-sql-2012/

In there, the author identifies the following PowerShell which must be run (on the SP server with SQL 2012 installed on it).

$apps = Get-SPRSServiceApplication
foreach ($app in $apps)
{
   New-SPRSExtension -identity $app -ExtensionType “Data” -name “ADS” –TypeName “Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=71e9bce111e9429c”
}

They also refer to the technet article (above) and say the very vague “the specific modifications can be found towards the bottom of this TechNet article”.  What I ended up doing was adding the following to the end of the rsreportserver.config file (note, one article says rsreportserver.config, the other says rssrvpolicy.config, in my case, I had files named “rsreportserver”).

<Configuration>

…

  <MapTileServerConfiguration>
    <MaxConnections>2</MaxConnections>
    <Timeout>10</Timeout>
    <AppID>(Default)</AppID>
    <CacheLevel>Default</CacheLevel>
  </MapTileServerConfiguration>
  <Data>
    <Extension Name=”ADS” Type=”Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=71e9bce111e9429c”/>
  </Data>
  <NamedPermissionSets>
    <PermissionSet class=”NamedPermissionSet” version=”1″ Name=”ReportExpressionsDefaultPermissionSet”>
      <IPermission class=”SecurityPermission” version=”1″ Flags=”Execution” />
      <IPermission class=”Microsoft.Office.Access.Server.Security.AccessServicesPermission, Microsoft.Office.Access.Server.Security, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” version=”1.0″ Flags=”CalculationCallback” />
    </PermissionSet>
  </NamedPermissionSets>
  <CodeGroup class=”UnionCodeGroup” version=”1″ PermissionSetName=”ReportExpressionsDefaultPermissionSet” Name=”Report_Expressions_Default_Permissions” Description=”This code group grants default permissions for code in report expressions and Code element. “>
  </CodeGroup>
</Configuration>

I had config files in both the 14 hive, and the 15 hive, and I ended up editing both files.  Afterwards, everything worked like a charm.