Table of Contents




Overview

NB! This Wiki is  based on a previously released white note paper outlining Access Services 2013.
Access Services in SharePoint Server 2013 allows people to host Access databases in SharePoint within the context of an Access app. Access apps for SharePoint are new in SharePoint Server 2013 and you will build them using the Access 2013 desktop client.  You can create, edit, and update linked Access 2013 databases and then view them directly from the app.

You can also use Access Services to view and edit a web database that was created using Access 2010 and SharePoint 2010, and you can republish them to SharePoint Server 2013.  You can't create a web database using Access Services in SharePoint 2013, but you can import Access 2010 web databases into an Access app. 

Access apps are SharePoint apps, therefore for Access Services to run Access app, SharePoint Server 2013 needs to be configured as a SharePoint app server. In addition, Access Services requires SQL Server 2012 to run.

Prerequisites

  • SharePoint Server 2013 installed on a Windows 2008 R2 Server or higher
  • SQL Server 2012 Standard or SQL Server 2012 Enterprise
  • The following SQL Server 2012 Feature Pack Components on the SharePoint server:
    • SQL Server 2012 Local DB
    • SQL Server 2010 Data-Tier Application Framwork
    • SQL Server 2012 Native Client
    • SQL Server 2012 Transact-SQL ScriptDom
    • System CLR Types for SQL Server 2012
 

Configure SQL Server 2012

Each 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. .  For installing SQL Server 2012, refer to the article Installation for SQL Server 2012  . 

The following configuration example is based on a previously released white paper,  and describes a  single on-premises Farm setup where the Service Applications and the Configuration Database are stored on the same database server that Access Services uses as its application database server.
 

Required SQL Server 2012 settings for Access apps

  • SQL Instance Feature Selections
    • Database Engine Services
    • Full-Text and Semantic Extractions for Search
    • SQL Management Tools feature ( for troubleshooting)
    • Client Tools connectivity
  • Security Mode = Mixed (SQL Server & Windows Authentication)
  • SQL Instance Properties
    • Enable Contained Databases = True
    • Allow Triggers to Fire Others = True
    • Default Language = English
  • SA password
  • The SA account running Access Services must have the following roles on the SQL Server Security Logins table:
    • dbcreator
    • securityadmin
     
To open the SQL Server Security Logins table, open SQL Server Management Studio for the SQL instance. Expand the Server Objects. Under Security, select Logins. 

Expand Logins and locate the SharePoint Service Account. Then right-click the account name and select Properties . Select Server Roles . 

Configuring SQL Server for Access Services

 

Security mode

If you have installed SQL Server 2012 using Windows Authentication Mode you need to change the mode as follows:
  1. Open SQL Server Management Studio (SSMS).
  2. Right-click the server name in Object Explorer and then select Properties.
  3. In the Server Properties dialog box, click Security.
  4. Select SQL Server and Windows Authentication mode.

Setting the SQL Server Enable Contained Databases, Allow Triggers to Fire Others, and Default Language  properties

To set the Enable Contained Databases property:
  1. Open SSMS
  2. Right-click the server name in Object Explorer and then select Properties.
  3. Select Advanced.
  4. Select the dropdpwn arrow in the Enable Contained Databases row and then select True.
  5. Select the dropdown arrow in the Allow Triggers to Fire Others row and then select True.
  6. Select the dropdown arrow in the Default Language row and then select English. 

Protocols setup

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. 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.
  1. In SQL Server Configuration Manager, select SQL Server Services. 
  2. Right-click SQL Server(MSSQLSERVER), and then select Restart.

Configuring Windows Firewall settings for SQL Server 2012

After you have installed SQL Server, you must set the following ports to communicate through Windows Firewall:
  1. TCP 1433
  2. TCP 1434
  3. UDP 1434
To set the ports, take the following steps:
  1. On the SQL Server host Windows server computer, type firewall in the Start search box and click Enter.
  2. Select Windows Firewall with Advanced Security and press Enter
  3. Select Inbound Rules
  4. Select the Action Menu  and click New Rule. The Rule Type page opens.
  5. Select Port as the rule type and then click Next. The Protocol and Ports page opens.
  6. Select TCP.
  7. Select Specific local ports: and enter 1433.
  8. Click Next. The Action page opens.
  9. Select Allow the connection.
  10. Click Next. The Profile page opens.
  11. Select Domain, select Private, and then click Next. The Name page opnes.
  12. In the Name text box, enter a name for the port, for example: TCP 1433.
  13. Click Finish.
  14. Repeat Steps 1 through 13 for TCP 1434
  15. Repeat Steps 1 through 5
  16. Select UDP.
  17. Select Specific local ports: and enter 1434
  18. Repeat steps 8 through 11.
  19. Enter a name for the port, for example: UDP 1434
     When you are done the entries, TCP 1433, TCP 1434 and UDP 1434 will be listed in your Inbound Rules dialog.

Required SQL Server 2012 Components on SharePoint Server 2013

In order for Access Services to function properly, it is 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 Applications 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. You will also need to conffigure the Load User Profile Setting in IIS, as described below.
      

