Issues when deploying Access Services in SharePoint 2010

Access Services in SharePoint 2010 provides a neat mechanism for editing, updating and publishing a Microsoft Access databases in the browser. And there is no requirement for full blown copy of the Access client on your desktop unless you want to make any database changes.

This blog article is not going to provide a functionality overview of Access in SharePoint, or a break-down of basic the installation steps (which are pretty straightforward), but I wanted to highlight a few of the little gotchas which can cause grief once this Service Application has been instantiated.

The troubleshooting tips I’ve made assume the following:

  • Your SharePoint 2010 Farm is running with an Enterprise license and is patched to SP1 or above
  • Access Services is configured as a Service Application within your Farm and the service itself is set to ‘Running
  • Either the ‘SQL Server Reporting Service 2012’ Service Application, or the ‘Reporting Services –Add-in’ for SharePoint Integrated mode has been applied
  • The SharePoint ‘State Service’ has been configured (this is a ‘Powershell Only’ Service Application in terms of provisioning) http://technet.microsoft.com/en-us/library/ee704548(v=office.14).aspx
  • You have created either a Web, Contacts or Project database using the standard SharePoint template

Issue 1

Once you’ve created your desired Access Services database and attempt to open it for the first time, you may find the ‘ever-so-helpful’ (not) failure notification: ‘An error has occurred. Click here to try again’


So where is my correlation ID to help me track the problem? In this instance, the root of the problem is loud and clear if you open the Windows Event Log.


Access Services runs with the context of it’s IIS Application Pool identity, and thus, this account requires ‘dbo’ access to the SharePoint content database. This can be achieved by locating the domain account (this should not be running under Local System by the way) in SQL Management Studio and editing its designated permissions (below shows an example of ‘db_owner’ granted database owne’ rights to the ‘Projects_DB’

 
 


 
 

Issue 2

When you try and run a report from within the Access Services ‘Report Centre’, you may witness the following error even if you have enabled Session State within your Farm environment: ‘This report failed to load because session state is not confirmed on’.

To confirm Session State is enabled, run ‘Get-SPServiceApplication’ from within Powershell and confirm it’s provisioned. One point to remember though, an IIS reset is required after provisioning Session State.



If ‘State Service’ is listed, the issue is down to Reporting Services not being configured correctly. And this is likely to be the cause of Issue 3.

Issue 3

With previous version of SQL Server Reporting Services (i.e SQL 2008 R2) there was a requirement to modify the ‘rsreportserver.config’ file to enable Access Services to leverage SSRS (and this was a rather painful exercise). However, the introduction of SSRS 2012 as a conventional Service App mitigates this manual step, although you still need to run some Powershell for a fully functional reporting experience.

$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”

}

The first screenshot below displays the error you will witness if you don’t run the Powershell fix-up script


 
 

 
 

 
 

And now you have a working Access Reporting service…..


  

Advertisements

2 thoughts on “Issues when deploying Access Services in SharePoint 2010

  1. I have recently updated and republished an Access DB to SharePoint 2010 / Access Services and I can see my startup Navigation but I can’t see any of my forms attached to tables. This was a working web app for several months and I updated today to add some tables and forms and then republished and now the above condition. When I click navigation form to open a form that is tied to a table I see the screen shot show in Issue 1 above. But again this all worked until the update. Now the only thing I can think of is I developed this update on a separate site and then imported the objects from that into my production web app, so maybe pointer issue? App works perfect in client but fails as described above after publish. I also tried to delete old production web app and republish from scratch same problem. Any ideas you might have would be greatly appreciated.

  2. Pingback: Configure Access Services in a two server SharePoint 2010 farm | Russ Ramirez's SharePoint Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s