Authorization and Authentication in SharePoint for apps

In SharePoint 2010, the authentication to the site is based on Classic or Claims based or Anonymous Access but in SharePoint 2013, Microsoft come up with the new mode of Authentication called as OAuth.

The OAuth is the new buzz in the world of SharePoint App development.  Just to remember, OAuth is not the protocol for authenticating users to access SharePoint. It would still be done by Claims Authentication. The OAuth comes into picture when we want to authenticate and authorize SharePoint 2013 Apps.

I’ll start with some briefing on OAuth and the key concepts that we need to understand about OAuth. OAuth is the internet protocol for creating and managing app identity. It is also a cross-platform mechanism for authentication and authorizing apps. The OAuth is also the emerging internet standard which is used by Facebook, Twitter and Google.

OAuth gives the power and flexibility of having app identity in addition to the user identity. Here are the some pointers about App Identity

• App should be granted permissions independently of user permission
• App can request specific permission from the user during installation
• App can be granted more permission than the user (Elevation)
• App is constrained to what it can do during and after installation

Here are some important concepts around OAuth

1. Content Owner – User who grants permission to content in a site

2. Client App – This is the remote App (running on a Cloud or Hosted environment) that needs permission to Site Content. In our case it is SharePoint 2013 App

3. Content Server – The web server that serves the content to be accessed by App. In our case it is SharePoint 2013 Server (Cloud or On-Premise)

4. Authentication Server – Trusted server that authenticates apps and creates OAuth tokens. In our case it is Azure ACS server or OAuth compatible authentication server.

In case of SP sites, OAuth Process Flow is as follows

User Signs in SP 2013–>Security Token is generated by Identity Provider–>Token is validated & allows the user to Sign in SP sites.

OAuth is an open protocol for authorization. OAuth enables secure authorization from desktop and web applications in a simple and standard way. OAuth enables users to approve an application to act on their behalf without sharing their user name and password. For example, it enables users to share their private resources or data (contact list, documents, photos, videos and so on) that are stored on one site with another site, without users having to provide their credentials (typically user name and password).

OAuth enables users to authorize the service provider (in this case, SharePoint 2013) to provide tokens instead of credentials (for example, user name and password) to their data that is hosted by a given service provider (that is, SharePoint 2013). Each token grants access to a specific site (for example, a SharePoint document repository) for specific resources (for example, documents from a folder) and for a defined duration (for example, 30 minutes). This enables a user to grant a third-party site access to information that is stored with another service provider (in this case, SharePoint), without sharing their user name and password and without sharing all the data that they have on SharePoint.

When is using OAuth required?:
The OAuth protocol is used to authenticate and authorize apps and services. The OAuth protocol is used:

·         To authorize requests by an app for SharePoint to access SharePoint resources on behalf of a user.

·         To authenticate apps in the Office Store, an app catalog, or a developer tenant.

Access Tokens:

 In SharePoint 2013, an OAuth STS is used only for issuing tokens (that is, server-to-server and context tokens). An OAuth STS is not used for issuing sign-in tokens, that is, they are not used as identity providers. So, you will not see an OAuth STS listed in the user sign-in page, the Authentication Provider section in Central Administration, or the people picker in SharePoint 2013.

·         But, SharePoint 2013 administrators can use Windows PowerShell commands to enable or disable an OAuth STS. SharePoint administrators are able to enable or disable OAuth for a given web application, similar to how they can enable or disable trusted login providers in SharePoint 2010.

·         SharePoint 2013 implements the OAuth protocol to allow apps that are running external to SharePoint to access protected SharePoint resources on behalf of a resource owner. In the SharePoint incoming implementation of the protocol, the OAuth roles are played by the following components:

·         External apps take on the role of the client.

·         SharePoint users take on the role of resource owner.

·         SharePoint 2013 takes on the role of the resource server.

·         ACS takes on the role of the authorization server.

