{"id":834,"date":"2013-05-01T12:18:26","date_gmt":"2013-05-01T19:18:26","guid":{"rendered":"https:\/\/mmman.itgroove.net\/?p=834"},"modified":"2023-02-24T21:48:45","modified_gmt":"2023-02-24T21:48:45","slug":"sharepoint-2010-access-services-reporting-with-sql-server-2012","status":"publish","type":"post","link":"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/","title":{"rendered":"SharePoint 2010 Access Services Reporting with SQL Server 2012"},"content":{"rendered":"

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 \u2013 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\u2019t working as anticipated, as all those articles are based on SQL 2008 \/ R2.  <\/p>\n

The issues the client was seeing in their Access Services Report was as follows.  (This was found in the ULS logs)<\/p>\n

\u201cAn error has occurred during report processing. (rsProcessingAborted) An attempt has been made to use a data extension \u2018ADS\u2019 that is either not registered for this report server or is not supported in this edition of Reporting Services. (rsDataExtensionNotFound)\u201d<\/em><\/p>\n

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

http:\/\/technet.microsoft.com\/en-us\/library\/ee662542.aspx<\/a><\/p>\n

Where you need to look for SQL 2012 is the following blog post.<\/p>\n

http:\/\/blog.mikehacker.net\/2012\/05\/16\/sharepoint-2010-access-services-reporting-with-sql-2012\/<\/a><\/p>\n

In there, the author identifies the following PowerShell which must be run (on the SP server with SQL 2012 installed on it).<\/p>\n

$apps = Get-SPRSServiceApplication
foreach ($app in $apps)
{
   New-SPRSExtension -identity $app -ExtensionType “Data” -name “ADS” \u2013TypeName “Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=71e9bce111e9429c”
}<\/font> <\/p>\n

They also refer to the technet article (above) and say the very vague \u201cthe specific modifications can be found towards the bottom of this TechNet article\u201d.  What I ended up doing was adding the following<\/font> 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 \u201crsreportserver\u201d). <\/p>\n

<Configuration><\/font> <\/p>\n

\u2026<\/font> <\/p>\n

  <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><\/font>
<\/Configuration><\/font><\/p>\n

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. <\/p>\n","protected":false},"excerpt":{"rendered":"

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 \u2013 this was throwing an error in the UI.  However, as they were … <\/a><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[17,7,32],"tags":[],"acf":[],"yoast_head":"\nSharePoint 2010 Access Services Reporting with SQL Server 2012 - Archive<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SharePoint 2010 Access Services Reporting with SQL Server 2012 - Archive\" \/>\n<meta property=\"og:description\" content=\"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 \u2013 this was throwing an error in the UI.  However, as they were …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/\" \/>\n<meta property=\"og:site_name\" content=\"Archive\" \/>\n<meta property=\"article:published_time\" content=\"2013-05-01T19:18:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-24T21:48:45+00:00\" \/>\n<meta name=\"author\" content=\"Colin Phillips (Alumni)\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Colin Phillips (Alumni)\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/\",\"url\":\"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/\",\"name\":\"SharePoint 2010 Access Services Reporting with SQL Server 2012 - Archive\",\"isPartOf\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#website\"},\"datePublished\":\"2013-05-01T19:18:26+00:00\",\"dateModified\":\"2023-02-24T21:48:45+00:00\",\"author\":{\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\"},\"breadcrumb\":{\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Blog Archive\",\"item\":\"https:\/\/regroove.ca\/archive\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SharePoint 2010 Access Services Reporting with SQL Server 2012\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/regroove.ca\/archive\/#website\",\"url\":\"https:\/\/regroove.ca\/archive\/\",\"name\":\"Archive\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/regroove.ca\/archive\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b\",\"name\":\"Colin Phillips (Alumni)\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g\",\"caption\":\"Colin Phillips (Alumni)\"},\"url\":\"https:\/\/regroove.ca\/archive\/author\/cphillips\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SharePoint 2010 Access Services Reporting with SQL Server 2012 - Archive","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/","og_locale":"en_US","og_type":"article","og_title":"SharePoint 2010 Access Services Reporting with SQL Server 2012 - Archive","og_description":"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 \u2013 this was throwing an error in the UI.  However, as they were …","og_url":"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/","og_site_name":"Archive","article_published_time":"2013-05-01T19:18:26+00:00","article_modified_time":"2023-02-24T21:48:45+00:00","author":"Colin Phillips (Alumni)","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Colin Phillips (Alumni)","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/","url":"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/","name":"SharePoint 2010 Access Services Reporting with SQL Server 2012 - Archive","isPartOf":{"@id":"https:\/\/regroove.ca\/archive\/#website"},"datePublished":"2013-05-01T19:18:26+00:00","dateModified":"2023-02-24T21:48:45+00:00","author":{"@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b"},"breadcrumb":{"@id":"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/regroove.ca\/archive\/2013\/05\/01\/sharepoint-2010-access-services-reporting-with-sql-server-2012\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Archive","item":"https:\/\/regroove.ca\/archive\/"},{"@type":"ListItem","position":2,"name":"SharePoint 2010 Access Services Reporting with SQL Server 2012"}]},{"@type":"WebSite","@id":"https:\/\/regroove.ca\/archive\/#website","url":"https:\/\/regroove.ca\/archive\/","name":"Archive","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/regroove.ca\/archive\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/adeb0df1cc7a862160be620ca7eace1b","name":"Colin Phillips (Alumni)","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/regroove.ca\/archive\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/14eeab0d377e9630e0983d9c08911979?s=96&d=mm&r=g","caption":"Colin Phillips (Alumni)"},"url":"https:\/\/regroove.ca\/archive\/author\/cphillips\/"}]}},"_links":{"self":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/834"}],"collection":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/comments?post=834"}],"version-history":[{"count":1,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/834\/revisions"}],"predecessor-version":[{"id":1941,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/posts\/834\/revisions\/1941"}],"wp:attachment":[{"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/media?parent=834"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/categories?post=834"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regroove.ca\/archive\/wp-json\/wp\/v2\/tags?post=834"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}