Access Services 2013 Setup for an On-Premises Installation

This blog post will show you how to configure Access Services 2013 to use in your own environment. 

Overview

Back in August 2012, when SharePoint 2013 was just in Beta 2 phase and not yet released, I wrote a blog post on Configuring Access Services 2013 on Premises.  That blog post was accurate at the time of its writing, but changes to the product were made when the product was released to manufacturing (RTM).  Additionally, a white paper was released (White Paper: Office 2013--Access Services Setup for an On-Premises Installation) that provided steps for configuring post-RTM, but missed a few needed details.  This blog post will show you from the how to set up Access Services 2013 in an on-premises installation. 

Jump Start with the Cloud

Before we go into all the messy configuration details, it’s important to note that NONE of this is required if you are using Office 365.  If you create an Office 365 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

The following pre-requisites are mandatory to work with Access Services 2013.  This does not work with previous versions of SharePoint, SQL, or the Office client.

  • SharePoint Server 2013
  • SQL Server 2012 Feature Pack Components installed on the SharePoint Server
    • Microsoft SQL Server 2012 Local DB (SQLLocalDB.msi)
    • Microsoft SQL Server 2012 Data-Tier Application Framework (Dacframework.msi)
    • Microsoft SQL Server 2012 Native Client (sqlncli.msi)
    • Microsoft SQL Server 2012 Transact-SQL ScriptDom (SQLDOM.MSI)
    • Microsoft System CLR Types for Microsoft SQL Server 2012 (SQLSysClrTypes.msi)
  • SQL Server 2012
  • Microsoft Access 2013 Client

Note that the requirements say that SQL Server 2012 is required.  SQL Server 2012 is only required for the database server where the app databases will be created, it is not required that the databases for SharePoint be on SQL Server 2012.  To make that clear, your SharePoint databases can reside on SQL Server 2008 R2, but the database server that is used for your Access Services databases must be SQL Server 2012.

Once all of this is installed, we can begin the configuration.

Configuration

There are five basic configuration steps necessary for Access Services 2013.

  • Configure for apps
  • Configure SQL Server 2012
  • Create a Secure Store service application
  • Create an Access Services service application
  • Configure IIS
  • Configure Security

We will walk through each of these in detail.

Configure for apps

Access Services in SharePoint 2013 uses the new app model, so you will need to configure your environment for apps by creating an isolated app domain.  There is a detailed walkthrough document, How to: Set up an on-premises development environment for apps for SharePoint.  The part we are concerned with in that document is the section, “Configure an isolated app domain to create and deploy SharePoint-hosted apps.” 

If you have already configured your environment for apps, you can skip this step.

The shortened version:

1) Create a top level DNS zone with a different URL than your SharePoint server, something like “contosoapps.com”. 

image

2) Create a new A record in DNS with a wildcard entry “*” that points to your load balancer.

image

3) Create a new web application in SharePoint that listens on port 80 with no host header.  It can be empty, with no site collections in it.  This web application is used to route requests for your apps to SharePoint.

image

4) Run the PowerShell.

 Set-SPAppDomain "contosoapps.lab"
net start sptimerv4
net start spadminv4