The OAuth authentication and authorization flow for a SharePoint 2013 cloud-hosted app is shown below

 1.      A user types a URL in a browser to go to a SharePoint page where a particular app is installed. In this case, the app is a Contoso.com app and the user interface element on the SharePoint page comes from the Contoso.com app.

2. SharePoint processes the page and detects that there is a component from the Contoso.com app on the page. SharePoint must get a context token that it can send to the Contoso.com app. SharePoint asks ACS to create and sign a context token that contains context information (for example, the current user, what web is being rendered on SharePoint, and other context information) and an authorization code. This context token can be used later by Contoso.com to request an access token from ACS. The Contoso.com server can use the access token to talk back to SharePoint if the Contoso.com app wants to make a web service call to SharePoint later.

3. ACS returns the signed context token to SharePoint. The signed context token is signed with a client secret that only ACS and the Contoso.com app share.

4. SharePoint renders the page, including an IFRAME pointing to the app host server” in this case, Contoso.com. When SharePoint renders the page, it also passes the context token to the IFRAME.

5. The IFRAME causes the browser to request a page from the Contoso.com server. The context token is included in the browser request that is sent to the Contoso.com server.

6. The Contoso.com server gets the context token. Contoso.com validates the signature on the context token. The token is signed with an client secret that only Contoso.com and ACS share. Contoso.com can validate that the token is really intended for it and that it is not a random request from some random server. It knows that it is part of a SharePoint request.

7. If the Contoso.com server wants to talk back to SharePoint, there is a refresh token in the context token that Contoso.com can extract, so that it can include that information in the request to ACS for an access token. Contoso.com uses the refresh token that it extracted from the context token, the context token that it got from SharePoint, and its credentials (which are its client Id value and its client secret value) to request an access token from ACS so that it can talk back to SharePoint.

8. ACS returns an access token to the Contoso.com server. Contoso.com can cache this access token. That way, the Contoso.com server doesn’t have to ask ACS for an access token every time that it talks back to SharePoint. (Or, Contoso.com can make an access token request every time and not cache the access token.) By default, access tokens are good for a few hours at a time. Each access token is specific to the user account that is specified in the original request for authorization, and grants access only to the services that are specified in that request. Your app should store the access token securely, because it is required for all access to a user’s data.

9. Contoso.com can use the access token to make a web service call or CSOM request to SharePoint, passing the OAuth access token in the HTTP Authorization header.

10. SharePoint returns the information that Contoso.com requested to Contoso.com. The Contoso.com app renders the IFRAME contents as a per-user request in step 1. This completes the OAuth transaction process. The user now sees the SharePoint page fully rendered.

Typical App Usage Scenario :

User Sign-in to SharePoint 2013 and is authenticated àUser then uses either Office Store (SharePoint Apps Marketplace) or Catalogue App (On Premises) à The user grant permission to the App to access SharePoint resources on behalf of User à A SharePoint site has an app launch à When user launches an app, SharePoint 2013 posts a context token to the app à The app then give call back to SharePoint 2013 to access SharePoint resources on behalf of the user by using the access token

What is Context Token -? : When SharePoint identifies that an App requires an access to SharePoint resources, SharePoint must get a Context Token from ACS (Identity Provider) and send the context token to the app

What is Access Token -?: If app want to talk to SharePoint /make a web service call then app need Access token. App uses context token to request the access token. ACS returns the access token to app which can be cached by the app that way App doesn’t need to ask for the access token every time it talks back to SharePoint. By default access tokens are good for few hours at a time. Each access token is specific to the users account

What is Refresh Token? : If app want to talk back to SharePoint there is a Refresh token is inside the Context token which can be used to request an access by the app. By default refresh tokens are good for one year. So the same refresh token can be redeemed for new access token from ACS for about a year.

Apps can be authenticated in multiple ways :

1)    When a call is made to App Web the call is attributed to the app web along with authenticated user (This is not a case for OAuth Call)

