Friday, 23 March 2018

An approach to building transactional systems in SharePoint

Overview:  It is common with modern SharePoint development to store transaction high volume data inside a SQL database and expose the application data using WebAPI or a WCF.  The application, e.g. SPA's, Angular or SharePoint pages itself merely calls the "web service" and viola you have an application that is fast and complex with the SharePoint world.


Problem:  When the WCF/WebAPI goes to the database we use a single account (single account principal).  This is an age-old problem in BI, and web applications.  The solution options are to have the security in the database, or each user needs to have a SQL login. 
Initial Hypothesis:  Generally in the last 20 years the majority of application go for the single principal data access approach.  This means there is no logging in SQL natively and you need to pass in the user's context (usually a username or email address).
My Solution:  I use the single access account principal, so I connect using the same account (either encrypt or use something like Azure Vault, in the old days this was the web.config entry with a username and password.  Each request needs to be unique so I pass in the username with the request, and my queries have users and roles and using these relationships I can validate that my user has rights to perform CRUD operations.  I am a huge fan of SQL 2016, as its performance is miles ahead of SQL 2014 and it supports "TemporalTables".  Now with other older SQL instances, you could build your own database logging (tomb tables is what I use to refer to it as).  Worth noting is that Entity Framework does not support Temporal Tables yet, but surely this will come. 
This solution provides a flexible, fast HA (assuming AOAG) transaction secured system with non-repudiation and full logging.  Overall I find this a great approach to building out complex solutions for my clients. 
This approach also provides a easy re-usable API that can be used to allow other applications and business partners to integrate with the solution.  It also allows for a mobile application UI to be easily added as the API are already in place.

0 comments:

Post a Comment