Installation and Configuration of Access Services 2013 (On Premise)

10th January


When I first started on my journey to install and configure Access Services 2013 for SharePoint I had to visit quite a few different articles in order to get the process and procedure "Correct". With this in mind I have decided to pull together a guide on configuring and installing Access Services 2013 in the hope that it can save someone considerable time and effort – reducing the trawl across the internet for multiple pieces of information, based on obscure errors in ULS.

You can still use this approach if you don't have anonymous access enabled - but as far as a I can see this is the only method that will worked with a fully locked down web portal and anonymous access - whilst still accessing SharePoint lists for data.

All the documents I have encountered/used are listed at the bottom of this post, and I have no doubt that you will come across most of them already (all credit to the original authors where due).

Pre-requisites


As you are most likely aware, Access Services 2013 will require a fully functioning application model development environment, and I'm assuming that as you have SP2013 installed and the App Domain configured that you're rocking SQL 2012 as well.

When access services creates "Apps" it creates a DB, which in itself must reside in SQL. If you are thinking of letting your development base create their own Access 2013 applications then I highly recommend that you use a different SQL instance (at the very least) to house your Access Services DBs. For the purposes of this article I will be using the same instance as SharePoint (for ease of setup), but this should never happen in production and certainly is not best practice.

You must also ensure that you have a secure store application created and populated with a key generated. As it is now 2014 I'm going to assume that you all have this service app created, if not a good old fashioned Bing search should provide adequate guidance on setting this up.

Installing and Configuring Access Services 2013 (on premise)

When configuring Access Service 2013 for on Premise I do it in 5 stages:

  1. Configuring SQL
  2. Configuring Usage Account
  3. Configuring the Application Server
  4. Creating the Service Application
  5. Testing

Stages 1 & 2 generally overlap as the user account has to be granted access to certain elements with SQL, but I will run through the steps in order.

Stage 1 – Configuring SQL Server

First of all you will need to make sure that your instance has the following elements installed (do this via SQL installation media if required):

Right click the main node for the SQL server, and select properties > Advanced and ensure that the following is set

  • Enable Constrained Databases: True
  • All Triggers to Fire Other: True
  • Default Language: English

Now ensure that the Server Authentication mode is set to SQL Server and Windows Authentication. On SQL server node right click, properties > Security:

Set SQL authentication mode: SQL Server and Windows Authentication mode

Next we need to ensure that named pipes is enabled. Open SQL Server Configuration Manger. Client Protocols > Named Pipes:

Named Pipes: Enabled

After this you must make sure you the RESTART SQL SERVICE for the change to take effect.

If you have a firewall enabled on your SQL box (you should) you will need to add rules to allow communication through for 1433 and 1434, both TCP and UDP - 2 inbound rules (SQL TCP and SQL UDP) turn on for both Domain and private

Stage 2 – Configuring Usage Account

Next up create a new service account for use with Access Services. Mine will be called SP2013Access. This account will be used to create the application pool for Access Service 2013 and will be the account that accesses the SQL instance. Due to the added memory footprint when creating multiple app pools I usually only have one or two application pools. As Access Services requires special permissions on SQL and App server box I usually create Access Services 2013 with its own account, as I only want these special permissions available to Access Services 2013 and not to any unnecessary services. This account cannot be a farm account!

On the SQL box that will house your DBs for Access Services 2013, Set service account perms in SQL - dbcreator, public and secadmin *** very important *** - without this you will get generic access denied errors (see troubleshooting steps at the end of this document)

Open Security > Logins. Add your user as a login and assign it the following Server Roles:

  • Dbcreator
  • Public
  • Securityadmin

Next (and probably most controversially) we need to grant the SP_Data_Access role on the SharePoint Config DB to our SP2013 Access account. In the days of SP2010 the Add-SPSHellAdmin command used to grant users the DB owner right. In SP2013 this is no longer the case. The Add-SPShelladmin command grants SP_Data_Access role, which means we don't have to edit the SharePoint Config DB as some other sources originally suggested.

The service account will also need to access the config cache (c:\ProgramData\Microsoft\SharePoint\Config\<GUID>) on the SharePoint servers and to do this the account needs to be a member of WSS_ADM_WPG group on each SharePoint server – when running the Add-SPShelladmin commandlet the service account is automatically added to this group on all SharePoint servers, effectively killing two birds with one stone.

From Tech net Link

SP_DATA_ACCESS database role

The SP_DATA_ACCESS role is the default role for database access and should be used for all object model level access to databases. Add the application pool account to this role during upgrade or new deployments.