2)    If it is an OAuth call then SharePoint uses Windows Azure Control Service (ACS) as an app identity provider

Authorization remains consistent across all apps. Authorization verifies if the app or the user acting on behalf of App (We can call it as a Subject) has permission to perform certain actions or if the Subject has an access to the resource

Hope this help someone Cheers... J

Detail information about the SharePoint Framework (2016):

What is SharePoint Framework?

SharePoint Framework is a new model for building customizations on the SharePoint platform. Although it has just been announced it isn't available for developers just yet. SharePoint Framework is based on the concept of building client-side solutions which integrate with the modern SharePoint UX.

 For building SharePoint Framework solutions you can use any JavaScript framework you want and, for the first time ever, building SharePoint solutions isn't limited to Windows and Visual Studio. Because SharePoint Framework leverages widely used web development stack, solutions can be built on any platform including OS X and Linux using code editor of your choice. For the first time Microsoft isn't using a proprietary development model specific to SharePoint, but instead leverages tools and techniques widely used by developers on all platforms and stacks. Also the Product Group is committed to using only the publicly available APIs to build their solutions on top of the framework which will allow developers to build solutions with capabilities comparable to what's available out of the box and more.

The SharePoint Framework is an evolutionary step in SharePoint extensibility that delivers a new client-side rendering framework leveraging open source JavaScript technologies. The client-side development framework will allow developers to use modern JavaScript and web tinplating frameworks across cloud and on-premises SharePoint.

Microsoft used the SharePoint Framework to create many of the experiences that we announced today, like the home page of a team site. Our customers can now build similarly powerful, rich apps and experiences in SharePoint, integrating services across Office 365 following the same patterns, practices and process we use to build native SharePoint experiences. Pages and experiences built with the SharePoint Framework will be mobile by default and will be integrated with the SharePoint mobile app.

The SharePoint Framework embraces the tools, frameworks and open source initiatives that developers rely on today, and it extends the scope of solutions that can be built on SharePoint, in the cloud and on-premises.

The SharePoint Framework will be released to Office 365 customers in First Release this summer. Web parts built with the framework can be added to modern pages and experiences and to existing pages.

In the third quarter of 2016, you will see:

• The Files API on Microsoft Graph.
• SharePoint Webhooks (preview).
• Client-side web parts for existing pages (preview).

And by the end of 2016, Microsoft will deliver:

• The Sites API on Microsoft Graph.
• SharePoint Webhooks (GA).

Custom sites on the SharePoint Framework.

The key benefit:

If you follow the guidelines and deliver your solutions according to the set forth practices; Your new solution will be 100% mobile friendly natively out of the box with the new modern Team Sites as well. This rhymes well with the cloud-first, mobile-first announcements in the past.

How does the SharePoint Framework work?

Tools and Frameworks

Since the SharePoint Framework is entirely JavaScript-based, you can decide what tools and techniques you use yourself. I have my personal recommendations and favorites of course, as I'm sure you do.

Yeoman, if you haven't used it before, is a scaffolding tool for modern web applications. It's very easy to use, and the benefit of using it is that within seconds you can create a new project structure and get ready to implement your custom functionality in that automatically generated project.

With the SharePoint Framework coming, there's going to be Yeoman scaffolding templates available as well, which will help you to get those projects created.

Gulp for task automation, tests and build

It's a task and build runner for developers. You can use it to perform repetitive tasks, automate minification, compression and a lot more. One of the great benefits of Gulp is that you don't have to write everything yourself. There's a whole sea of plugins available, ready to be used easily in your gulpfile.js.

Visual Studio, Visual Studio Code or something else?

