This subject is a good example of when not having good information in one place, with some context around it, will lead to failure every time.
The background for this post is that we have a customer where the use of Access Services made very good sense. I have lived through situations where people decided that using SharePoint as a database for dummies was a good idea; only to discover later how bad an idea it really was.
Access Services is not in high demand in our marketplace, but it has a place for cases where all that someone really needs is a controlled environment within which to create simple databases and reports. This approach is especially attractive when compared to hiring someone to do the work in SQL Server directly, as the resident staff might have the Access skills, but are by no means SQL developers. This is the small office scenario that Access was intended for.
There are a few things you need to know concerning the configuration of Access Services.
- Access Services 2010 really does require SQL Reporting Services configured for Integrated Mode
- SSRS Integrated Mode for a stand-alone Developer installation is not the same as it is for a Production Farm
- The Access Services configuration, as of SharePoint 2010 SP1, needs to be tweaked a bit in order for the Reporting web parts to work correctly.
- Kerberos authentication is not required for a two-server configuration (but likely is for separate SSRS application server topologies, i.e. 3 or more servers)
The easy part is setting-up the Access Services service application; start here:
The most relevant set of starting point instructions I found for SQL Reporting Services Integrated Mode in a multi-server Farm were in MSDN here:
Referencing the section “Two server deployment, example 2“, you will see the topology I was aiming to setup. Note that as it says, both SP servers need to be at the same build level. Access Services is part of the Enterprise edition of SharePoint 2010 and the SQL 2008 R2 edition used was also Enterprise.
The Installation Order section of this MSDN article is OK, but basically, since this was an add-on to an existing SharePoint farm, what was needed on the Reporting/SQL Server (Computer 2) was to:
- Install SharePoint 2010 Enterprise SP1 (or the same bits as your WFE)
- Run the Configuration Wizard to join the Reporting Server (SQL Server) to the Farm
You will also need to install the SSRS SharePoint add-on if you have not already done so. When I do new SP farm installations, I add SSRS using the choice to install it and configure it later.
Now you need to configure reporting services. There are two ways to do this, but you may as well go the conventional route (called Remote mode) that enables all integrated SSRS reporting within SharePoint.
Lydia Bronze has a post here that describes in detail how to run through the SSRS Configuration Manager, as well as the final steps in Central Administration you will need to perform to complete the integration of SSRS for SharePoint.
Update 1: For the two server case presented here, Step 13 of Lydia’s post needs to be modified as I have done below in red. Some have had this work by selecting Windows Authentication in a single server model, but the two server model absolutely requires it as far as I can tell.
13. In the Report Server Web Service URL provide the URL we have created in section 10. Choose Trusted Account for the authentication mode and User credentials. Choose whether to activate the feature on all existing site collections or in specified site collection. Click on OK.
OK, at this point if you created an Access Database site using one of the stock site templates such as the Issues Database, you need to do one more thing before you’re successful.
Adam McEwen has these steps documented here and it includes a PowerShell script (Issue 3) to correct the default SSRS configuration (on SQL Server 2008R2 in my case) settings so that Access Services can use SSRS web parts for report rendering (at least with the SP1 bits). If you need to manually update the rsreportserver.config file for SQL 2008R2 to get past Issue 3, then that guidance is here.
Update 2: Note that following the documented guidance linked to above for Issue 3, is incorrect as literally written. The text stating “..under the <Extension> node..” should be omitted, i.e. the change is made immediately under the <Data> node. Shout out to Steve for pointing this out.
I suppose like anything, Microsoft’s documentation, often done by MVPs and partners, is only first-rate when the demand is high enough for that to be the case.