Configuring Access Services 2013 on Premises - Kirk Evans Blog - Site Home

UPDATE: See White Paper: Office 2013--Access Services Setup for an On-Premises Installation for updated guidance for configuring Access Services 2013 for the RTM version.

Recently I co-presented the developer track for Ignite training with Andrew Connell. The training, developed by Critical Path, covered the new SharePoint and Office app models, workflow, BCS, and other topics. One of the topics that I covered was Access Services 2013. There was quite a bit of interest in this session, so I thought I would share the configuration steps necessary to start exploring Access Services 2013.

Jump Start with the Cloud

If you create an Office 365 Preview site, getting started is incredibly easy. Just follow the steps in the blog post, Get started with Access 2013 Web Apps. You’ll see that it is really quick and easy to get started playing with the new features. You don’t even need SharePoint installed locally, you just need Access 2013 client to get started.

Pre-Requisites

If you want to use Access Services 2013 with your on-premises deployment, you are going to need to go through some configuration steps. The following are pre-requisites for using Access Services 2013.

SharePoint Server 2013
SQL Server 2012
Microsoft Access 2013 Client

Configure Isolated App Domain

Access Services 2013 uses the new app model, so we need to configure our environment to host the new apps. The first step is to configure an isolated app domain. The steps to configure an isolated app domain are documented in MSDN. However, a colleague, Tom Van Gaever, has written a great post, “Prepare SharePoint 2013 Server for App development: Create an Isolated App Domain PowerShell Script” that performs all necessary steps including disabling loopback check.

Configure SQL Server 2012 for Access Services 2013

The next step is to configure SQL Server 2012 to support Access Services 2013. This consists of adding required features, setting the Enable Contained Databases property, and setting SQL security mode, setting the correct permissions for the service account, and enabling protocols.

Adding Required SQL Server 2012 Features

For my development environment, I use the same SQL instance that is used for my SharePoint environment. However, in production you will probably want to isolate this to a second instance so that you can control growth and set up maintenance plans specific to that environment. The following features need to be enabled for your SQL instance.

Database Engine Services
Full-Text and Semantic Extractions for Search
SQL Management Tools
Client Tools Connectivity

If you already have a SQL Server 2012 instance installed but didn’t add these services, you can simply run setup and add these features to an existing installation.

Here is what my feature selection screen looks like. I highlighted the selected options to make them more obvious.

Enable Contained Databases

The next step is to enable contained databases in SQL Server 2012. In SQL Server Management Studio, right-click the server node and choose properties. On the General page there is a property, “Enable Contained Databases”, that you switch to True.

Setting SQL Security Mode

We also need to set the SQL Security mode to “SQL Server and Windows Authentication Mode”. In SQL Server Management Studio, right-click the server node and choose Properties. Choose the Security page and choose the “SQL Server and Windows Authentication Mode” option.

Setting Permissions for the Service Account

When you create the Access Services 2013 service application, you are prompted if you want to create a new application pool or use an existing one. The identity that the application pool runs under must be granted permission to create new databases and to grant security access to those databases. In my case, the security login in SQL is “SHAREPOINT\spService”, and this account must be granted dbcreator, public, and securityadmin roles for the server.

Note: if you are still having problems after performing all steps in this post, a quick test is to grant serveradmin and sysadmin privileges to your service account to rule out security issues. Do not run like that in production, it’s just a quick test to rule out permissions issues in your environment.

Enabling TCP/IP and Named Pipes Protocols

Open SQL Server Configuration Manager and choose SQL Server Network Configuration. Beneath that you should see “Protocols for XXXXX”, where XXXXX is the name of the SQL instance that will host Access Services 2013 databases. Enable Named Pipes and TCP/IP.

Enable Firewall

You need to enable ports 1433 and 1434 for your SQL instance for both TCP and UDP. To do this, create two inbound rules, “SQL TCP” and “SQL UDP”. Enable the proper protocol and provide the appropriate port numbers.

For each of the rules, you are prompted for the profile (domain, private, or public) to which the rule applies. Choose Domain and Private.

Configure Access Services 2013

Now that we have created an isolated app domain, configured SQL, and opened the firewall, the next step is to configure Access Services 2013.

SharePoint Central Administration – Manage Services on Server

In Central Administration, go to Manage Services on Server and start the following services if they are not already started.

Access Services (this is the Access Services 2013 service)
Access Database Service 2010 (for viewing and modifying existing 2010 Access Web databases)
App Management Service
Microsoft SharePoint Foundation Subscription Settings Service
Secure Store Service

These services will be used in subsequent steps.

Create a Secure Store Service Application

Create a new Secure Store Service Application. The settings here are very straightforward.

Once you create the Secure Store Service Application, you may need to run IISRESET in order to proceed to the next step.

Generate a Secure Store Key

The Secure Store will prompt you to generate a key before proceeding. Click the Generate New Key button in the ribbon. If you get an error like the following:

Make sure that the Secure Store service is started in “Manage Services on Server”. Also make sure that you have enough available RAM in your machine. Once you have less than 5% available RAM, WCF calls will fail. Performing a quick IISRESET to shut down application pools should do the trick here. If that does not resolve your issue, then you might try restarting the SharePoint Search Host Controller process.

Create the Access Services 2013 Service Application

Finally! After all that, we are here! In Central Administration, create a new Access Services service application. There are two options, one for Access Services, and one for Access Services 2010. The former is for using Access Services 2013, the latter is to view and modify legacy Access Services 2010 applications.

When you create the service application, you are prompted with a familiar screen. Notice, though, that the database server name is not automatically populated. The whole idea is that you will use a different SQL server instance to contain the databases required to support Access Services 2013.

Create a Custom Web App in Access 2013

Open the Access 2013 client application and choose “Custom web app”. In the next screen, it asks to point to the Team Site where your app will be created.

It may take awhile to create the app. Go to your site collection and view all contents, and you should see your new app. Click it and you’ll see a screen like the following:

Open the app in Access 2013. Add a table (you can create your own or choose from existing templates).

Modify the column names, add new columns, change the views, and start creating solutions with Access 2013!

What Did It Do?

OK, why all the configuration for SQL, what just happened here? When the app is created, it creates a database in SQL server according to the settings that you provided above.

Crack open one of those databases, and we can see the table “Access.Customers” for the app that we just created.

In Access Services 2010, we converted Access tables into lists, the views into .ASPX pages, etc. This meant bloating the content database. In Access Services 2013, it does the right thing and allows you to create databases in SQL. Now you can see why the recommendation is to use a different SQL instance than your SharePoint instance.

A question came up in class if you can edit the HTML, CSS, or JavaScript for the app directly. Unfortunately, you cannot currently edit the HTML, CSS, or JavaScript for the app directly. However, you can add macros such as showing a dialog when a field changes.