One of the benefits of having a fully decoupled framework, purely written in mature JavaScript, is that you're not constrained to a specific development IDE or toolset. You can simplify development by using any tool you feel comfortable with. Personally, I've done my projects in Visual Studio and Visual Studio Code. Without getting into an argument about which IDE is best, I prefer Visual Studio Code for responsiveness and super-lightweight coding. But I prefer Visual Studio Ultimate when I need to use my integrations, extensions and plugins which currently aren't available for Visual Studio Code. As always, this comes down to a personal preference - and the great thing about the SharePoint Framework is that it doesn't require you to use any specific tooling you decide what you want to use.

Workbench - for local development:

There's something called the Workbench. This is interesting because it's really a way to develop your applications and web parts on your dev box, and by running gulp serve for example, it will spin up a new localhost environment called the SharePoint Workbench. From here you can simply add your new Web Part or Application and see how it works before even pushing this on to your actual environments. This means you'll get a light-weight and similar UX as the new SharePoint sites will offer in terms of how to add and work with your web parts etc - but offline for easier development efforts.

Most of this is fully automated. Simply running gulp serve in your new project will open up a browser and present the Workbench.

Benefits?

Test Web Parts or Applications without a connection to any server

Quick development turnaround from editor to verification in browser

Lightweight

Things you need to know about on the page

The Chrome contains your business context, if you will. It contains the logic offered by Microsoft out of the box - and any additional logic you may build on top of that.

Mobile friendly

It has its own branding, theming and business logic. If you play by the rules and follow guidance which will be made available, anything you run and build in the SharePoint Framework will also be looking great on mobile devices. There's plenty of benefits of following the rules - and since we're doing it from scratch for new customizations, there's no reason not to do it the right way from start.

Page Body

The Page Body (see picture above) is where the users mostly live and interact with anything you build. Web Parts, Applications and any customizations would most likely go into this area. Users can easily click on the plus sign and then add any app you've built from there.

Canvas

The canvas is simply the part of the page where you add your Web Parts of apps. You could relate them to what used to be Web Part Zones or Rich Text Editors perhaps. It's what you see in the previous image above.

Will SharePoint Framework solutions work on classic Sites?

The SharePoint Framework will eventually work on your existing team sites, and on the new modern team sites. However, the classic solutions will not work on the new modern team sites.

This means it's a good idea to get to know this enhancement in the developer experience, and see how it fits into your customization story.

Hosting your code and applications

When a development effort is ready enough to try out live in a dev/test environment, or even production, it is a good idea to know where to host it.

Using a CDN (content delivery network) of your own choice might be a good idea for performance, geolocation and caching etc. There's plenty of ways you can achieve that.

Key aspects for developers  

• Client web parts and client-side applications are the new building blocks. As discussed in the last post, these go along with the new page model.
• It’s a JavaScript world  Config files are in JSON, and code is implemented in JavaScript on the client-side.
• The packaging of artifacts is different! There are new manifest files to learn about (e.g. a web part manifest), and other files such as bundle.json, package-solution.json, upload-cdn.json and more. Gulp tasks are used for packaging.
• Files for your web part or app can live anywhere (e.g. a CDN, or a website you host) - they don’t have to live in SharePoint. Anywhere that can be accessed on a URL by the end-user basically.
• The “local development” model is very different – Gulp and node.js are used to host files locally, so you don’t need to use IIS on your local machine. A special “workbench.aspx” page is used to support this.
• No particular JavaScript framework is mandated – you can use Angular, React, Knockout, Handlebars or whatever you like. The one thing to consider is that React will already be on the page as it’s used by some SharePoint components, so that’s one less file to download the first time users hit your site or customization – useful, but probably not a massive factor for intranets though.
• You should consider learning TypeScript – at least the key parts such as modules, the type system and so on.
• You no longer NEED to work in Visual Studio - if you prefer another tool (e.g. because you’re not a “career” SharePoint developer), that’s fine. Other lightweight code editors such as Visual Studio Code or Sublime are now 100% viable options because the packaging of artifacts is different. Even I (as a 10 year SharePoint developer) have started to prefer using VS Code, even if some bits are slightly painful initially.
• SharePoint Webhooks – these are the new “event receivers”. This is a move towards a more standards-based approach to responding to changes in SharePoint/Office 365.
• The App Catalog is used as a packaging and registration method – both client web parts and client-side applications are packaged in this way (moving away from the web part gallery we’re used to)
• Page security needs some consideration – because anything on the page can theoretically access anything else (e.g. by scraping the DOM), that could raise some interesting questions when the user’s mail is shown (for example) and web parts from different sources/vendors are present. To counter this, some operations are blocked in the new framework but other governance may be required. special thanks to CHRIS O'BRIEN for his wonder full blog on SharePoint framework your contribution to SharePoint world is awesome.

