Blog Archive » SharePoint 2013 Access Services

SharePoint 2013 Access Services

I recently got Access Services (2013) in place in our internal SharePoint 2013 environment.  The following is what I learned from trying to go through this install (for the second time — the first time was a failure because we hadn’t setup Apps properly).

NOTE: In this article, unless otherwise stated, all references to Access Services apply to Access Services (2013) in SharePoint Server 2013.

Hardware / Software

  • SharePoint 2013 with a WFE Server and an APP server (in our case, these were different virtual machines) on minimally Win Server 2008 R2, preferably Win Server 2012
  • SQL Server 2012 SP1 Server (minimally, you need “Standard”, “BI” or “Enterprise” also work) – SQL Server 2008 is NOT sufficient for Access Services 2013

Details

  • Download this document, which is from MS and is the starting point for documentation.  http://www.microsoft.com/en-us/download/details.aspx?id=30445
    • Follow the steps in the document, I’ll just summarize things a bit below, and my experience.
  • You’ll need to download the following components (to your SharePoint “APP” server).
    • 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)

These are available from the SQL Server 2012 Feature Pack.

  • You’ll need a desktop/non-SP server machine with the Access 2013 Client application installed (must be Office 2013, and should be Professional Plus) for the very last step
  • You need a SharePoint farm built to best practices
  • You MUST first configure your SharePoint environment (completely) for “apps” – without this, the rest will fail.  Check out this TechNet article for details.
  • Create a new instance on your SQL server 2012 for ACCESS.  Typically you can use the same server as is running your SharePoint DBs, this instance isn’t likely to be overly taxing – unless your Access DBs are ridiculously large.
    • Ensure you setup backups for this instance in your backup software
  • Setup the SQL server according to the settings in the installation document (above).
    • Including the database instance, security mode, specific settings (triggers, language, etc.)
  • Setup the firewall on the SQL server appropriately (if you have a separate instance, running on a different port, be sure to open that port in the firewall)
  • Install the SQL server components (you downloaded above) on your APP server (they shouldn’t need to be installed on a WFE, only the server you plan to run Access Services from – which should be your APP server).
  • Now go into “Manage Services on Server” under “System Settings” and enable the following services (on your APP server)
    • Secure Store Service
    • Access Services
    • Access Services 2010 (yes, you really should set this up too – like it or not)
    • App Management Service
    • Microsoft SharePoint Foundation Subscription Settings Service
  • From Central Admin, run the farm configuration wizard and turn on the required services (as noted in the last step)
  • Now go back to earlier in the document and setup the “IIS Application Pool Load User Profile Setting”
  • Ensure you have a security key setup in your secure store
  • Create a site collection that you’ll use as your “Access apps management store”
    • Once created, go into the site collection and ensure you setup permissions for everyone who will need permissions to create access apps – in order to create an access app, you’ll need at least “Owner” level permissions.
  • Go back to Central Admin and setup a “New Application Database Server” (under the Access Services 2013 service in “Manage Service Applications”), point it at the ACCESS instance you setup earlier (I recommend you use a SQL alias to do this)
  • Finally, create a new Access App from Access 2013, and point the “web location” to the site you created earlier
    • Don’t forget, in order to be able to create an App, you need Owner level permissions