office 2013 – access services setup for an on-premises installation

Access Services Setup for an On-Premises Installation ©2013 Microsoft Corporation. All rights reserved. This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it. Some examples are for illustration only and are fictitious. No real association is intended or inferred. This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes. You may modify this document for your internal, reference purposes.

Office 2013 – Access Services Setup for an On-Premises InstallationMicrosoft CorporationOctober 2012Revised: February 2013Applies to: Office 2013 | SharePoint Server 2013 | Access 2013 | Access Services in SharePoint Server 2013 | SQL Server 2012

SummaryThis white paper provides instructions that will help you install and configure Access Services in SharePoint Server 2013. It includes instructions for configuring SQL Server 2012 and SharePoint Server 2013 for the proper functioning of Access Services.

© 2013 Microsoft Corporation. All rights reserved.

ContentsSummary..................................................................................................................................................................2

Overview..................................................................................................................................................................4

Software prerequisites for Access apps...............................................................................................................4

Configuring SharePoint Server for apps...............................................................................................................5

Configure SQL Server 2012 for Access Services........................................................................................................5

Required SQL Server 2012 settings for Access apps.............................................................................................5

Scripted installation.............................................................................................................................................7

Configuring SQL Server for Access Services.........................................................................................................7

Required SQL Server 2012 Components on SharePoint Server 2013.................................................................11

Configuring Database Backups..........................................................................................................................13

Configure Access Services......................................................................................................................................13

Basic configuration steps...................................................................................................................................14

How to complete the basic configuration steps.................................................................................................14

Windows PowerShell cmdlets............................................................................................................................19

Set a new application database server..................................................................................................................19

Create an Access app.............................................................................................................................................22


OverviewThis white paper assumes that you have installed SharePoint Server 2013 and that you have configured it for apps. The instructions in this paper apply to a configuration where SQL Server 2012 Enterprise is the ContentDB and the application database server for SharePoint Server 2013. If you are using a separate instance of SQL Server 2012 for Access Services, see the section Set a new application database server.

To install, set up, and provision Access Services in SharePoint Server 2013, you must perform the following major steps:

Configure SQL Server 2012 Enterprise software for Access Services

Configure SharePoint Server 2013 for SharePoint apps

Configure Access Services

Create a SharePoint Site collection for Access apps

Because an Access app is a SharePoint app, you must first configure SharePoint Server 2013 as a SharePoint app server so that Access Services can successfully run Access apps.

In addition, Access Services setup requires specific SQL Server 2012 configurations so that SQL Server 2012 can successfully store and manage the data for Access apps.

Note:In this article, unless otherwise stated, all references to Access Services apply to Access Services in SharePoint Services 2013.

Software prerequisites for Access appsThe following are the software prerequisites for Access Services in SharePoint Server 2013:

SharePoint Server 2013 on at least Windows 2008 R2

SQL Server 2012 Standard or SQL Server 2012 Enterprise

SQL Server 2012 Feature Pack Components on the SharePoint Server:

o Microsoft SQL Server 2012 Local DB (SQLLocalDB.msi)o Microsoft SQL Server 2012 Data-Tier Application Framework (Dacframework.msi)o Microsoft SQL Server 2012 Native Client (sqlncli.msi)o Microsoft SQL Server 2012 Transact-SQL ScriptDom (SQLDOM.MSI) o Microsoft System CLR Types for Microsoft SQL Server 2012 (SQLSysClrTypes.msi)

The following are the software prerequisites for creating and modifying Access apps:

Access 2013 (required for Access app design)

A web browser (required for viewing and updating data)


http://www.microsoft.com/en-us/download/details.aspx?id=29065

Configuring SharePoint Server for apps See the TechNet article, Configure an environment for apps for SharePoint 2013 for instructions.

As a brief overview, a SharePoint app requires the following:

SharePoint Server 2013

Installation of App Management Service

Startup of Microsoft SharePoint Foundation Subscription Settings Service

Domain Name Services (DNS) domain name to provide a host name for the apps

DNS record

Currently running Office (spadmin) and SharePoint Timer (sptimer) services

SharePoint Managed Account membership in the Administrators group on the server on which you run Windows PowerShell cmdlets

The app prefix and the app domain name are entered in the Configure App URLs page in SharePoint Central Administration