Deploying to production (e.g. deploy to CDN)

Once we’re ready to have our solution used in test or production, we need to move away from the locally-hosted model. Now the JavaScript, CSS and any other files needed at runtime need to live in a location which can be accessed by all users – this can be a CDN, a simple website such as an Azure web app, or any other location of your choosing. You *can* continue to deploy to SharePoint libraries (e.g. Site Assets) if you choose, but now we have an option which moves us away from needing key files deployed (and duplicated) in each site collection – woohoo! You are responsible for providing this location (unless you’re choosing SharePoint)

Client applications 

As well as client web parts, let’s touch briefly on client applications. These are coming later in the year, and in the same way that client web parts offer a JavaScript-only version of web parts, client applications do a similar thing for full page apps. I summarized the flavors in my previous post like this:

Page-based apps – an alternative to provider-hosted apps (which remember, are implemented with server-side code).

List-based apps – think of these as an alternative to JSLink for transforming the display/edit/new experience around list items

Client applications are implemented purely in JavaScript, but have benefits such as having full “context” and using the Office 365 suite bar etc. There is a framework or scaffolding page in your SharePoint site/Office 365 tenancy, but the main page body is implemented in your JavaScript/HTML/CSS. It’s pretty easy to understand the page-based apps, but the list-based flavor is interesting too. Recently I’ve worked on several mini-applications which provide a custom interface, but store their data as items in a SharePoint list. We built a page which takes a URL parameter for the item ID, and then issued a REST call to fetch the data and build the page around that. One example was a “office locations directory”, where the locations were stored in the list but we provided a nice presentation with an embedded map, particular layout of the data elements and so on. You can consider list-based client applications as a formalisation of that – it will be quicker and easier to build such solutions, almost in a “JSLink on steroids but without the proprietary display template framework”-kinda way. Nice.

Summary

That might be the best word to describe what is coming out of the productivity machine at Microsoft. There are plenty of opportunities for developers and foremost I like the idea of decoupling things from SharePoint even more, hosting them as scripts from CDN etc instead. Keep your SharePoint clean and alive - and focus on what's important. And don't forget - always play by the rules so you don't end up in the same customization madness that so many people have done in the past.

Now it finally feels like we're moving into a modern world, and that SharePoint is actually up to current standards. You don't have to spend all your development efforts in outdated techniques, tools or frameworks - choose whatever you want for yourself and your team, and go build stuff! In the simplest way possible I'd say it's all about consuming API's and working with them - what you leverage in order to communicate with those API's, that's entirely up to you. Thanks for Reading. Hope it helps you…J Cheers…

Overview of SharePoint Access Services

If you have deployed SharePoint Server 2010/2013 with the Enterprise license to your organization, then you can purchase the Office Web Apps (OWA) add-on to provide your SharePoint portal users with browser-based editions of Word, Excel, PowerPoint, and OneNote. IN case you wondered, “But what about Access?” doesn’t fret? Access Services is included with the SharePoint 2010 Enterprise license. The coolest thing about Access Services and OWA is that you can host Microsoft Office documents in your SharePoint portal and your users don’t have to have the full-blown Office applications installed on their computers; the only requirements on the client side are (a) a Web browser; and (b) access to the SharePoint portal.

