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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Now that we have created an isolated app domain, configured SQL, and opened the firewall, the next step is to configure Access Services 2013.
In Central Administration, go to Manage Services on Server and start the following services if they are not already started.
These services will be used in subsequent steps.
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.
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.
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.
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!
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.
Prepare SharePoint 2013 Server for App development: Create an Isolated App Domain PowerShell Script
How to: Set up an on-premises development environment for apps for SharePoint
Get started with Access 2013 Web Apps
Hey Kevin. I appreciate your posting this as I have been having trouble with pre-requisites. However, in my installation, with SQL Server 2012, SharePoint 2013 Preview, and having configured an App domain, I am still having trouble finding Access Services 2013 in the Manage Services on Server link. The only Access services I am showing in "Services on Server" is one for Access Database Service 2010 and Access Services. Access Services 2013 is no where to be found. What did I do wrong or what did I not install? Please help, thank you
Sorry you are having problems Jake. I updated the text to say the service name is "Access Services" and added a screen shot.
I appreciate your help in the last post. However, I am still having problems. I configured SharePoint as per your directions, but when I publish an Access 2013 application to my site in SharePoint 2013, I receive this error,
"This application was designed in a version of Microsoft Access that is not compatible with the version of Access Services 2010 installed on this server."
I must have done something wrong. Please Help. Thank You
Thanks for the post..