Configure an environment for apps for SharePoint (SharePoint 2013)


This post is a culmination of white papers, TechNet articles, and blog posts to combine, into one place, the complete process to enable Access Services in a SharePoint Server 2013 farm. References to the articles are at the end.

Configuring Access Services involves a lot of players in the company: DNS, Active Directory, IIS, SQL 2012, and SharePoint Server 2013. Be prepared to coordinate with these groups for a successful deployment.

WARNING: This is a very lengthy post!

You will need a service account already being used in your environment or a new one for the following services:

  • Subscription Settings Service – either a new account or one that already exists

  • App Management Service – either a new account or one that already exists

  • Secure Store Service – either a new account or one that already exists

  • Access Services – you will need a new account as special permissions are required and you cannot use the SharePoint Farm account.

Sample Farm Topology

 Overview

Step 1: Configure the domain names in DNS (all hosting options)

This needs to be done by the DNS administrator for the domain.

Create a forward lookup zone for the app domain name 

  • Verify that the user account that performs this procedure is a local administrator on the domain controller.

  • Click Start, point to Administrative Tools, and then click DNS.

  • In DNS Manager, right-click Forward Lookup Zones, and then click New Zone….

  • In the New Zone Wizard, click Next

  • In the Zone Type page, accept the default of Primary zone, and then click Next

  • In the Active Directory Zone Replication Scope page, select the appropriate replication method for your environment (the default is To all DNS servers in this domain), and then click Next

  • In the Zone Name page, in the Zone name box type the name for your new app domain name (for example, ContosoApps.com), and then click Next

    The New Zone Wizard shows the new domain name for apps.

  • On the Dynamic Update page, select the appropriate type of dynamic updates for your environment (the default is Do not allow dynamic updates), and then click Next

  • On the Completing the New Zone Wizard page, review the settings, and then click Finish

Create a wildcard Alias (CNAME) record for the new domain name

  • Verify that the user account that performs this procedure is a local administrator on the domain controller.

  • In DNS Manager, under Forward Lookup Zones, right-click the new app domain name, and then click New Alias (CNAME).

  • In the New Resource Record dialog box, in the Alias name (uses parent domain if left blank) box, type *.

    The Fully qualified domain name (FQDN) box displays *. followed by the domain name that you created for apps. For example, *.ContosoApps.com or *.Contoso-Apps.com.

  • Next to the Fully qualified domain name (FQDN) for target host box, type the FQDN of the server that hosts the SharePoint sites.

    For example, SharePoint.Contoso.com.

    Or:

    • Next to the Fully qualified domain name (FQDN) for target host box, click Browse and navigate to the Forward Lookup Zone for the domain that hosts the SharePoint sites.

      For example, Contoso.com.

    • And then navigate to the record that points to the server that hosts the SharePoint site.

      For example, SharePoint.

    • Select the checkbox to allow any authenticated user to update DNS records                           

      New Resource Record dialog box shows the wildcard alias for the app domain and the FQDN of the server that hosts the SharePoint sites.

  • Click OK.

Verify the new domain name

Any user or administrator can verify the new domain name from any machine.

  • Verify that the user account that is performing this procedure is a local administrator on the domain controller.

  • Click Start, and then click Command Prompt.

  • At the command prompt, type ping followed by a subdomain of the domain that you created, and then press ENTER.

    For example, ping Apps-12345678ABCDEF.contosoapps.com

    If the ping command returns the correct IP address, then your wildcard for the domain name was configured successfully.

Step 2: Create a Service Account for Access Services

This procedure requires a domain administrator or someone who has access to Active Directory Users and Computers and can create a user.

 Create an Access Services Service Account

  • Launch Active Directory Users & Computers

  • Expand the domain

  • Right click the Users container, choose New, then User

  • Create a SharePoint Access service account

  • Click Next

  • Type in a password and select

    • User must change password at next logon

    • Password never expires (optional)

  • Click Next then Finish

 

 Step 3: Create a new instance on SQL Server 2012 for Access Services

These steps are performed by the SQL DBA.

SQL Server 2012 is a requirement for the successful operation of Access Services in SharePoint Server 2013.

Every Access app creates its own database on SQL Server. In SharePoint Server 2013, SQL Server 2012 is the only version of SQL Server that can serve as the SharePoint Server 2013 application database server for Access Services.

