Recently I wrote a short blog post about using SQL Server 2012 with SharePoint 2010 where I pointed out some resources to help users get started with SQL 2012 and SharePoint. From that blog post I received a question about configuring SharePoint 2010 Access Services with SQL 2012 Reporting Services. Previously I wrote an article called Configuring Access Services with SharePoint 2010 that outlined how to get SQL Server Reporting Services from SQL Server 2008 R2 to work with SharePoint 2010 Access Services. Getting SQL Server Reporting Services (SSRS) from SQL Server 2012 working with SharePoint 2010 Access Services is slightly different.
A major change in SSRS 2012 is that it is installed as a SharePoint Service. Once you have installed SSRS in your SharePoint 2010 farm you will need to make a few small updates in order for it to work with SharePoint 2010 Access Services.
The first update is to enable an ADS extension. In previous versions of SSRS you would modify the rsreportserver.config file and add an XML node. In SSRS 2012 you run the following PowerShell commands from the SharePoint 2010 Management Shell. These commands must be run on the server in your SharePoint farm that is running the SQL Reporting Services SharePoint service. Depending on your server configuration, you may be required to right click on the SharePoint 2010 Management Shell shortcut and choose “Run As Administrator”.
$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=220.127.116.11, Culture=Neutral, PublicKeyToken=71e9bce111e9429c"
If you do not run the PowerShell script you will see an error similar to the one below when trying to view a Access Services 2010 report:
“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)”
After the ADS extension has been enabled you will need to modify the rssrvpolicy.config file on the SharePoint Server where the SSRS service is installed. You can find the policy file located in the 14 hive: ..14/WebServices/Reporting directory. The specific modifications can be found towards the bottom of this TechNet article. Please note that the rssrvpolicy.config file is in a different location for SQL Server 2010 Reporting Services.
If you do not make the proper changes to the rssrvpolicy.config file you will receive a security error when trying to run reports from Access Services 2010.
Once you have made all of the changes listed above your Access Services reports should work as expected.