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. 

Summary: 
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 an 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.

Note: Temporal Tables have been available since SQL 2016 and are available on Azure SQL.

101 PowerApps for SharePoint on-prem.

Overview:  Microsoft PowerApps is easy and can be used to build robust applications.  It is common for organisations to have data store on-prem and in the post, I look at the Infrastructure to have a Power App that uses data stored on SharePoint 2016 on-prem.

PowerApps can create forms from:
SharePoint: Replace lists on a form
Desktop (think this is being removed by MS)
Online at web.powerapps.com (Preferred option to build an application from scratch)

Either choose the phone or tablet layout for your application/form.
Do not confuse with SharePoint MS Forms.  SharePoint forms are for basic forms (replaces FOSL), very limited usage whereas PowerApps are the replacement for InfoPath.

PowerApps works nicely with Flows.  Note:  I believe Visio will be able to create Flows with.
Formula-based programming like Excel.
Can also hook to Azure Functions.
Link InfoPath, you can save the PoerApps files to move them around.

Setup PowerApp using O365
Install the "On-prem. data gateway for Microsoft PowerApps" to communicate with on-prem.  Configure the outbound firewall to allow traffic thru
Connect to SharePoint 2016

https://powerapps.microsoft.com/en-us/tutorials/gateway-reference/
Laura Rogers presented at SPC2018 on PowerApps

Evolution of SharePoint Solution Design Principles

Overview:  Over the past couple of years I have strict rules I use when developing full SharePoint applications on SPO, SP2013, and SP2016. 

Hypothesis: Since SPO does not allow FTC and many large enterprises have been trying top remove FTC (governance preparing to move to O365) I follow the basic concept of using no FTC on any WFE's.  Small single pages and applications are merely JS injection changes.  I'm talking about large applications.

JS injection is my biggest tool to bend SP.  In the old days, I used WSP's.  When JS won't cover the tasks.  I deploy my code thru a custom XML driven console or PnP, I also have an old XML driven PowerShell script for deploying the code to each environment.  This allows me to store our code in TFS or GIT.  package and deploy to multiple DRAP environments.

I use C# consoles that are generally XML driven calling the CSOM API for provisioning sites and then the actual application is also XML driven.  I also have XML driven content for automated testing in lower environments or seed data needed for production setup.

Lastly, if I need a transaction database I use Provider Host Apps (Add-ins), I also would use a server-based WCF for any long-running processing that I want to fire from the web.

Workflows, I tend to use what the customer has Ninetex or K2 I have seen a fair amount.  Most workflows are pretty basic once the client's actual need has been clarified and the business process work out so I keep it simple.

Reporting, still use the SSASA, SRS and SSIS and a mix in between if I can. Basic SharePoint KPI's are my first choice.  I have integrated other products like Tableau.

Timer Jobs, I use C# consoles and log to a central place.  I read the server performance to ensure I don't destroy the farm when using multi-threading.  I fire off using windows scheduled task (or equiv i.e. ControlM).

Now the new world with SPFx....

SharePoint Tooling 2018

On a development/local machine, I have complied this list of tools that I'm likely to use but it completely depends on the development project, approach, and technologies used.  This post lists tools I commonly use as of March 2018:

SharePoint Tools:

1. SharePoint Inspect
2. SharePoint Designer
3. Visual Studio 2017
4. SharePoint Search Query Tool (CodePlex now PnP)
5. SharePoint Manager
6. CAML Builder

Developer Tools:

1. NodeJS
2. NPM
3. Yeoman, 
4. GULP
5. Webpack

Design Tools:

1. Balsamiq (My favourite) - Screens and interaction flow
2. Updated 2019/12/01 - visual-paradigm.com online drawing tool
3. Axure - Prototyping
4. Figma
5. Microsoft Blend - Screens and interaction flow
6. Visio - Architecture
7. MS Access to ERD design

Other Tools:

1. Visual Studio or Visual Studio Code
2. SnagIT - Basic video recording with audio and annotate screenshots.
3. Office & OneNote
4. NotePad++, add the compare dll plugin
5. Grammarly
6. Slack or Teams
7. Yopmail or mailinator - useful for creating accounts for testing (beware these can be a security risk as anyone can guess you temporary emails) they are deleted after a few days.
8. BrowserStack

Networking:

1. Wireshark
2. Fiddler
3. DeveloperTool IE & Chrome
4. Burp
5. Telnet
6. Postman

Source Control:

1. TFS
2. TFS online
3. GIT

Consultant Bingo - A master class

I love a useless term to baffle the room as much as the next fellow, but watching a master in a meeting today:

STRIDE Model is Microsoft's Security/Threat classification model.  I had to look it up and found another acronym.  STRIDE is for Threat modelling as part of risk management.  Acronym for: 

1. Spoofing a server
2. Tampering a file
3. Interlude: Scope and timing
4. Reputing an order
5. Information Disclosure
6. Denial of Service
7. Elevation of Privilege's