In this set-up, we are assuming a single on premise SharePoint 2013 farm where the SharePoint Services Content and Configuration Databases are stored on the same SQL Server 2012 server that Access Services uses as its application database server.

Create a new instance for the Access databases

Because Access creates its own databases on SQL Server, I feel it is better to have these in their own instance instead of being with the rest of the SharePoint databases. You will need to have access to the SQL Server 2012 media.

  •  From the Start menu, select Microsoft SQL Server 2012
  • Expand Configuration Tools and select SQL Server Installation Center (64-bit)

  • Select Installation

  • Select New SQL Server stand-along installation or add features to an existing installation

  • Browse to where the SQL Server 2012 media is located

  • Click OK at the Setup Support Roles

  • Click Next at Product Updates

  • Click Next at Setup Support Rules

  • Ensure the radio button is selected for Perform a new installation of SQL Server 2012, click Next

  • Click Next at Product Key

  • Accept the License Terms, click Next

  • Ensure the radio button is selected for SQL Server Feature Installation, click Next

  • On the Feature Selection page, select the following settings

    • Database Engine Services

    • Full-Text and Semantic Extractions for Search

    • Click Next

 

  •  Click Next on Installation Rules
  • On the Instance Configuration page, select Named Instance and provide a meaningful name for the Access databases. In my case I called the named instance Access

  • Click Next

 

  •  Click Next on Disk Space Requirements
  • Ensure Startup Type is set to Automatic on Server Configuration for SQL Server Agent

  • Click Next

  • Select Mixed Mode (SQL Server authentication and Windows authentication) on the Database Engine Configuration page, Server Configuration tab

  • Enter the password for the SQL Server System Administrator (SA) account

  • Add a SQL Server administrator account

  • Select the Data Directories tab and configuration the locations for the various directories

  • Click Next

   

  • Select whether or not to use Error Reporting and click Next

  • Click Next on Installation Configuration Rules

  • Select Install

  • Once installation is complete, follow any on-screen instructions (if any) then select Close to complete the installation

    • In my case, I needed to install Service Pack 2 so I went to Windows Update, checked for updates, and installed the required and recommended updates

 Add Server Roles to the Access Services Instance

The Access Services Service account and the SharePoint farm account need specific server roles on the Access instance created earlier 

  • Launch SQL Server Management Studio

  • On the Connect to Server dialog box, click the down arrow in the Server Name section and select <Browse for more…>

  • Expand Database Engineand choose the Access instance created earlier and click OK then Connect

   

  • Once you are connected to the Access instance, expand Security and Logins

  • Right-click Logins and choose New Login

  • On the Login page, click Search

  • Change the Locations to the domain

  • Enter the Access Services Service account from the domain location and click Check Names, then OK

  • Select the Server Roles page and select the following roles

    • dbcreator

    • securityadmin

  • Follow steps 5-9 for the SharePoint Farm account

Configure SQL Server for Access Services

There are several properties on the SQL Server 2012 Access instance that need to be configured. The following sections provide step-by-step guidance for configuring SQL Server 2012 Access instance for the required settings for Access apps.  

  • Open SQL Server Management Studio (SSMS).

  • Connect to the Access instance

  • Right-click the Access instance in Object Explorer and then select Properties.

  • In the Server Properties dialog box, click Security.

  • Select SQL Server and Windows Authentication mode.

  • Do not close the server properties

  • Select Advanced page

  • Select the dropdown arrow in the Enable Contained Databases row and then select True

  • Select the dropdown arrow in the Allow Triggers to Fire Others row and then select True

  • Select the dropdown arrow in the Default Language row and then select English

  • Set Max Degree of Parallelism to 1

  • Click OK to close

 

Set permissions on proc_putObjectTVP in the Config database

The Access Services Service Account must have permissions to execute the store procedure called proc_putObjectTVP in the configuration database. The account needs the SPDataAccess role.

  • In SQL Server Management Studio, connect to the SQL instance that has the SharePoint databases and expand the SharePoint_Config Database

  • Expand Security, then Users

  • Add the Access Services Service Account

  • Choose Windows User

  • Select the domain

  • Click on the Membership page

  • Click the box for SPDataAccess

  • Click OK

 Configure SQL Server protocols

You must enable TCP/IP and Named Pipes protocols in the SQL Server Network Configuration.  

  • Open SQL Server Configuration Manager and select Protocols for MSSQLSERVER to enable both protocols.

  • Right-click any disabled protocol and choose enable

  • Repeat steps 1-2 for the Access instance

 