Get-SPServiceInstance | where{$_.GetType().Name -eq "AppManagementServiceInstance" `
    -or $_.GetType().Name -eq "SPSubscriptionSettingsServiceInstance"} | `
    Start-SPServiceInstance
Get-SPServiceInstance | where{$_.GetType().Name -eq "AppManagementServiceInstance" `
    -or $_.GetType().Name -eq "SPSubscriptionSettingsServiceInstance"}

$managedAccount = Get-SPManagedAccount "contoso\sp_service"
$appPool = New-SPServiceApplicationPool -Name "SharePoint Services App Pool" `
    -Account $managedAccount
$appPool = Get-SPServiceApplicationPool "SharePoint Services App Pool"
$appSubSvc = New-SPSubscriptionSettingsServiceApplication `
    –ApplicationPool $appPool –Name "Subscription Settings Service Application" `
    –DatabaseName SettingsServiceDB 
$proxySubSvc = New-SPSubscriptionSettingsServiceApplicationProxy 
    `–ServiceApplication $appSubSvc
$appAppSvc = New-SPAppManagementServiceApplication `
    -ApplicationPool $appPool -Name "App Management Service Application" `
    -DatabaseName AppServiceDB
$proxyAppSvc = New-SPAppManagementServiceApplicationProxy -ServiceApplication $appAppSvc

Set-SPAppSiteSubscriptionName -Name "app" -Confirm:$false

 

The highlighted part points to the zone that you created in DNS in the previous step.  If you have problems with the PowerShell or are not sure what’s going on, see the article How to: Set up an on-premises development environment for apps for SharePoint and go to the section on creating an isolated app domain.

Configure SQL Server 2012

The following features are required for your SQL Server 2012 installation that will be used for your Access Services databases.

  • Database Engine Services
  • Full-Text and Semantic Extractions for Search
  • SQL Management Tools feature (for troubleshooting)
  • Client Tools Connectivity

If you didn’t add those when installing SQL Server 2012, no problem, just run setup again and add those features to the existing installation.

Now that you have the features installed, here are the configurations required. 

  • Security Mode = Mixed Mode
  • Service Account (dbcreator, securityadmin)
  • Enable Contained Databases = True
  • Allow Triggers to Fire Others = True
  • Default Language = English
  • TCP/IP Protocol = Enabled
  • Named Pipes Protocol= Enabled
  • Windows Firewall Inbound Ports TCP 1433, TCP 1434, and UDP 1434

If you are unsure of how to configure this, see the whitepaper White Paper: Office 2013--Access Services Setup for an On-Premises Installation that provides details on how to configure each of these in SQL Server 2012.

Create a Secure Store service application

This one is really straightforward.  Go into Central Administration and create a new Secure Store service application.

image

Once created, go to Central Administration / System Settings / Services on Server and start the Secure Store service.

image

Go to Central Administration / Application Management / Manage Service Applications.  Click the Secure Store service application.  You are prompted to create a new key before continuing.  Click the “Generate New Key” button in the ribbon.

image

That’s all you have to do, you do not have to create an application or set any additional permissions.

 

Create an Access Services service application

This one is also really straightforward.  Go into Central Administration and create a new Access Services service application.

image

When creating service applications, I typically use one service application pool for all of my service applications.  For example, I usually have a service application pool named “SharePoint Services App Pool” that runs as the user “contoso\sp_service”.  For Access Services, I choose to create a separate identity, “contoso\sp_access” that runs with a separate application pool.  The reason why is because this account needs additional permissions to each SharePoint server and the SQL database, and I don’t want to grant these additional permissions to my typical service application account.  Rather than grant additional permissions to the account that runs other service applications, I create a new identity and application pool.

image

Once the service application is created, go to Services on Server and start the Access Services service.

image

The whitepaper mentioned above indicates you need an Access Services 2010 service application, but that is incorrect.  You don’t have to create an Access Services 2010 service application, that is just there for backwards-compatibility. 

Configure IIS to Load User Profile

Next we need to tell IIS to load the user profile for the application pool identity.  Since you’ve created the service application in an application pool with its own identity, it will be easy to pick out which application pool you need to work with.

image

Right-click and choose Advanced Settings.  Go to the property “Load User Profile” and set its value to True.

image

 

Configure Security / Troubleshooting

These are the missing steps in that whitepaper document I mentioned previously. 

When I have seen people try to install and configure Access Services in their SharePoint 2013 environment, I have seen them get frustrated at somewhat misleading error messages that indicate “you do not have permission to perform this action.”  If you use a tool like ULSViewer to view the ULS logs and filter based on the correlation ID that is shown in the error dialog, you can usually read a friendly error message that tells you what’s wrong.  Here are the additional configuration details necessary.

    1. You must have a Secure Store Service Application and its associated service instance running.  You must generate a secure store key before attempting to create your Access apps.  This only needs to be done once.  If you don’t create the service application and have the service running in Services on Server, you will get an error that the app cannot be created and to try again later.
    2. The identity for the application pool that is running Access Services must have permissions to the content database.  In my environment, I use contoso\sp_access as this account.  The easiest way to do this is using PowerShell:
 PS C:\> $w = Get-SPWebApplication https://intranet.contoso.lab
PS C:\> $w.GrantAccessToProcessIdentity("contoso\sp_access")
PS C:\> $w.Update()

 

  1. The identity for the application pool that is running Access Services cannot be the farm account.  If you attempt to run the application pool as the farm account, you will see an error like "04.06.201304:14:58.47  w3wp.exe (0x1080) 0x12FC Access Services Data Layer     ahkty  Exception  System.InvalidOperationException:TheSystemAccount   cannot perform this   action." The fix is to run the application pool as a domain account that is not the farm account, and make sure this account has the necessary permissions (see below).
  2. The identity for the application pool that is running Access Services must have permissions to the App Management service application database.  The easiest way to do this is to go to the App Management Service Application, click next to it (to highlight the row), select Permissions, and add the identity running Access Services with full control. image
  3. The identity for the application pool that is running Access Services must have permissions to execute proc_putObjectTVP in the config database.  Looking at the security for that stored proc, you need to add the SPDataAccess role to the account in the config database.  The only way I know to do that is to open SQL Service Management Studio and add the role directly.image
  4. The identity for the application pool that is running Access Services must have permissions to the config cache.  If you haven’t granted this permission, you will see an error in Access that indicates you do not have permission, and in ULS you will see an error like:

07/14/2013 12:58:24.05 w3wp.exe (0x1958) 0x0EB8 SharePoint Foundation Topology 8xqx High Exception in RefreshCache. Exception message :Access to the path 'C:\ProgramData\Microsoft\SharePoint\Config\3d0f9f56-beb0-4f5b-af31-7f1aee5aa887\cache.ini' is denied. 23082f9c-f051-b013-2187-378787956946

You can either go directly to that path and grant read/write permissions for the identity on each SharePoint server, or you can add the identity running the application pool to the WSS_ADMIN_WPG group on the SharePoint server(s) (which already has read/write permissions to the path).  I prefer to explicitly grant read/write permission to the config cache instead of adding to WSS_ADMIN_WPG because I want to give the least amount of permissions possible.

 

Summary

This blog post summarizes the steps necessary for configuring Access Services 2013 in an on-premises installation, including security information not found in other documentation.  If you are unable to get it working, please leave comments to this post so that others can benefit.

For More Information

White Paper: Office 2013--Access Services Setup for an On-Premises Installation

How to: Set up an on-premises development environment for apps for SharePoint