Always Encrypted SQL Databases, all thanks to Sitecore
Background
There has been a lot of massive data breaches occurring involving some of the largest organisations in Australia in 2019–2023, which exposes a big amount of Personal Identifiable Information (PII) data. The exposed data also include government and financial institution data tied to people. Those who were affected have to change all their Government issued IDs, and all the banks had to proactively step in to help their customers safe guard their accounts.
In some other cases, the exposed data would also jeopardise someone’s professional status as employee data was involved.
Protecting Data
I recently had to work on a project migration, where PII data was previously stored in house on a Microsoft SQL Server, and migrating to a software vendor managed SaaS application, where the data is hosted in a Microsoft Azure SQL Managed Instance.
I was heavily investigating protecting personal sensitive data in a previous Sitecore implementation from a few years back, and have read a lot of documentation on the various protection already in place, or as options available in Sitecore.
Implementation
Microsoft Azure SQL Server MI has the following enabled by default
- Transport Layer Security (TLS) — connection encryption between client application and database
- Transport Data Encryption (TDE) — the physical files such as data and log files are encrypted. The data restoration process requires an account with the correct RBAC access to those files before they can be restored.
There are specific database table columns that contains PII sensitive data using Always Encrypted. The decision is to encrypt those individual columns with Azure Key Vault, which is deliberately not managed by the SaaS vendor. In a case where the database, or its backup is compromised, or worse still, the Azure Subscription from the vendor is compromised, the data backup files are totally useless to the attacker.
This ensures that the key is always held by the customers, and can be rotated as required.
Data Retention Policies
Another major concern from the data breaches, is that organisations are holding onto data for way too long, even though their customers have not interacted with the organisation for many years.
The challenge that I was facing, was that the transactional data is very important to maintain for inventory record purposes, as that data is used to gauge the popularity of a particular product.
The decision was made to retained only 5 years of records associated with a user of the system. However, due to inventory reasons, the data cannot be Deleted. Instead, an anonymised process is run every day to anonmise transactional records that are older than 5 years.
This was done by creating a fake user called “Anonymous User” with a User ID that does not exists in any system to prevent data clash. e.g. creating a user with an email zzzz@zzzz.zz. The transactional records that are older than 5 years, are then assigned to this anonymous user once a day. Any users in the User database table without any child records, is then deleted as part of the daily anonymise process.
Summary
There is never a 100% fool proof method in cyber security. The most important thing I have learned from a security architect that I worked with, is that we need to add as many steps as possible for a person to make mistakes, and accidentally exposed the data.
- Encryption in transit
- Encryption at REST
- Encryption at storage
- Minimise number of records
The strategies implemented above should hopefully help reduce the risk to data exposure.