Configure SQL Server 2012 for Access ServicesSQL 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. Refer to TechNet article Installation for SQL Server 2012 for installation instructions.

This article describes a single on-premises SharePoint Farm setup 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.

See the “Set a new application database serverWindows PowerShell cmdlets” section of this white paper to learn how to assign a new application database server for Access apps.

Required SQL Server 2012 settings for Access appsSQL Server 2012, which is the application database server for Access Services in SharePoint Server 2013, stores Access app objects, including data. It controls all query processing. To coordinate with Access Services, the SQL Server 2012 instance must include the following settings:

SQL Instance Feature Selections:

o Database Engine Services


http://go.microsoft.com/fwlink/?LinkID=259492

http://go.microsoft.com/fwlink/?LinkId=259482

o Full-Text and Semantic Extractions for Search

o SQL Management Tools feature (for troubleshooting)

o Client Tools Connectivity

Note: You can set these features during installation or you can launch SQL Server Installation Center to install these features on an existing SQL Server instance. To launch the SQL Server Installation Center, from your Start menu select Microsoft SQL Server 2012. Select Configuration Tools. Select SQL Server Installation Center.

Security Mode = Mixed Mode (SQL Server authentication and Windows authentication)

An SA password

The Service Account that runs Access Services must have the following Server Roles on the SQL Server Security Logins table:

o dbcreator

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

Right click the account name and select Properties.

Select Server Roles.

Note: You assign the Service Account when you configure the SharePoint farm.


Enable Contained Databases = True

Allow Triggers to Fire Others = True

Default Language = English

TCP/IP Protocol = Enabled

Named Pipes Protocol= Enabled

Windows Firewall Inbound Ports TCP 1433, TCP 1434, and UDP 1434

Scripted installationIf you are familiar with running installation scripts in PowerShell, you can save time and accomplish most of the steps by:

Running the following installation script where you modify the settings in brackets (<>)

call "\\<location>\products\SQL Server 2012\RTM\setup.exe" /q /ACTION=Install /PID=<>/IACCEPTSQLSERVERLICENSETERMS /FEATURES=SQLEngine,FullText,Conn,ADV_SSMS /INSTANCENAME=MSSQLSERVER /TCPENABLED=1 /NPENABLED=1 /SQLSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" "<DOMAIN>\<FarmAdmin>" "<DOMAIN>\<BackupFarmAdmin>" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SQLSVCSTARTUPTYPE=Automatic /SECURITYMODE=SQL /SAPWD="<>" /INDICATEPROGRESS

Configuring SQL Server for Access ServicesThe following sections provide step-by-step guidance for configuring SQL Server 2012 for the required settings for Access apps.

Setting SQL Server security modeIf you have already installed SQL Server 2012 with Windows Authentication mode rather than Mixed mode authentication (SQL Server and Windows Authentication), take the following steps to change security modes:

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 propertyTo set the Enable Contained Databases property:

1. Open SSMS.

2. Select the server name and right-click it.

3. Select Properties.

4. Select Advanced.

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

Setting the SQL Server Allow Triggers to Fire Others propertyTo set the Allow Triggers to Fire Others property:

1. Open SSMS.

2. Select the server name and right click it.


4. Select Advanced.

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


Setting the SQL Server Default Language propertyTo set the Default Language property:

1. Open SSMS.

2. Select the server name and right click it.


4. Select Advanced.

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


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 2012After 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:

1. On the 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 box:

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

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

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 (enter 1434 in the text box that is next to Specific local ports and use TCP 1434 as the name for the port).

15. Repeat Steps 1 through 5.


16. Select UDP instead of TCP.

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.

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

Required SQL Server 2012 Components on SharePoint Server 2013In 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. You will also need to configure the Load User Profile setting in IIS, as detailed in the instructions below.

IIS Application Pool Load User Profile SettingA 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.

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


http://www.microsoft.com/en-us/download/details.aspx?id=29065

4. If you installed both Access Services and Access 2010 Services, you will see two Application Pools with GUIDs 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 BackupsIf 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 of 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 ServicesBefore you begin to configure Access Services, make sure that you have already installed SharePoint Server 2013 and that you have configured it for SharePoint apps.