By the end of this brief article you will understand how to publish Access 2010/2013  databases to your SharePoint portal with a minimum of muss, fuss, or greasy aftertaste.

Set up and configure Access Services 

Access Services in Microsoft SharePoint Server 2010/2013 is a service that allows users to publish a Microsoft Access 2010 Web database to a SharePoint site. When an Access Web database is published to SharePoint Server 2010/2013, a site is created to host the Web database, plus it moves all of the database objects and data to a SharePoint list on that site. While you do not need the Access client to use the published Web database, the Access client is required to make any changes to the database structure. In addition, a user account is required to use the Web database on SharePoint Server 2010/2013. Anonymous access is not supported

Deploy Access Services

The following steps are needed to deploy Access Services:

1.      Install and configure Microsoft SQL Server 2008 R2 Reporting Services Add-in for SharePoint Technologies 2010 (SSRS).

2.      Create an account in the Active Directory service to run the application pool for the Access Services service application.

3.      Register that (Polasoft\accessapppool) account as a managed account in SharePoint Server 2010.

4.      Start Access Services.

5.      Create an Access Services service application

To create an Access Services service application

1. On the Central Administration home page, under Application Management, click Manage service applications.

2. On the Manage Service Applications page, click New, and then click Access Services.

3. In the Access Services Application Name section, type Access Services in the text box.

4. Select the Create new application pool option and type AccessServicesAppPool in the Application pool name text box.

5. Select the Configurable option, and from the drop-down list, select Polasoft\accessapppool.

6. Click OK.

On the Access Services 2010/2013 settings page, change any of the settings shown in the following table.

Setting	Description
Lists and Queries	Settings for queries used against Microsoft SharePoint Foundation lists
Maximum Columns per query	The maximum number of columns that can be referenced in a query. Note that some columns may automatically be reference by the query engine and will be included in this limit. Valid values: from 1 to 255 Default value: 40
Maximum Rows per query	The maximum number of rows that a list used in a query can have, or that the output of the query can have. Valid values: from 1 to 200000 Default value: 25000
Maximum Sources per query	The maximum number of lists that may be used as input to one query. Valid values: from 1 to 20 Default value: 12
Maximum Calculated Columns per query	The maximum number of inline calculated columns that can be included in a query, either in the query itself or in any sub-query on which it is based. Calculated columns in the underlying SharePoint Foundation list are not included. Valid values: from 0 to 32 Default value: 10
Maximum Order by Clauses per query	The maximum number of Order By clauses in the query. Valid values: from 0 to 8 Default value: 4
Allow Outer Joins	Allow left and right outer joins in a query. Inner Joins are always allowed. Check box: selected or cleared for Outer Joins allowed.
Allow Non Remote-able Queries	Allow queries that cannot be remoted to the database tier to run. Check box: selected or cleared for Remotable Queries allowed.
Maximum Records Per Table	The maximum number of records that a table in an application can contain. Valid values: -1 (indicates no limit), any positive integer Default value: 500000
Application Objects	Limitations on the types of objects an Access Services application can contain
Maximum Application Log Size	The maximum number of records for an Access Services Application Log list. Valid values: -1 (indicates no limit), from 1 to any positive integer Default value: 3000
Session Management	Behavior of Access Database Service sessions
Maximum Request Duration	The maximum duration (in seconds) allowed for a request from an application. Valid values: -1 (indicates no limit), 1 through 2007360 (24 days) Default value: 30
Maximum Sessions Per User	The maximum number of sessions allowed per anonymous user. If a user has this many sessions and starts a new session, the user’s oldest session is deleted. Valid values: -1 (no limit), from 1 to any positive integer Default value: 10
Maximum Sessions Per Anonymous User	The maximum number of sessions allowed per user. If a user has this many sessions and starts a new session, the user’s oldest session is deleted. Valid values: -1 (no limit), from 1 to any positive integer Default value: 25
Cache Timeout	The maximum time (in seconds) that a data cache can remain available, as measured from the end of each request for data in that cache. Valid values: -1 (indicates no limit), 1 through 2007360 (24 days) Default value: 1500
Maximum Session Memory	The maximum amount of memory (in MB) that a single session can use. Valid values: 0 (disable) through 4095. Default value: 64
Memory Utilization	Allocation of memory on Access Database Service
Maximum Private Bytes	The maximum number of private bytes (in MB) allocated by the Access Database Service process. Valid values: -1 (the limit is set to 50% of physical memory on the computer), any positive integer Default value: -1
Templates	Settings related to template management
Maximum Template Size	The maximum size (in MB) allowed for Access Templates (ACCDT). Valid values: -1 (no limit), from 1 to any positive integer Default value: 30