By default, SQL Server enables TCP/IP during installation of SQL Server. If the TCP/IP status is not Enabled, enable it when you enable Named Pipes.

  • In SQL Server Configuration Manager, select SQL Server Services.

  • Right-click SQL Server (MSSQLSERVER), and then select Restart.

Right-click SQL Server (Access) and then select Restart

Configure Windows Firewall settings for SQL Server 2012

After you install SQL Server, you must set the following ports to communicate through Windows Firewall:

    • TCP 1433
    • TCP 1434
    • UDP 1434

To set the ports, take the following steps:

  • On SQL Server, type firewall in the Start search box and click Enter.

  • Select Windows Firewall with Advanced Security and press Enter to view the following dialog box:

  • Select Inbound Rules

  • Select the Action Menu and click New Rule. The Rule Type page opens.

  • Select Port as the rule type and then click Next. The Protocol and Ports page opens.

  • Select TCP.

  • Select Specific local ports: and enter 1433.

  • Click Next. The Action page opens.

  • Select Allow the connection.

  • Click Next. The Profile page opens.

  • Select Domain, selectPrivate, and then click Next. The Name page opens.

Note:

Depending on your needs, you may also want to open the port to the Public.

  • In the Name text box enter a name for the port, for example: TCP 1433.

  • Click Finish.

  • Repeat Steps 1 through 13 for TCP 1434 (enter 1434 in the text box that is next to Specific local ports and use TCP 1434 as the name for the port).

  • Repeat Steps 1 through 5.

  • Select UDP instead of TCP.

  • Select Specific local ports: and enter 1434.

  • Repeat steps 8 through 11.

  • Enter a name for the port, for example: UDP 1434.

You will see the entries in your Inbound Rules dialog box when you are done, as shown in the following screenshot:


 

Step 4: Configure the Service Applications

These steps are performed by the SharePoint 2013 administrator.

Apps rely on the App Management and Microsoft SharePoint Foundation Subscription Settings service applications. These service applications use the multi-tenancy features to provide app permissions and create the subdomains for apps. Therefore, even if you are not hosting multiple tenants, you must still establish a name for the default tenant for your environment (any SharePoint site that is not associated with a tenant will be in the default tenant).

 Start the Subscription Settings, App Management, & Access services in Central Administration

  • Verify that you are a member of the farm administrators group in Central Administration.

  • In SharePoint 2013 Central Administration, click System Settings.

  • On the System Settings page, under Servers, click Manage services on server.

  • On the Services on Server page, select the server that will be handling the service, then next to Access Database Service 2010, click Start.

    • NOTE: This is optional only if the organization will be using Access 2010

  • On the Services on Server page, select the server that will be handling the service, then next to Access Services, click Start.

  • On the Services on Server page, select the server that will be handling the service, then next to App Management Service, click Start.

  • On the Services on Server page, select the server that will be handling the service, then next to Microsoft SharePoint Foundation Subscription Settings Service, click Start.

  • Verify that the App Management and Microsoft SharePoint Foundation Subscription Settings services are running. The following illustration shows the Services on Server page where you can verify that the services are running.

Ensure you have the right server for these services to be running.


Configure the Subscription Settings Service Application by using Windows PowerShell

If the Subscription Settings Service application is already created, skip to “Configure Permissions for the Subscription Settings Service Application 

  • Logon as the SharePoint Administrator account and ensure that account has the following memberships:

    • securityadmin fixed server role on the SharePoint SQL Server instance.
    • db_owner fixed database role on all databases that are to be updated on the SharePoint SQL Server instance.
    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 15 Products cmdlets.

Note:

If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  • On the Start menu, click All Programs.

  • Click Microsoft SharePoint 2013 Products.

  • Right-click SharePoint 2013 Management Shell and choose Run As Administrator

  • Open the SharePoint PowerShell command prompt, type the following commands, and press ENTER after each one to create the application pool:

    # Get the name of the managed account and sets it to the variable $account for later use.

    $account = Get-SPManagedAccount "<AccountName>"

    Where:

    <AccountName> is the name of the managed account in the SharePoint farm in domain\username format.

    # Create an application pool for the Subscription Settings service application.

    # Use a managed account as the security account for the application pool.

    # Stores the application pool as a variable for later use.

    $appPoolSubSvc = New-SPServiceApplicationPool -Name “Subscription Settings Service Application” -Account $account

  • At the Windows PowerShell command prompt, type the following commands, and press ENTER after each one to create the new service application and proxy:

    # Create the Subscription Settings service application, using the variable to associate it with the # application pool that was created earlier.

    # Stores the new service application as a variable for later use.

    $appSubSvc = New-SPSubscriptionSettingsServiceApplication -ApplicationPool $appPoolSubSvc -Name “Subscription Settings Service App” –DatabaseServer <sqlserver\instance> -DatabaseName <Subscription_Settings_Service_DB>

    Where:

    <SettingsServiceDB> is the name of the Subscription Settings service database.

    Example: Subscription_Settings_Service_DB

    <sqlserver\instance> is the name of the SQL Server 2013 instance for the SharePoint databases

    Example: SP2013-SQL

    # Create a proxy for the Subscription Settings service application.

    $proxySubSvc = New-SPSubscriptionSettingsServiceApplicationProxy -ServiceApplication $appSubSvc

  • You can verify this by going to Central Admin, Application Management, and Manage Service Applications.


 

Configure permissions for the Subscription Settings Service Application

  • Click to the right of the Subscription Settings Service Application and select Permissions from the Ribbon

  • Add the Access Services Service account with Full Control permissions.

 

Configure the App Management service application by using Windows PowerShell

If the App Management Service Application is already configured, skip to “Configure Permissions for the App Management Service

  • Logon as the SharePoint Administrator account and ensure that account has the following memberships:

    • securityadmin fixed server role on the SharePoint SQL Server instance.

    • db_owner fixed database role on all databases that are to be updated on the SharePoint SQL Server instance.

    • Administrators group on the server on which you are running the Windows PowerShell cmdlets.

      An administrator can use the Add-SPShellAdmin cmdlet to grant permissions to use SharePoint 15 Products cmdlets.

Note:

If you do not have permissions, contact your Setup administrator or SQL Server administrator to request permissions. For additional information about Windows PowerShell permissions, see Add-SPShellAdmin.

  • On the Start menu, click All Programs.

  • Click Microsoft SharePoint 2013 Products.

  • Click SharePoint 2013 Management Shell.

  • At the Windows PowerShell command prompt, type the following commands, and press ENTER after each one to create the application pool:

    # Get the name of the managed account and sets it to the variable $account for later use.

    $account = Get-SPManagedAccount "<AccountName>"

    Where:

    <AccountName> is the name of the managed account in the SharePoint farm using domain\username as the format of the account name.

    # Create an application pool for the Application Management service application.

    # Use a managed account as the security account for the application pool.

    # Stores the application pool as a variable for later use.

    $appPoolAppSvc = New-SPServiceApplicationPool -Name “App Management Service Application” -Account $account

  • At the Windows PowerShell command prompt, type the following commands, and press ENTER after each one to create the new service application and proxy:

    # Create the Application Management service application, using the variable to associate it with # the application pool that was created earlier.

    # Stores the new service application as a variable for later use.

    $appAppSvc = New-SPAppManagementServiceApplication -ApplicationPool $appPoolAppSvc -Name AppServiceApp –DatabaseServer <sqlserver\instance> -DatabaseName <AppServiceDB>

    Where:

    <AppServiceDB> is the name of the App Management service database.

    Example: App_Management_Service_DB

    <sqlserver\instance> is the name of the instance housing the SharePoint databases

    Example: SP2013-SQL

# Create a proxy for the Application Management service application.

$proxyAppSvc = New-SPAppManagementServiceApplicationProxy -ServiceApplication $appAppSvc

  • You can verify this by going to Central Admin, Application Management, Manage Service Applications


 

Configure permissions for the App Management Service Application

  • Click to the right of the App Management Service App and choose Permissions

  • Add the Access Services Service account with Full Control permissions

 

Configure Access Services & Access Services 2010 (backward capability) using Central Admin

For this step, we will be using the Access Services Account created by the domain administrator earlier. However we need to add the account to the managed accounts set up for SharePoint

 

  • In Central Admin, go to Application Management, Manage Service Applications

  • Click the New button and choose Access Services

  • Provide a name such as Access Services Application

  • Enter the name of the SQL Server\instance for the Access instance on SQL Server 2012

  • Create a new application pool

  • Select the Access Services Service Account in the drop-down under Configurable

  • Click OK

 

  • Follow steps 1-7 to create the Access Services 2010 Application (optional) creating an appropriate name and using the Access Services Service Account.

 