The DREAD Model is pretty much the same as STRIDE.

CIS framework or MITRE framework—a Security framework for benchmarking. It is closely related to the SOC (Security Operation Centre).

'RESPECT' for: "I evaluated my DTAP environments cross Federation services using the STRIDE model over the DREAD model because it is simpler.  Of course, all the cross-cutting concerns have been dealt with." 

Three Amigos—Backlog review: The PO, SM, and Team members meet to discuss design, development, and testing.

YAGNI is an XP principle: "You Aren't Gonna Need It." This principle basically means only creating code for requirements, not what you feel may be needed later on.  

Pareto Rule - roughly 80% of consequences come from 20% of the causes.  Or 80% of outputs come from 20% of inputs.  So 80% of the revenue may come from 20% of your clients.  Also referred to as the 80-20 rule. The same principle applies to the 90-10 rule.  Pareto analysis 80% of a project's benefits can be achieved by doing the correct 20% of the work.

Rindelmann Effect - Individual members become less effective as the size of the group grows.  I opt for small, focused teams even for large programmes as more people do not equal more technology delivery. 

A hockey stick pattern is a chart pattern that shows a rapid increase after relative stability.  For example, pizza sales might drastically increase when a pandemic strikes as people no longer go out to eat and tend to order more delivery pizza.

GIGO - Garbage In Garbage Out.  It is the same idea as FIFO or LIFO.  

WSJF (Weighted Shortest Job First) is a technique for prioritizing tasks in the scale-able Agile Framework (SAFe). It is pronounced "Wiz-jiff." I'm not a fan of this technique.

The CIA Triad is confidentiality, Integrity, and data Availability. Basically, as part of DevOps, SecDevOps, and BizOps, all stakeholders must continually consider the CIA.

OMGA - (Owner, Member, Guest user, Application User) is a security structure used to control access.

6 hats/ Six hat thinking - helps with creative thinking within group decision-making.  

ProActivity Hunt - SOC tries to imagine scenarios/hypothetical situations and, using data capture, verify if there are security risks.  I've only ever heard this term at Microsoft.

TV pickup is a phenomenon that occurs in the United Kingdom, involving sudden surges in demand on the national electrical grid, which happens when a large number of people simultaneously watch the same television program and an advert break or half time happens as we all switch on our tea kettles et al.  

Useful Glossary:

The Architecture Review Board (ARB) functions as governance to ensure IT projects/programs align with the business's IT Architecture and IT initiatives align with the company's IT goals.

Change Advisory Board (CAB) - a board of members that evaluates changes and the associated risks to the business.  It has a strong technology influence, not only technical.  Sometimes, CABs in companies are IT-focused, dealing with IT change requests, and are more like an ARB.

ExCo (Executive Committee) - a collection of decision makers, mainly board members/higher-ups, who make strategic decisions.

MMSP (Managed Security Service Provider) - People, Processes and Technology to protect your business. Outsource service that manages & monitors enterprise security.  IAM, Cloud security, app security, data security, and network security.  Includes MXDR - Core monitoring.

Kill Chain - the steps that trace stages of an attack from the early reconnaissance stages to the exfiltration of data.

SOC (Security Operations Centre) - usually the CoE/security team within a business. 

PAM (Privilege Access Management)—CyberArk and Azure have a PAM that allow temporary recorded privilege escalation for users with dedicated admin accounts.

Enterprise Architecture is one level up from solution architecture. The main frameworks are TOGAF (I am 9.1 certified), the Zachman framework, and the Federal Enterprise Architecture Framework (FEAF), also called FEA. I have used ArchiMate and, briefly, LeanIX (SAP) for modelling within the TOGAF framework to describe the Architecture of a government department; it's okay.

BCM (Business Capability Map) describes what a business does to help build IT services strategically and reduce cost and complexity. It is useful for Asset/Portfolio management and "as is" and "To Be" Architecture.

Open Source Software Licencing for dummies/me: 

1. Public Domain Licence: No restrictions, go for it.

Following are the Permissive licences from most open to most restricted:

2.1 MIT Licence: This is a common permissive (open-to-use) licence.  Simple to understand, anyone can reuse and modify code, but the creator is not liable for any future use.  MIT is almost identical to a 2.2 BSD licence.  It is excellent as you can freely use it, and I would like to offer my code with MIT licencing.  

2.3.GNU General Public Licence (GPL): This is a nice open-source licencing you can reuse or modify, but your work must be open-source and available to all.  If you use GP, make source code available; anyone can use your derivative.

2.4 Apache and Apache 2: These are also Permissive but more protective than MIT licences.

3. Copyleft: This is more restrictive. Users can reuse, but any derivations are bound by the copyleft licence on the originals.

4. Proprietary: Most restricted, closed source; no change or redistribution is allowed.  

GIT Intro

