Kirk Evans Blog

.NET From a Markup Perspective

Access Services 2013 Setup for an On-Premises Installation

Access Services 2013 Setup for an On-Premises Installation

Rate This
  • Comments 18

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 http://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

  • In a multi-server farm, which SharePoint servers do the SQL Feature Pack components need to be installed on?

  • Paul - this needs to be installed on any of the servers where the Access Services service is running.  I am never a fan of installing components to some servers and not others, this creates nightmares for maintenance and patching.  I would recommend putting it on every server in your farm.

  • When i add a new access app in my site, it creates the database with a name that is random...is there a way to define the name?

    Also, when i try to open these access apps from my site...it prompts me for credentials and the only one that grants me access is my farm account but i must enter it 3 times, when it does let me in...the page is entirely blank...i do see however that it is passing me over to my app domain...could you help me understand where i could look to troubleshoot this login issue and the issue with no content on the page?

  • To correct my previous post...

    It will prompt me for credentials 3 times and any account can be tried....it will still result in a blank page...the same happens if i just hit cancel at the login box...makes me thing that the blank page is being generated due to permission issues, but where do i look to grant access....if thats the case...

  • Found out the issue with my the prompting...it was related to not having a web application with no host header created using port 443...i had one created using port 80 but realised that i needed this 443 web app as well.

    Still curious about the database names though if you have any opinions to offer me on that.

  • Glad you've got it figured out, Jacob.  The database names are not configurable, you get those pretty GUIDs for no extra charge :)  

  • One more thing: The identity for the application pool that is running Access Services (e.g. “contoso\sp_access”) must have "Log on as a batch job" rights in Local/Group Security Policy (User Rights Assignment section). Otherwise, the application pool won't run (it can be started from IIS Manager, but when a request come, it will be stopped for violating batch logon rights)

  • Thank you for the great article, especially "Configure Security / Troubleshooting" info. I wasted many hours of troubleshooting when trying to create an access app even after following all the steps on the white paper. I was getting the “you do not have permission to perform this action.”

    Your troubleshooting steps helped me resolve the problem.

  • First, thanks for the detailed step by step. great work. Now I can get to the point where I could create the APP OK. but when I click to open the application link, is generated: app-115b4e2e9d6059.MySubdomain.MyDomain.com and does not work, could you help me with this?

  • Ronaldo - Check the ULS logs.  Turn logging up to verbose, attempt the operation, and then review the logs to look for clues.

  • I have SP 2013 on -prem. I am trying to access  Access 2013 reports and getting the following message..

    Access Services 2010 reports are not enabled. To view the report, open the report in the Access client.

    Troubleshoot issues with Microsoft SharePoint Foundation.

    Correlation ID: 3d79909c-c8ea-30f3-b29b-e17c169a065f

    Date and Time: 5/13/2014 7:45:41 AM

    Any ideas?

  • Is the isolated app domain required for access services? I've only read that the isolation is for security purposes.

  • Justin - yes, the isolated app domain is required to support the app model, which Access Services is built upon.

  • Can on premise SharePoint 2013 be configured to use SQL Azure to store Access Services data? I am looking through my timer jobs and seeing things like Access Services Provider for SQL Connection Statistics (SQL Azure only)  and Access Services Provider for SQL Event Log (SQL Azure only)

  • Kirk, I appreciate your extensive and in-depth articles for configuring SharePoint, it's been a great help.

    I am having problems with my Access Web Apps.  I have a host named site collection that is ( or will be) a host for Access Web Apps.  I can create the Apps, open them and add any different type of tables and can launch them without error.

    However, when I try to add a SharePoint list and the pop-up that comes up asking to "Trust It" pops up, I can click on trust it, then I get a blank screen in the pop-up window.  I have to cancel, then add the list again, but I don't get prompted to Trust It, and the list is added as a table.

    When I launch it though, I receive an error that reads "Sorry, your changes could not be saved.  Please refresh your browser and try again."  But as I stated before, If I add, for instance, an Excel sheet as a table, I can launch the app and see the web form for the Excel sheet, but receive the error for the SharePoint List.

    Please help, I've had this problem for months

    Any help would be appreciated

Page 1 of 2 (18 items) 12
Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
Translate This Page
Search
Archive
Archives