Configure permissions for the Access Services Application

While still on the Manage Service Applications page, we need to grant the Access Services Service Account full control to the Access Services Application database.

  •  Click to the right of Access Services Application and choose Permissions button
  • Add the Access Services Service Account with Full Control permissions.

  • Click OK

 

 

  • Follow steps 1-3 for the Access Services 2010 Application

 Set a new application database server

During SharePoint Server setup, you are prompted to assign a configuration database server for the SharePoint_Config database. By default, SharePoint Server assigns that same server as the application database server. If that server happens to be SQL Server 2008 R2, or if you will be using a separate instance of SQL Server 2012, you must assign a new application database server so that Access Services can point to a SQL Server 2012 instance.

  • Go to Central Admin, Application Management, Manage Service Applications

  • Click on Access Services Application

  • The Manage Access Services 2013 screen appears, displaying the default settings for Session Management and Memory Utilization.

  • Click New Application Database Server to expand the choices:

  • Enter the name of the SQL Server 2012 instance you want to use.

    • This is the SQL instance created earlier for Access

  • Select Windows authentication.

    Note: The account configuring this must have dbcreator and securityadmin privileges on the SQL Server.

    If you have multiple instances of SQL Server 2012, you can assign multiple Application Database Servers for Access Services by using PowerShell.

Configure the Secure Store Service Application

The Secure Store Service provides capability to store data (e.g. credential set) securely and associate it with a specific identity or group of identities. The SharePoint Secure Store Service manages authentication and authorization for Access apps. 

NOTE: If the Secure Store Service application is already configured all you need to do is to Refresh Key and skip this section. 

  • Open SharePoint Central Administration.

  • Select Application Management.

  • Select Manage Service Applications.

  • Click New and select Secure Store Service

  • Provide a Service Application Name such as Secure Store Service Application

  • Ensure the Database Server name is correct

  • Leave the default Database Name

  • Provide a Failover Database Server name if applicable

  • Either use an existing application pool or create a new application pool

  • Select the managed account for the Secure Store Service

  • Select whether to enable auditing. If selected, the audit log is required to be a number of days between 1 and 365

  • Click OK

 Generate the Secure Store Application Security Key

Access Services requires the Secure Store Service to be started and enabled. Access Services requires you to generate a Secure Store Service security key for it to run properly. 

Set the key for the Secure Store Service Account by following these steps:

  • Open SharePoint Central Administration.

  • Select Application Management.

  • Select Manage Service Applications.

  • Select Secure Store Service.

  • Click Generate a New Key.

  • Enter a Pass Phrase. The Pass Phrase for the key does not have to be the same as the one you entered when you installed SharePoint Server.

 

Configure permissions for the Secure Store Service Application

  • Go to Central Admin on Manage Services Applications

  • Click to the right of Secure Store Service Application and choose Permissions from the ribbon

  • Add the Access Services Service account with Full Control permissions

 

Step 5: Add the Access Services Service Account to Managed Accounts

This procedure is performed by the SharePoint Administrator account on the SharePoint Server 

  • In Central Admin, go to Security and choose Configure Managed Accounts

  • Select Register Managed Account

  • Type the Access services account in the form of domain\username

  • Type the Password

  • Click OK

 

 

Step 6: Configure Apps

These steps are performed by the SharePoint 2013 administrator. 

The app URL points to your app domain and a prefix that determines how each app is named. The following steps will configure app URLs for non-hosting environments by using Central Admin.

 Configure app URLs

  • In Central Administration, click Apps.

  • On the Apps page, click Configure App URLs.

  • In the App domain box, type the isolated domain that you created for hosting apps.

    For example, ContosoApps.com or Contoso-Apps.com.

  • In the App prefix box, type a name to use for the URL prefix for apps.

    For example, you could use “apps” as the prefix so that you would see a URL for each app such as “apps-12345678ABCDEF.ContosoApps.com”. The following illustration shows the Configure App URLs page after you have filled in the App domain and prefix.

    The Configure App URLs page in Central Administration shows the App domain and App prefix.

  • Click OK.

  • If you will install apps and you have changed the App prefix from a previous name (also known as the site subscription name), you must perform additional steps that involve restarting the World Wide Web Publishing Service (WWW Service) that hosts the apps.

