In light of the recent NHS attack, I wanted to write a blog about Security and re-emphasise what we should be doing to protect our data, especially in regards to SharePoint.

This blog will focus on SharePoint within Office 365 and on Premise and some (of many) pointers of defence to help protect SharePoint.

  • A common security issues I see with many clients is permission governance.

A SharePoint governance plan can help keep your data secure and compliant, by helping you structure, create policies and procedures and implement controls, such as designing security controls, permissions and roles for assigning permissions etc.

SharePoint is used to store data, and on most occasions, sensitive data.  It is important to ensure that this sensitive data is not accessible to the people who do not need to see it, especially as data if shared with external parties, such as contractors, partners etc, therefore it is vital to ensure that access rights remain aligned with the business needs.  There have been many cases known where data has got into the wrong hands, e.g. United States v. Manning.

This also includes using the least privileged accounts and use specific accounts for specific purposes.  I have seen many SharePoint systems where IT use the farm account as their admin account.   Plan for administrative service accounts : .

Also you should being using Groups to manage users as much as you can. Using groups gives you a more maintainable security model, meaning If you want to make a change to permission, you apply it to a group, not individual people.

for e.g. different sites require different governance policies. Site such as homepage would be more tightly governed as it would typically be available to everyone in the organisation, whereas the HR department, for example would be more tightly governed than other generic sites such knowledge based areas.

  • Multi Factor Authentication – Within office 365, Multi Factor Authentication (MFA) increases the security of user login. With MFA, users are required to enter an second stage of authentication after their initial entering of their password. The second stage requires the user to receive an email, app notification, phone call or text message to enter a number. Only after the second stage is complete will the user be authenticated to sign in.
  • Virus Protector: For On-Premise installs of SharePoint, there are a growing number 3rd party tools which can be used for anti-virus protection, such as
    1. MacAfee
    2. Bit Defender
    3. Sophos
    4. Trend Micro

However, Microsoft recommend that you install an anti-virus solution based on SharePoint Portal Server Virus Scanning Application (VS API) – this is because SharePoint is continually providing enhancements to SharePoint via, installing a non related SharePoint AV will not guarantee you any support for SharePoint specific issues.

Within Office 365, files are scanned as they are uploaded. If a file is found to be infected, a property is set so that users cant download that file from the browser or syn the file in the One Drive for business client.

This is not to say you should not have a virus protector on your local machine

  • Information Rights Management (IRM) – IRM is a technology applied at the list / library level in SharePoint. A document that is IRM permission can either permit / deny a user from doing certain actions within that document such as :
    1. Controlling copy and paste
    2. Preventing printing and editing
    3. Require users to review their credentials at specific intervals
    4. Provide ability to not upload documents that are not IRM protected
    5. Allow people with least the view items permissions to run embedded code or macros on a document.

Now this next step is not strictly a SharePoint security setting. With SharePoint, Online and On-Premise it’s important to have some sort of lock or password on your device, especially as users now bring their own devices into work. SharePoint is available on mobile, apps like Yammer, One Drive for Business can be exposed thus potentially leaking sensitive data.

In addition to this, I wrote a blog on Data Loss Prevention (here) which is another great way of ensuring that sensitive data does not leave the corporate domain.

Excel Web Access and OData Connections: Data Refresh

Have you ever tried to use a SharePoint list to populate a Power Pivot Graph in excel ; and then render the graph in SharePoint using Excel Web Apps? – Sounds pretty cool eh!?

Not Quite.

I created a SharePoint list, for e.g. Profit Loss , which details the a financial summary of a project, e.g.  (Profit is a calculated column)


Having created the list, I opened up Excel and created a OOData Data Feed connection (below) and generated a graph based on the data within the list.


Graph below


Seems relative.

However, if you make a change to the SharePoint list, the excel web access web part in SharePoint  does not dynamically update to show an updated chart to reflect the change made on the list.  You must manually open the excel sheet, refresh data connections and then save the excel sheet again to update the excel web access web part.

If you manually refresh the Excel web part by clicking Data, Refresh Selected DataConnection, the web part loads  the new updated data;

Screenshot below showing new data


Screenshot below showing a manual refresh


Result after doing a Manual Refresh (notice the profit)


If the entire page is refreshed, the webpart reverts back to the original, see image below.

Normal Chart


I noticed that if you have the excel sheet open in real time and make a change to the SharePoint list – the Excel will in fact update – but it does not save the sheet. (You can check the modified date on the library).

