Revision #9

You are currently reviewing an older revision of this page.
Go to current version

Overview

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

Setting SQL Server 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 property

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.

Setting the SQL Server Allow Triggers to Fire Others property

To set the Allow Triggers to Fire Others property:

  1. Open SSMS.
  2. Right-click the server name in Object Explorer and then select Properties.
  3. Select Advanced.
  4. Select the dropdown arrow in the Allow Triggers to Fire Others row and then select True.

Configuring 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. 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 to view the following dialog:

      

 

Configure Access Services

Set a new Application Database Server

Create an Access App

 


See Also

Revert to this revision