Access Services 2013 Setup for an On-Premises Installation

發行項
07/14/2013

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”.

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

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.

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.

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

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.

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.

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.

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

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.

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

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()

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).
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.
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.
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

Comments

Anonymous
April 01, 2014
The comment has been removed
Anonymous
April 01, 2014
The comment has been removed
Anonymous
April 10, 2014
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?
Anonymous
April 10, 2014
The comment has been removed
Anonymous
April 10, 2014
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.
Anonymous
April 10, 2014
Glad you've got it figured out, Jacob. The database names are not configurable, you get those pretty GUIDs for no extra charge :)
Anonymous
April 10, 2014
One more thing: The identity for the application pool that is running Access Services (e.g. “contososp_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)
Anonymous
April 19, 2014
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.
Anonymous
April 27, 2014
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?
Anonymous
April 27, 2014
Ronaldo - Check the ULS logs. Turn logging up to verbose, attempt the operation, and then review the logs to look for clues.
Anonymous
May 12, 2014
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-e17c169a065fDate and Time: 5/13/2014 7:45:41 AMAny ideas?
Anonymous
May 23, 2014
Is the isolated app domain required for access services? I've only read that the isolation is for security purposes.
Anonymous
May 23, 2014
Justin - yes, the isolated app domain is required to support the app model, which Access Services is built upon.
Anonymous
June 13, 2014
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)
Anonymous
June 19, 2014
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 monthsAny help would be appreciated
Anonymous
June 20, 2014
Jason - I've moved to a new role focused on Azure, I don't work with SharePoint anymore, I wouldn't have a lab to try to repro your issue. The best advice I can give is to look in ULS logs for an indication of the error. You van also try posting on StackOverflow.com, or even open a support case. Apologies I can't help further.
Anonymous
July 08, 2014
Kirk, you ROCK. I spent half a day trying to figure out all the different things that were going wrong - then found your post. Followed it step by step, and it works! Thank you.
Anonymous
July 23, 2014
After following this post and the white paper, I still had no joy, but found in the logs the need to run this script:SP_CONFIGURE 'show advanced options',1RECONFIGUREGOSP_CONFIGURE 'contained database authentication',1GOSP_CONFIGURE 'show advanced options',0RECONFIGUREGOI also found an error in the logs and had to refresh the Secure Store Key (when I opened the service it had said 'key unobtainable').Hope this might help others on this torturous trail
Anonymous
October 06, 2014
The comment has been removed
Anonymous
November 07, 2014
Hi,reading your post helped me a lot setting up access services in our sharepoint. But when I try to create a new webapp I run in an error "Technical problem" all the time. Looking in ULS logs shows me a problem with the secure store: "ValidateCredentialClaims - Access Denied: Claims stored in the credentials did not match with the group claim for a group app."Do you have any idea what exactly is the problem here?
Anonymous
December 09, 2014
Thanks for the post. I was just looking similar configuration for SQL Server 2014 for Access Services in SharePoint Server 2013. Any pointer is highly appreciated.Thanks!
Anonymous
January 21, 2015
We tried the above steps but still we are getting error.(we are using sql server 2014)Sorry, something went wrongAn error has occurred.TECHNICAL DETAILSTroubleshoot issues with Microsoft SharePoint Foundation.Correlation ID: 4b2ae29c-b007-609f-8d1b-c610ac24b02dDate and Time: 1/22/2015 11:07:44 AM
- Anonymous
  April 19, 2016
  @Abdul. Did you by chance resolve this particular error? I am getting the same thing and cannot figure out how to get around it. Thanks!
Anonymous
April 28, 2015
@Teddy @Abdul - as I stated before, I am no longer focused on SharePoint and kind of out of the loop on where the product is in terms of updates and fixes. That said, I saw an email today that stated SQL Server 2014 has not yet been tested and thus is unsupported. The only currently supported on-premises configuration for Access Services 2013 is for SQL Server 2012, and that is stated in the whitepaper and in this blog as requirements.
Anonymous
April 28, 2015
Kirk,I had already read and followed this article too, but still I have no Access Services or Access Services 2010. I even reran the Prerequisite installer because it looked lik the AppFabric did not install correctly before. It did this timel and then I reran the Sharepoint install in repair mode. It said it repaired Access Services successfully, but it still doesn't appear when I go to enable it. I am kind of at a loss as to what else to try. Any suggestions.
Anonymous
April 29, 2015
@Gretchen - Access Services is only available in the Enterprise edition. See the feature chart:technet.microsoft.com/.../jj819267.aspx
Anonymous
May 13, 2015
Hi, I have SharePoint 2013 on premis with SQL Server 2014. Would any of the steps be different? Thanks
Anonymous
May 13, 2015
@Jean - to my knowledge, Access Services is currently not supported with SQL Server 2014. It is only supported with SQL Server 2012. social.technet.microsoft.com/.../12514.sharepoint-2013-access-services.aspx
Anonymous
June 15, 2015
The comment has been removed
Anonymous
September 02, 2015
We have a weird problem with Access 2013 on our Production and Pre-production SP2013 systems. I've followed the guides to get it working in test environvment successfully, but in any environment with AlwaysOn configured (SQL 2012 R2) when I add an Access App to the site we can see the database files being created in SQL but SQL gets incredibly slow, you cannot expand the databases list in SMS and eventually it times out. Then it attempts to roll back the creation and this also times out. Performance otherwise in SQL seems adequate for day to day SP tasks. There's nothing of note in the SQL logs and the two events I described above in the SP logs. Any idea why this might be?
Anonymous
October 14, 2015
Thank you.. great article.. Worked well for me.. :)
Anonymous
December 15, 2015
Hi Kirk. I have a question regarding the configuration and setup of Access services, specifically related to the pre-requisites. What are the ramifications of installing the SQL Server Local DB feature on each server? My concerns are with installing this onto each of our servers, as it appears to be an express or lite version of SQL server. Does this need to be installed on each server?Would it be possible to just designate one of our application servers to run the access services, or possibly two application servers and avoid installing this onto our web front-end servers? My concerns are in how this Local DB is actually used, and whether storage space for databases that are created by this would create any issues. I guess I'm not fully understanding the need for this feature to be installed on each server if the primary requirement is to have SQL Server 2012 as our main database. Do all the databases get created on that actual SQL server database? If so, what purpose does the Local DB serve?Any help would be appreciated!
Anonymous
January 30, 2016
Thank you. It was a very helpful post. However for wildcard record, I had to create CNAME (instead A record) and point it to main my SharePoint site FQDN. Also, I had to create a root site collection to avoid "script error registersod". And then, it all worked perfectly!

共用方式為