通过

Configuring Access Services 2013 on Premises

  • 项目

UPDATE: See Access Services 2013 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.

image

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

image

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.

image

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.

image

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.

image

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.

 UPDATE: The account needs additional permissions to the configuration database. See my updated post, https://blogs.msdn.com/b/kaevans/archive/2013/07/14/access-services-2013-setup-for-an-on-premises-installation.aspx. 

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.

image

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.

image

image

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

image

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

image

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.

image

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:

image

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.

image

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.

image

 

 UPDATE: Additional configuration needs to be applied to the SharePoint servers and to grant permission to the config and content databases. See my updated post, https://blogs.msdn.com/b/kaevans/archive/2013/07/14/access-services-2013-setup-for-an-on-premises-installation.aspx. 

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. 

image

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:

 

image

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

image

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.

image

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

image

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. 

For More Information

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

UPDATED GUIDANCE for RTM:  https://blogs.msdn.com/b/kaevans/archive/2013/07/14/access-services-2013-setup-for-an-on-premises-installation.aspx

Comments

  • Anonymous
    September 06, 2012
    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

  • Anonymous
    September 06, 2012
    Sorry you are having problems Jake.  I updated the text to say the service name is "Access Services" and added a screen shot.

  • Anonymous
    September 23, 2012
    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

  • Anonymous
    September 28, 2012
    Thanks for the post..

  • Anonymous
    June 22, 2014
    Thanks for this go through. I have just signed up for a free SharePoint site with .cloudappsportal. They are offering Access hosting services along with it. I don't know how different it would be from on premises solution. Thanks but.

  • Anonymous
    August 05, 2014
    Hi, I am getting the below error. please help me. "sorry something went wrong An error has occured" Thanks & Regards, Srini

  • Anonymous
    August 07, 2014
    Hi, Kirk nice article, in my case access services does not appear available in the list, Do you know why? Greetings

  • Anonymous
    August 11, 2014
    Hi Kirk, I have found out that Access Services 2013 and SharePoint Apps can be used instead of InfoPath. There won't be any new versions of InfoPath. I have created Word templates using InfoPath and want to test Access Services 2013 and SharePoint Apps to see how they work when creating Word templates. Do you have documentation on this?

  • Anonymous
    October 28, 2014
    Hi Kirk, I tried your great manual but got following exception when creating new Custom Web App: Result=Microsoft.Office.Access.Services.Proxy.AccessServerSessionException: An error has occurred.     at Microsoft.Office.Access.Services.Proxy.ServerSession.ExecuteWebMethodCore(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod)    

  • at Microsoft.Office.Access.Services.Proxy.ServerSession.ExecuteWebMethod(WebMethodType webMethodType, WebMethodBehaviorAttribute webMethodBehavior, CommandParameter parameter, CoreWebMethod coreWebMethod) No records in Event Viewer. I found some articles regarding that exception, unfortunatelly no obvious solution. Do you have an idea how to trace the exception or even get rid of it. Thank you very much, Radek
  • Anonymous
    January 21, 2015
    Hello Kirk
  1. It is required to enable this Subcription and App Manageent service in web front end servers
  2. We have to install SQL feature pack on all sharePoint servers in the farm. Please relpy ASAP....

  • Anonymous
    March 17, 2015
    If people want the ease of Access 2013 Web Apps but with the ability to add custom coding and styling, LightSwitch might be a better choice.

  • Anonymous
    April 28, 2015
    Hi Kirk, I am also not seeing Access Services.  There is no mention of Access Services 2010 or Access Services in the drop down menu.  I can't find anywhere to enable it either.  Does anyone have any idea how to do that?  

  • Anonymous
    April 28, 2015
    @Gretchen - this post is outdated, as stated at the top of this post.  For more updated guidance, see blogs.msdn.com/.../access-services-2013-setup-for-an-on-premises-installation.aspx. .