If you have not yet configured your SharePoint installation for SharePoint apps, follow the directions in the TechNet article Configure an environment for apps for SharePoint 2013. As


http://go.microsoft.com/fwlink/?LinkId=259482

http://technet.microsoft.com/en-us/library/ms187048.aspx

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 stepsAfter you configure SharePoint Server 2013 for SharePoint apps, follow these basic steps to configure Access Services:

1. Enable the following required services:

Secure Store Service

Access Services

Access Services 2010

App Management Service

Microsoft SharePoint Foundation Subscription Settings Service

2. Generate a Security Key for the Secure Store Service.

Note:For purposes of the instructions in this document, in which SharePoint Server 2013 uses Windows authentication by default, you can generate the Secure Store Security Key after Access Services is provisioned.However, if you configure the SharePoint Server 2013 Application Database Server to use SQL authentication mode, you must add the Secure Store Security Key prior to installing Access Services. As part of provisioning Access Services, the SQL Server login and password are stored in Secure Store Service.

3. Create a Site Collection.

4. Set permissions on the Site.

After you finish the preceding steps, you should be ready to create a new Access Custom web app from your Office 2013 client computer.

How to complete the basic configuration stepsThe following sections provide more details about how to accomplish the basic steps of configuring Access Services.

Enabling required servicesAfter you configure SharePoint Server 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 to configure the SharePoint farm.

You are prompted to launch psconfigui.exe.

2. Click Yes to start the wizard.

The Configuration Wizard opens and then runs tasks to provision the SharePoint farm.

3. Click Finish when the configuration tasks are done.

You are prompted to assign the Service Account.

Your options are to:

Use existing managed account

Create new managed account

For this example, select Use existing managed account. The services that SharePoint installs by default are checked.

Access Services 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 as you have started the Access Services service, you can publish existing Access 2010 Webs 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 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 (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. In a later step, you will create a secure store key for this service.

4. Select the necessary services and click Next.

A SharePoint message shows you that it is working on the configuration of the services:

5. When the configuration of the services is done, click Create a Site Collection to create the site collection for Access apps.

Creating a site collectionYou must create a SharePoint site collection to manage your Access apps. After SharePoint Server finishes installing the services, it prompts you to create the site collection. If it does not, go to Central Administration and select Create Site collections under Application Management.

1. Enter a title for the site. The title will appear on the page for the site, but is not part of the URL address.


2. Provide the website address:

3. In the Template Selection area, the experience version is 2013 by default.

a. Select Team Site.

b. Click OK.

After you click OK to complete the Create Site Collection screen, a message will confirm that you have completed the Farm Configuration Wizard. It displays the site title, site URL, and list of service applications that you installed.

c. Click Finish. You may need to refresh your browser.

SharePoint brings you to the Central Administration page.

4. Test navigation to the URL that you created.

Setting permissions on the siteYour next task is to set permissions on the site.


1. Navigate to the site that you created, for example: http://AccessServer

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


http://AccessServer/

Windows PowerShell cmdletsIf you are used to using Windows PowerShell cmdlets to configure your environment, there are new cmdlets for Access Services. Refer to the article Use Windows PowerShell cmdlets to manage Access services in SharePoint Server 2013 for more information.

Set a new application database serverDuring 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.

If you have already installed and configured SharePoint Server 2013 for apps, open SharePoint Central Administration:

1. Click Start.

2. Click All Programs.

3. Select Microsoft SharePoint 2013 Products.

4. Click SharePoint 2013 Central Administration.

5. The UAC prompts you to open psconfigui.exe. Click Yes.

6. In Central Administration in the Application Management section, select Manage Service applications.

7. Select Access Services.

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


http://technet.microsoft.com/en-us/library/ee906548(v=office.15)

http://technet.microsoft.com/en-us/library/ee906548(v=office.15)

8. Click New Application Database Server to expand the choices:

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

10. Select Windows authentication.

Note: You must have dbcreator and securityadmin privileges on the SQL Server.

You are now ready to create a new custom app in Access 2013.

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


Create an Access appIf 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.

1. Start Access 2013 and click Custom web app.

2. In the Web Location box, enter the URL of the site that you created.

3. Click Create.


office 2013 – access services setup for an on-premises installation

Documents