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

Configuring Database Backups

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

Set a new Application Database Server

Create an Access App

See Also

• SharePoint 2013 Portal
• SharePoint 2013 - Service Applications
• SharePoint 2013 - Resources for Developers
• SharePoint 2013 - Resources for IT Pros