Enabling Session State for SharePoint 2010 Access Reports:

1) Open SharePoint 2010 Management Shell

2) Type in the following command PS C:\Users\shiva> enable-SPSessionStateService

3) You should get the following response:-

cmdlet Enable-SPSessionStateService at command pipeline position 1
Supply values for the following parameters:

4) Type in the Database Name as accessreports (DatabaseName: accessreports) – This will create a new database in SQL names accessreports

5) You will be return to the root path PS C:\Users\shiva>

6) Open the normal command prompt and restart the IIS by typing in iisreset

Access Services lets you quickly create web applications and make them available to your SharePoint users via their web browser. The great thing is you don’t actually need to know much about SharePoint or be a web developer to make your application, the only thing you need is Microsoft Access skills.

The process for creating an Access Services database is:

1.      Create a database with tables and forms in Access 2010.

2.      Click the Home menu and choose the Save and Share option.

3.      Perform the compatibility test and fix any issues.

4.      Publish to SharePoint – enter a site name (Access Services creates a site for each published Access database).

Access Services migrates the data from the Access MDB file into the SharePoint Content database (as lists). This allows multi-user access to the Access Services database. One thing to note is that Access Services databases can’t use a SQL Server backend database.

SharePoint Access Services 2010/2013 – Benefits:

§  Access Services are nothing but Data driven web applications called web databases hosted in the SharePoint 2010/2013 environment

§  Access Services Benefits and Usage

§  Balance between business agility and IT manageability

§  No Install Solution –  Web based Access

§  Improved Collaboration – Share and collaborate on the declarative RAD no-code web based team databases.

§  Centralized Data Storage – Single truth of the Application Logic and Data

§  Improved Reliability, Scalability, Security, and Manageability – Central IT management

§  Improved Backup/Restore – Access databases are part of the SharePoint Backup and Restore Process

§  Increased Concurrency – Locks the database at the object level, instead of database file level resulting in fewer conflicts

§  Access Applications Standardization using Templates – IT can configure/support services, start building out the web database standards, and let end-users manage it

§  RAD No-Code SharePoint Applications – Useful to build web based powerful RAD applications which supports query engine that can perform complex joins, filtering, aggregations, and parent-child relationships between lists.

§  RAD Reporting Tool – Useful as reporting tool to generate the customized reports (RDL files) based on SharePoint Lists hosted in the SharePoint.

Architecture Details:

§  Access Service is a middle-tier service, which handles the query processor and data access layer. It also manages communication between Access Web Application and SharePoint Content Databases.

§  Support for the Relational Database Data Integrity using SharePoint Lists Enhancements Infrastructure – Lists Relationships, Cascade Deletes, Unique Constraints, and Data level validations at the List and Item Level