Overview: Git is is very popular and it is similar to existing version control systems.  The key is to be able to work on multiple branches that you can go to at any point.

Let's get started..

Start a new Git repository (repo) - all folders created under the main git folder are part of the repository. Any changes are kept track of and all change history is recorded.  The new repo has a default main branch created automatically.  To create a new repo use the syntax:

>git init

There are files you don't want to keep track of in the repo, so use the .gitignore files or folders to skip specific files from being tracked int he repo.

Once you are writing code, you get a Git local staging area.  -A means All files and folders

>git add -A

Now you make changes in your staging environment via your IDE, and need to add it back to the default "Main" branch.

Create a repo, add code, change code and commit to the branch.

This is a fantastic simple illustration to quickly understand Git.

GitHub is Git hosted (centrally hosted instances, bought by Microsoft circa 2018 but still open source, https://github.com), the developer clones the GitHub repository and works using a distributed source control.  You can host yourself but has great integration with Azure DevOps.   You can also replace the central GitHub with Azure DevOps (Git).

• Git ignore files, tells source control not to include certain file types
• Tags for a specific point in time.  Like labels in TFS/Azure DevOps
• Pull request (PR) - dev made change, and wants to push the code into main branch, someone else generally approves and the code is pulled into the main (Working Directory) branch (depends on branching strategy)
• Developer normally branches of Main branch using a cloned copy on local dev env.  The Developer does changes, then does a PR, the PR if approved gets approved it gets merged into Main (automatically or manually per config), Main branch has the latest code and the developer can delete their cloned branch.
• GitHub Enterprise allows integration with Microsoft Teams (sounds amazing)
• GitHub Codespaces - Instead of local dev, it allows dev using a browser.  Competes with Microsoft's Dev Box (spins up dev env that is browser accessible).  Microsoft Dev Box iGB VM's to choose from, the 16GB, has 4 vCPU's.  Only bills when DevBox is running but the storage used is continuous.  If left on/max monthly cost, it would cost about £370, if well managed i.e. turned off on weekend and overnight but used for roughly 8 hrs a day cost would be around £85 for a month.  All dev licences are included.  Pls check with Ms this is my understanding.

DevOps has morphed into DevSecOps (Development, Security, Operations) - same team responsible for all the roles.  Continuously ensure security built in, call shifting left so it is not tact on at the end of the project.  Includes monitoring and auditing.  Git like other source control systems assists in DevSecOps.

CORS for SharePoint Requests

Problem:  I wish to create a standard header for my client to layover multiple applications to tie together branding and global organisation branding.  Similar to what O365 does as shown below:

Provide a common header that logs the user in and dynamically generates the header within SharePoint.  Use an HTTP Javascript request from multiple children applications to deliver the shared user common header.  As I have numerous application on sub-domains (e.g. https://hr.contoso.com https://cash.finance.contoso.com and even https://clients.oldurl.net) so I need to ensure I allow CORS requests that also allow for user authentication.  

"The CORS mechanism supports secure cross-domain requests and data transfers between browsers and web servers."  Mozilla

Initial Hypothesis:

Option 1 - IIS and SharePoint struggle to handle this requirement using configuration.  For instance, by default, only same origin subdomain requests are allowed.  Adding the header Access-Control-Allow-Origin: * allows for any domain but I can't specify to use credentials so I need an anonymous site, and then I lose my ability to identify my user and generate a dynamic menu.
Result: Fail.  I receive the following error in the browser: "A wildcard '*' cannot be used in the 'Access-Control-Allow-Origin' header when the credentials flag is true"

Option 2- Specify a multiple sub-domains, i.e. Access-Control-Allow-Origin: https://hr.contoso.com,other.contoso.com
To do authentication, I now need the following 3 HTTP response headers:
Access-Control-Allow-Origin: https://hr.contoso.com, https://other.contoso.com
Access-Control-Allow-Credentials: true
Vary: Origin
Result: Fail.  I receive the following error in the browser: "The 'Access-Control-Allow-Origin' header contains multiple values '', but only one is allowed".

Option 3 - Specify a single sub-domains, i.e. Access-Control-Allow-Origin: https://hr.contoso.com
Access-Control-Allow-Origin: https://hr.contoso.com
Access-Control-Allow-Credentials: true
Vary: Origin
Result: Fail.  Works for the hr sub-domain but my other sub-domains fail. I have multiple sub-domains that need access.

Key take away: There can only be 1 Access-Control-Allow-Origin response header, and the returned Access-Control-Allow-Origin header can only have one URL.

Option 4 - Dynamically inject the Access-Control-Allow-Origin, in SharePoint, this is an ISAPI filter, or I need to use the Global.asax to dynamically set the HTTP Access-Control-Allow-Origin header to a white-list list of URLs.  Beware of caching pages downstream.  Alternatively, URL Rewrite can be used on the IIS WFE's.

Thanks to Abhishek Sharma for highlighting my lack of knowledge about CORS requests.