IIS Application Pool Load User Profile Setting

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 is necessary after you change the setting:
  1. Click Start and type IIS. Select Internet Information Services (IIS) Manager.
  2. Select the server name and click the + (plus) sign to expand the tree.
  3. Select Application Pools.
  4. If you installed bot Access Services and Access 2010 Services, you will see 2 Application Pools with GUID's for their names. The Access Services Application Pool contains multiple applications. The Access Services 2010 application pool contains only one application. Select the Access Services Application Pool that has a GUID and multiple applications.
  5. Right-click and select Advanced Settings.
  6. In the Process Model section, click the dropdown for the Load User Profile setting and select True.
  7. Click OK and restart the server.
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.

Database backup

If you have not already done so, you will likely want to set up backups of your SQL databases. Even if Access Services uses the same instance SQL Server 2012 as SharePoint Server 2013, simply configuring SharePoint backup will not back up Access data; you will need to configure SQL Server backup. If you are unfamiliar with setting up SQL database backups, refer to the Technet article Back up and Restore of SQL Server Databases  .

Configure Access Services

Before you start configuring Access Services, make sure that SharePoint 2013 is properly installed and that you have configured it for SharePoint apps. 
If you haven't configured your SharePoint installation for SharePoint apps, follow the directions in the Technet articleConfigure an environment for apps for SharePoint 2013  . As the article states, you must set up a Domain Name Services (DNS) domain name to provide a host name for installed apps. You must also create a DNS record so that the domain name will resolve correctly

Basic Configuration Steps

When you have configured SharePoint 2013 for SharePoint apps, follow these basic steps to configure Access services:   
  1. Enable the following required services
    1. Secure Store Service
    2. Access Services
    3. Access Services 2010
    4. App Management Service
    5. Microsoft Sharepoint Foundation Subscription Settings Service
  2. Generate s Security Key for the Secure Store Service
  3. Create a Site Collection
  4. Set permissions on the site
When the above steps have been completed, you should be ready to create a new Access Custom web app from your Office 2013 client. 

How to complete the basic configuration steps

Enabling required services

After having enabled SharePoint 2013 for apps, you must configure your SharePoint farm.
  1. Click Start
  2. Select Microsoft SharePoint 2013 products
  3. Select SharePoint 2013 Central Administration
  4. Click Configuration Wizards
  5. Click Launch the Farm Configuration Wizard
The Welcome screen appears and asks how you want to configure your SharePoint farm
  1. Click the Start the Wizard button
  2. Click Yes to start the wizard
  3. The Configuration Wizard opens  and then runs tasks to provision the SharePoint farm. Click Finish then the tasks are done.
  4. Select Use existing managed account
Access Service requires a minimum of the following services:
  • Access Services 2010 -  Enables viewing, editing, and interacting with Access Services 2010 databases in a browser. Note that as long you have started the Access Services service, you can publish existing Access 2010 Web apps on SharePoint Server 2013.
  • Access Services - Enables viewing, editing, and interacting with Access Services databases in a browser
  • App Management Service - Enables you to to install SharePoint apps from the Office Marketplace or the Corporate Catalog and is required for running any Access app.
  • Microsoft SharePoint Foundation Subscription Settings Service - This service does not appear in the list of services in the Farm Configuration wizard. However, if you manually add services, you must make sure to start this service.
  • Secure Store Service -  Provides capability to store data securely and associate it with a specific identity or group of identities. The SharePoint Secure Store Service manages authentication and authorization for Access apps.
  1. Select the necessary services and click Next. A SharePoint message shows you that it is worjing on the configuration of the services
  2. When the configuration of the services is done, click Create a Site Collection to create the site collection for Access apps.

Creating a site collection

You must create a SharePoint site collection to manage your Access apps. After SharePoint 2013 Server finishes the services, it prompts you to create a site collection. If it does not, go to Central Administration and select Create Site collection under Application Management.
 
  1. Enter a title for the site. The title will appear on the page for the site, but it's not part of the URL address. 
  2. Provide the website address:
  3. In the Template Selection area, the experience version is 2013 by default.
    • Select Team Site
    • Click OK
    • Click Finish
  4. Test navigation of the URL you created.
 

Setting Permissions on the site

  1. Navigate to the site you created.
  2. Select the Page tab.
  3. On the ribbon, click Page Permissions.
  4. Select the appropriate group and then add users to it.
 

Generating Secure Store Security Service 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:
  1. Open SharePoint Central Administration.
  2. Select Application Management.
  3. Select Manage Service Applications.
  4. Select Secure Store Service.
  5. Click Generate a New Key.
  6. 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.
 

Create SQL Server 2012 Application Database Server

If you have already installed SharePoint 2013 using a SQL Server 2008 R2 database, you must assign a separate SQL Server 2012 application database server for Access Services.
To create the server, open Central Administration.
  1. Select Manage Service Applications
  2. Select Access Services.
  3. Click New Application Database server.
  4. Enter a SQL Server 2012 instance
  5. Select Windows authentication.
 

Access Apps

 In order to create an Access App, you need to have Office 2013 installed on a computer running on Windows 7 or Windows 8.
To create an app, do as follows:
  1. Start Access 2013, and from the list of available templates, select Custom Web App
  2. In the Custom web app dialog, enter a name for your app, and add the url to the site that you created in theCreating a site collection step above.
  3. Click Create.
To further build up your app with tables or schema, or to get an overview of alternate ways to create or download Access Apps, please refer to the following blog post from the Office 2013 Access Team:  http://blogs.office.com/b/microsoft-access/archive/2012/08/20/4-ways-to-create-access-apps.aspx