SharePoint 2010 Access Services Reporting with SQL 2012

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=14.0.0.0, 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.

8 thoughts on “SharePoint 2010 Access Services Reporting with SQL 2012”

  1. Hi Mike,

    Great post, maybe you can help me with this problem. We cannot publish an access database to SharePoint server 2010 as we get this error “An error occurred while initializing access services database.” whenever we try to publish the default contact database. By the way, we are using the single server environment (standalone). If you need more details just let me know. Any help would be appreciated, thanks.

  2. I was able to register my custom extension, but ReportDesigner can”t see it. Any ideas where that should be registered? I do see it in BIDS and if I use the create shared datasource in SharePoint.

    1. As a follow up, I just had to copy the dll and .config to the 14/WebServices/Reporting/Bin directory as was necessary for Native mode.

      Thanks for the post!

      1. @BobC,
        Do you mean you can get your own custom data extension in report builder? By saying this I don”t mean running an existing report from some server. Report Builder is able to do that by delegating the report execution to the server (that can be configured with custom data extension).
        I mean: are you sure you can create a report using a custom data extension from scratch within report builder?

        Unless I missed something, Report Builder does not support Custom Data Sources… It seems that you posted some workaround on the following connect ticket:
        http://connect.microsoft.com/SQLServer/feedback/details/525749/ssrs-report-builder-does-not-supports-custom-data-source-extensions-organization-id-1277145
        I doubt however it has anything to do with the matter…
        Can you possibly clarify exactly what you are doing ?

  3. How about for SharePoint 2013? Should 14.0 be replaced with 15.0 for both powershell and policy configuration?

  4. Hi! I’m trying to migrate a RDC Extension from native mode to integrated mode. I pasted the dll in correct the bin folder and execute New-SPRSExtension from power shell. After that, I reset the services but SSRS doesn’t use the DLL that I pasted. What am I forgetting? Thank you a lot

Leave a Reply