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
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’
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.
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=220.127.116.11, Culture=Neutral,
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…..