Important:

Restarting the WWW Service will also restart the IIS Admin Service and the Windows Process Activation Service. This will also shut down all Web sites and applications that depend on these services and they may lose existing state and will be unavailable until the services successfully restart. You should plan to perform these steps during a planned maintenance time.
To complete the App prefix rename tasks, perform these steps:

a)      Stop the SharePoint Timer service.

b)      Restart the World Wide Web Publishing Service that hosts the apps.

c)      Start the SharePoint Timer service.

 

Step 7: Install Required SQL Server 2012 Components on SharePoint Server 2013

This procedure is done by the SharePoint Administrator on the SharePoint Server(s) that will provide Access Services. 

In order for Access Services to function properly, it is highly recommended that you install the following SQL Server 2012 Feature Pack components on the SharePoint Server 2013 computer:

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

    You can download the Microsoft SQL Server 2012 Feature Pack components from the Microsoft Download Center. The files are also available as an attachment to this post. 

    The SQL Server 2012 Feature Pack components are needed for various essential features of Access 2013. LocalDB and the Load User Profile setting are needed for reading from external SharePoint lists and the Native Client is needed for loading saved app packages. 

    You will need to install all five (5) components on the server(s) where Access Services will be available. 

Step 8: Grant permissions to the config cache

The Access Services Service Account must have permissions to the config cache.  

  • On the SharePoint servers, open Windows Explorer and browse to C:\ProgramData\Microsoft\SharePoint\

  • Right-click the Config folder and choose Properties

  • On the Security tab, click Edit

  • On Permissions for Config dialog box, click Add

  • Enter the Access Services Service Account object and click Check Names

  • Click OK

 

  •  Back on the Permissions for Config, ensure the Access Services Service account is highlighted then remove all permissions EXCEPT Read and Write in the allowed column.

 

  • Click OK two times to exit the properties of the Config folder.

  • Repeat Steps 1-8 on each of the SharePoint servers in the farm

Step 9: Set permissions on the Web Application Database for the Access Services Service Account

The identity for the application pool that is running Access Services must have permissions to the web application.  

Launch the SharePoint Management Shell as Administrator and run the following script 

$w = Get-SPWebApplication PortalName

$w.GrantAccessToProcessIdentity(“domain\username”)

$w.Update() 

Where:

PortalName is the name of the site where Access Services will be configured

Domain\username is the name of the domain and the Access Services Service Account 

EXAMPLE

$w = Get-SPWebApplication http://portal.fabrikaminc.local

$w.GrantAccessToProcessIdentity("fabrikaminc\spaccess")

$w.Update()

 

Step 10: Create an empty Web Application

This procedure is run on the SharePoint server with a SharePoint Administrator account.

Running Apps in SharePoint 2013 require a web application that doesn’t use host headers in order for the redirect to the apps domain to function properly. This web app will not have any site collections in it.

  • In Central Admin, go to Manage Web Applications

  • Click New

  • Create a new IIS web site and provide a Name

  • Change the Port to 80

  • Leave the Host Header field blank

  • Scroll down to the Application Pool section and create a new application pool

  • Change the managed account to the correct account

 

  • Click OK

WARNING!!!!! If this fails and you receive this error, follow the work-around.

 Work-Around

  • Follow steps 1-4

  • In step 5, put in the name of the server that is running the App service

   

  • Continue with steps 6-8

  • Open IIIS Manager and expand Sites

  • Select the site created for Apps and choose Bindings

  • Select the binding and choose Edit

  • Delete the Host Name and leave it blank, click OK then Close

 

 NOTE: Follow steps 4-7 on all the SharePoint servers in the farm

Step 11: IIS Application Pool Load User Profile Setting

This procedure is performed by the SharePoint Administrator. 

A change to the IIS Application Pool for Access Services is necessary for you to be able to open linked SharePoint tables. You must set the Load User Profile setting to true because ADS requires a user profile to load LocalDB. A restart of the server(s) is necessary after you change the setting. 

Important:

The following steps will need to be performed on all the SharePoint Servers that have the same Access Services Application App ID in Internet Information Services (IIS) Manager.

  •  Click Start and type IIS. Select Internet Information Services (IIS) Manager.
  • Select the server name and click the + (plus) sign to expand the tree.

  • Select Application Pools.

  • Determine which App Pool to modify

    • Sort the Application Pools by their Identity by selecting the Identity column header

    • Locate the Identity of the Access Services Service Account

    • Right click the application pool and select View Applications

    • Expand the Physical Path

      • If the physical path shows AccessDataServices, this is the application pool you want to modify as it’s for Access Services

      • If the physical path shows AccessDataServer, this is the application pool for the Access Services 2010 application and you DO NOT want to modify this app pool.

  • Once you’ve located the correct App Pool, right click the application pool and select Advanced Settings…

  • In the Process Model section, click the dropdown for the Load User Profile setting and select True.

  • Click OK and restart the server.

Step 12: Configure permissions on the App Management Database

While the SharePoint servers are rebooting, have the SQL Server Admin account run this procedure on the SQL Server 2010 Instance housing the SharePoint databases.

  • Launch SQL Server Management Studio

  • Connect to the instance housing the SharePoint databases

  • Expand security

  • Expand Logins

  • Double-click the Access Services Service account

  • Select User Mapping on the left

  • Select the App Management Service database

  • Add the following permissions for the Access Services Service account

    • Db_owner

    • Db_securityadmin

    • SPDataAccess

 

 

 

 Step 13: Create an Access app

The procedures for this need to be done on a client machine as a regular user. 

If you haven't already done so, install Office 2013 on a computer other than the server that is running SharePoint Server 2013. Office 2013 requires Windows 7 or Windows 8. 

  • Start Access 2013 and click Custom web app.

  • In the Web Location box, enter the URL of the site where you want to install the app.

    • Example: http://portal.fabrikaminc.local/sites/IT

  • Click Create


     

  • A dialog box will show it is connecting to the web service

  • You will be prompted to enter your credentials

You have successfully created an Access app and all you have to do is add tables and configure it.

NOTE: This post does not go into how to configure the actual tables in Access Services.

Summary

As you can tell, there are lots and lots and lots of steps with coordination among several people to ensure a successful deployment of Access Services.

 Resources

Access Services 2013 Setup for an On-Premises Installation

Set up and configure Access Services for Access apps in SharePoint Server 2013

Configure an environment for apps for SharePoint (SharePoint 2013)

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

Enable apps in AAM or host-header environments for SharePoint 2013

Additions

How to: Create and customize a web app in Access 2013

A special shout-out goes to Mark Kordelski & David Wollerman - my colleagues @ Microsoft

SQL2012FeatureComponents.zip

Comments (7)

  1. SharePoint 2013 Administration training Online says:

    I absolutely love your blog and find nearly all of your post’s to be precisely what I’m looking for.
    http://staygreenacademy.com">SharePoint 2013 Administration training Online

  2. sharepoint 2013 videos says:

    Thanks so much! Your instructions are very clear and helpful. I've been wondering how to do this for months and thrilled to find out how finally!
    http://staygreenacademy.com/sharepoint-videos-training-tutorial/">sharepoint 2013 videos

  3. pgo says:

    wow - very concise! thanks!

  4. Richard says:

    Thanks, but I can't see the forest for the trees.

    Step 10 - Create an Empty Web Application on Port 80 - But without a host header you can't run two sites on Port 80. In the picture it shows a sharepoint setup in IIS and I get an error on hitting OK on those windows. So now there is an empty website (running
    on a different port), but the AccessDataServices application is running under a completely different application pool, that isn't assigned to this or any website. So what is the empty web application for? How does Access use this empty web application? should
    the Access Data Services application be re-assigned to the application pool of the empty site?

    Step 13 - Magically the web location is a completely different site. I can't understand why/how that would work. Is it connecting to the access services, or a place where it would publish the app? Surely not publishing to the empty web application.

    I have had no success creating a web app within Access, but some success creating an Access app within a sharepoint site, and opening that in Access to design and re-deploy. The Web Location is such a nebulous object and I need a better explanation of how the
    Empty Web Application, Application Pool, and sites relate to it.

  5. RC says:

    Thanks for the guide. Are you able advise if I need to create the empty web application even if I'm using a HNSC setup? My gut tells me no since the existing single web app should be able to handle the new app domain. If I do create a new web app, I run
    into the same issue as described by Richard.

    Thank you.

  6. nelson says:

    I have both host header web applications and one web application using HNSC. However, It appears that with Kerberos on the webapp that uses HNSC the users are getting 401's since the SPN for the APP url does not exist. Any one else have this same problem?

Skip to main content