SharePoint 2013 - Access Services
Table of Contents
- Configure Access Services
- Access Apps
- See Also
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.
- 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
- 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
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.
- 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:
Expand Logins and locate the SharePoint Service Account. Then right-click the account name and select Properties . Select Server Roles .
- Open SQL Server Management Studio (SSMS).
- Right-click the server name in Object Explorer and then select Properties.
- In the Server Properties dialog box, click Security.
- Select SQL Server and Windows Authentication mode.
Setting the SQL Server Enable Contained Databases, Allow Triggers to Fire Others, and Default Language propertiesTo set the Enable Contained Databases property:
- Open SSMS
- Right-click the server name in Object Explorer and then select Properties.
- Select Advanced.
- Select the dropdpwn 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.
- In SQL Server Configuration Manager, select SQL Server Services.
- Right-click SQL Server(MSSQLSERVER), and then select Restart.
- TCP 1433
- TCP 1434
- UDP 1434
- On the SQL Server host Windows server computer, type firewall in the Start search box and click Enter.
- Select Windows Firewall with Advanced Security and press Enter
- 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, select Private, and then click Next. The Name page opnes.
- 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
- Repeat Steps 1 through 5
- Select UDP.
- Select Specific local ports: and enter 1434
- Repeat steps 8 through 11.
- Enter a name for the port, for example: UDP 1434
- 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)
- 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.
- 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.
- Right-click 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.
Back up and Restore of SQL Server Databases .
If you haven't configured your SharePoint installation for SharePoint apps, follow the directions in the Technet article Configure 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
- Enable the following required services
- Secure Store Service
- Access Services
- Access Services 2010
- App Management Service
- Microsoft Sharepoint Foundation Subscription Settings Service
- Generate s Security Key for the Secure Store Service
- Create a Site Collection
- Set permissions on the site
- Click Start
- Select Microsoft SharePoint 2013 products
- Select SharePoint 2013 Central Administration
- Click Configuration Wizards
- Click Launch the Farm Configuration Wizard
- Click the Start the Wizard button
- Click Yes to start the wizard
- The Configuration Wizard opens and then runs tasks to provision the SharePoint farm. Click Finish then the tasks are done.
- Select Use existing managed account
- 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.
- Select the necessary services and click Next. A SharePoint message shows you that it is worjing on the configuration of the services
- When the configuration of the services is done, click Create a Site Collection to create the site collection for Access apps.
- 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.
- Provide the website address:
- In the Template Selection area, the experience version is 2013 by default.
- Select Team Site
- Click OK
- Click Finish
- Test navigation of the URL you created.
- Navigate to the site you created.
- Select the Page tab.
- On the ribbon, click Page Permissions.
- Select the appropriate group and then add users to it.
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.
To create the server, open Central Administration.
- Select Manage Service Applications
- Select Access Services.
- Click New Application Database server.
- Enter a SQL Server 2012 instance
- Select Windows authentication.
To create an app, do as follows:
- Start Access 2013, and from the list of available templates, select Custom Web App
- In the Custom web app dialog, enter a name for your app, and add the url to the site that you created in the Creating a site collection step above.
- Click Create.