Having done further tests – I also noticed the Pivot Chart had a ‘Refresh data when opening the file’ – I checked this and saved the excel file to my sharepoint document library. DataRefresh

The Excel Web Access web part now showed an Warning when the page was loaded, as below


The user must click Yes to load the worksheet – which as a result refreshes the latest data. Not, really a solution though.

For On-Premise users – we can resolve the warning message by adding the Excel file as a trusted location in the Excel Services Application; however, the Excel Service Service Application is not available in SharePoint on-line 🙁

I guess the only viable solution is to for a user to manually open the Excel sheet(s) and do a refresh all and then save the worksheet back into SharePoint; thus updating all the Power Pivot Charts..

I will let you know if there are any updates on when or if the Excel Service Application is available so we could add worksheets into the trusted locations.

There is a good blog article here which could be of help should you run into a similar issue:


How safe is it storing data in the cloud?

My journey into work last week consisted of me having a debate with a few friends about storing data on-premise vs storing data in the cloud, in particular office 365.
I was able to discuss some key points and advantages of using office 365, so I thought it would be a good idea to give a brief overview on some of the advantages of using office 365 on my blog and more importantly outline what measures Microsoft take to ensure that ‘our’ data is safe in the cloud.

Firstly, its important to understand that Microsoft offers various levels of security to its users within Office 365.
No one has specific access to data, No third party, literally no one.
Each data centre (where data is stored) has a strict control of access as to who can actually enter the data centre & what they can do; this is governed by what Microsoft called ‘Lock Box’. Essentially  meaning that if an engineer does have to go into the data centre; then their is a strict access control as to what the engineer can do – at no point will that engineer have access to the data; its mainly just troubleshooting tasks.

Below is a brief overview on some of the various layers of security offered on different layers.

Network Layer – Firstly all data on Office 365 is encrypted in transit using TLS/SSL; this ensures that data is confidential; (previous blog post on SSL) – so if a user did ‘intercept’ communication; then the results would be scrambled and would be of no use to them.

Physical Layer.  On Premise, an IT Admin or IT staff know exactly where the disk that contains the data is, they know exactly which computer it is on and exactly how to get to it; this is generally how an premise environment is run – in that someone knows exactly where the data is. A malicious user once on the server has the ability to do whatever they want, i.e. run code, delete data, copy data, remove the drive etc.

In the cloud world, the only folks who can get into the data centre are the engineers, for e.g. during maintenance – but their are strict access controls in place to ensure data is not accessed, its mainly just troubleshooting tasks.
Having ability to find out whose server is whose, or which partition data lives on or where it lives within the data centre is like finding a needle in a haystack, the size of the data centre and the amount of servers would mean a malicious person would never know which disk drive belongs to a particular person.
But in a worse case scenario supposing an engineer pulls a drive out? – Microsoft have invested in BitLocker which basically means that the drive that is pulled out will be wiped.

Furthermore Microsoft have  a Blue team and red team.
The red team are constantly trying to ‘hack’ into Microsoft Data centres whilst at the same time the Blue team are consistently trying to prevent those attacks.

Logical Layer. No code that is not known to Microsoft is allowed to be executed on any of the servers; i.e it cant get random code out of the environment and run it on servers . Only known processes are white listed to run on servers, This would make is virtually impossible for a malicious user to run a malicious code on a server (that’s if they ever got on).

User Layer  – The office 365 admin portal offers much more in terms of security.

>Multi Factor Authentication – Multi factor Authentication is a two way sign in process, making it harder for a malicious user to get into your account; When a user signs into their Office 365 account with their username and password – an additional layer of security must be acknowledged via a phone call or text before that user can sign in.  This feature is also available on most Hotmail / Outlook accounts.

> Data loss prevention – DLP essentially scans emails for sensitive information, such as “Credit Card Number”.  Warnings can be given to the sender alerting them and give the sender control of weather they would like to send the email or not. If the sender agrees to send the email, then it can be encrypted using TLS encryption or we could apply rights management

> Rights management. Rights Management is a list / library setting (within SharePoint) that allows site owners to protect attachments stored against list items and / or supported file types.
For e.g. If a document is downloaded; the file is encrypted  so only authorised people can view it; furthermore the file can be have restrictions imposed on it; making it impossible for users to print, copy, save a local copy etc.

There is a fantastic white paper which is available here to download which details the above with additional security measures Microsoft have  taken to ensure data is safe in the cloud.