The SP_DATA_ACCESS role replaces the db_owner role in SharePoint 2013.

The SP_DATA_ACCESS role will have the following permissions:

Grant EXECUTE or SELECT on all SharePoint stored procedures and functions

Grant SELECT on all SharePoint tables

Grant EXECUTE on User-defined type where schema is dbo

Grant INSERT on AllUserDataJunctions table

Grant UPDATE on Sites view

Grant UPDATE on UserData view

Grant UPDATE on AllUserData table

Grant INSERT and DELETE on NameValuePair tables

Grant create table permission

To add the SP_Data_Access role and add the service account to WSS_Admin_WPG on each server, run the following PowerShell command once - from any SharePoint server (Afterwards open SQL and confirm on the Security settings on the Config DB that the role has been applied):

Add-SPShelladmin –Username Sundown\SP2013Access

Now we must make sure that the service account has access to the App Management Service App.
Go to Central Admin > Manage Service Applications > Select the App Management Service APP > Choose your Account > Add > Tick Full Control > OK

Stage 4 – Creating the Service Application

Register your service account in Central admin
Go to central admin > Security > Registered Service Account

Now we are ready to create a service application.
Go to central admin > Manage Service Applications > New > Access Services and fill out as follows:

Create the service application as follows:
Name: Application Name
Application Database Server: SQLServer\Instance
Create New Application Pool
Use your Registered Service Account for Access Services 2013

Stage 5 – Testing

All testing must be completed on a non SP machine and preferably a client. If you're solely in a development environment and short on machines then installing the Office 2013 professional plus client on SQL works – but not recommend of course :)
Open you're Access 2013 client > Select Custom App > Type in the name of the App and the URL to the site collection that you want to house your Apps:


Now navigate to the newly created app (Launch App in Access Client) and launch the Access 2013 Client using the hyperlink provided (this proves two way communication), then add tables as you desire – as a quick test search for 'Order' and add the default table. Saving the db automatically uploads it to your app:

That should conclude your setup for Access Service 2013 :)

Troubleshooting

The testing section seems relatively straight forward, but in reality when trying to implement Access Service 2013 things rarely go smoothly. The majority of the issues that you will face will either be caused by permissions at the various levels or issues with the App model. Below are my top tips when troubleshooting Access Service 2013 installation

Check those permissions! - If you trawl through ULS when the Access 2013 app is created you will note that it creates a local copy of the app first then publishes it to the app model – if you can see that the local copy has been completed, but the publishing of the app fails, then nine times out of ten this is a permissions issues (usually denoted with a can't find Db or app error). I can't stress enough that you should re-check your permission allocation:

Does your Access Services account (App Pool Account) have a log on to the SQL box?

Have you granted the service account SPShellAdmin?

Have you given it permissions to the web app (GrantAccessToProcessIdentity)?

Service App Won't create A common mistake on app creation is in the section for Database when creating the Service Application people tend to enter the name of a db i.e. wss_content_Access2013 – in reality this section needs to know your DB server name and instance name that you will store DB's for access services. This may seem logical, but you would not believe the amount of people that automatically default to conditioned behaviour and add a DB name out of habit.

Make sure the Service is started - Make sure that the service is started on the server you want to run Access Services 2013 – and also make sure that the components are installed on it, and if any other servers have the service started – make sure that the components are installed on there also.

A word on bindings - In some fringe cases I have seen IISBindings applied for the app model, in this case the default binding in IIS must be a wildcard – otherwise your app will create just fine, but will give 404 when's rendered

Access Service 2013 a Common Correlation Error - This error crops up more than most and can be very misleading when it comes to trouble shooting. If you get to this point, I implore you to go back through your permissions tree and make absolutely certain that you have applied the permissions at all the relevant levels

ULS Log Entries – Very misleading!

Get the full error from Event Viewer (Microsoft Office Alerts):

Fix:
This is usually caused because the service account does not have the required permission at the sq instance level – in this scenario – we forgot to add the dbcreator role – so it's worth checking!

Reference

None of this post would have be possible without the reference material provided by these previous posts on the subject. I would like to thank the original authors for their time and effort - great work people!

configuring-access-services-2013-on-premises
access-services-2013-setup-for-an-on-premises-installation
technet.microsoft.com/jj714714.aspx
sharepoint-2013-access-services - Hints and Tips
Whitepaper for install
SQL 2012 Feature Pack
Access Service PowerShell Commands
Account perms in 2013

Follow Us