§  Improved Performance/Scalability – Offers caching layer that addresses the limitations of the maximum number of list items that a query can return at one time by ignoring the List View Threshold. Improved Performance by allowing large record sets in the SharePoint List. Access DB supports more than 100K records. SharePoint Lists has filtering or sorting restrictions if you have more than 5K records in the list because content database blocks large calls. Access Services Data Sheets retrieves all the 100K SharePoint list items in the 2K records in chunk and later stitches them together in the ADO.NET record set in memory Layer as cached data for the performance. This allows sorting and filtering against cache instead of content database on the more than 50K records in the Access datasheet. Access datasheet View is rendered by the Project Data grid object and it is smart enough to bring only 200 records at a time in the browser. With the smart navigation using AJAX experience, as you scroll down, it will bring additional records from the cache without affecting user experience. Access List Views are continuous forms supports paging, sorting, and filtering, and behaves same way as Datasheet View by retrieving data from the ADO.NET cache in the middle tier.

§  Concurrency Conflicts – Different users can make changes to different objects or different data items in a list without causing conflicts. When data conflicts do occur, a conflict resolution wizard enables the user to choose which version of data items to preserve. For object conflicts, Access provides the name of the user who made the saved changes and creates a renamed backup copy of the local object before downloading the other user’s changes.

§  Source Control – While downloading the database locally on file system, Access client downloads the entire database only when a user doesn’t have local copy. Access fetches only objects or data items that have changed.

§  Only Web databases are supported in the Access Services. Web database supports two types of Access Objects – Web Objects that can run either in browser or Access Client and client objects (non-web objects) that can only run in the Access client. All design changes for both web and client objects must be made in Access Client. It is important to remember that client object definitions are published and stored in the SharePoint but they can accessed and executed during runtime only in the Access client.

§  All linked tables are client tables. Only client objects like reports, forms, and macros can work with linked tables. Linked tables aren’t available to the web objects.

§  All the reports, forms, and macros with VBA code make these objects client objects.

§  All the web objects like reports, forms, and macros would work only with the web tables (Note web tables are Access DB tables fully compatible with SharePoint lists)

§  When working in the Access client and connected to the network where Access Service App is running, data and design changes to web tables automatically synchronize with the server.  When disconnected, Access client works with local copy and doesn’t allow changes to the tables. When reconnected, Access notifies users to synchronize and resolve any conflicts. Design changes to objects other than web tables synchronize only when users explicitly request sync by clicking Sync All button.

§  Forms and Reports gets rendered in the Data Form Web Part

§  Data Sheets rendered in the Project Data grid JS Object to support large data sets

§  Reports are rendered in the Report Viewer Web Part installed with the SSRS Add-in

§  Access Service AJAX WS provides AJAX experience on the Access Web Applications

§  Better Administrative Control – Uses the OOB SharePoint Security for the Security Layer 

§  Managing and Fine Tuning the Access Services – From the Central Admin -> Manage Access Services Settings e.g. Max columns/rows per query, Max sources per query, Max calculated columns per query, Max order clauses per query, Max records per table in the join, Max sessions per users etc.

Why use Access Services:

Access Services applications can be built by anyone with Microsoft Access with a very low learning curve. No need to learn .NET development.

Existing Access Databases can be converted. They must be upgraded to Access 2010 /2103 first and a few changes may be required to meet the deployment requirements, but will often be far quicker than developing a new application.

Performance is great for remote users. The application is accessed via your web browser and is a great way to improve performance for remote users.

Your database becomes multi-user with minimal effort!

Because it’s SharePoint your application is available on the intranet without additional infrastructure and licensing.

A few other things you should know:

•User access management is handled by SharePoint security.

•Locks the database at object level rather than file level. Better concurrent user access.

•Allows large lists (100,000 items is easily supported)

•Master page branding is not supported. Uses Access branding.

•Linked tables are not supported

•Access Services sites can’t be edited using SharePoint designer

Requirements:

To use Access Services you need the following:

•SharePoint 2010/2013 Enterprise

•SQL 2008 R2 with Reporting Services installed in SharePoint Integrated mode

At this point you have the fundamentals under your belt such that you should be able to verify the running state of Access Services in SharePoint 2010 and publish an Access 2010 database to your portal. Please be sure to leave your questions and/or observations in the comments area of this post; I’m happy to help

Hope this helps someone… J