Providing Tamper Proof Data Solutions

Have you ever wanted to keep complete change records in your database, or understand the history of how data changed over time?

There are a number of SQL Server technologies that can be deployed to help you with this, whether its Change Data Capture or System Versioned Temporal Tables. These technologies have a downside though – tampering. Data changes can still be removed from the database by a user with enough permission. For financial or health organisations, this must not be allowed to happen – we need immutable data.

So, what is the answer? Microsoft have launched a new technology for Azure SQL Database – the Azure SQL Database Ledger.

Azure SQL Database Ledger is a fusion on SQL and Blockchain technology. It works by using the same cryptographic patterns seen in Blockchain technology, with each database transaction being cryptographically hashed and inserted into both the database table and a database digest stored outside the Azure SQL database, on tamper proof storage. These database digests are then used to verify that the data in the database has not been tampered with. The hashes in the digest are used to compare with the hashed values stored in the Azure SQL database ledger table, so any data change is detected.

Azure SQL Database Ledger provides two types of tables; update-able ledger tables where you are free to update existing data with your TSQL and append only tables which you can only add data to. The figure below shows an overview of the components of the technology.

Use cases for this technology include anywhere where data requires auditing, as the technology would greatly improve the time spend proving the data has not been tapered with since it was recorded.

There are some restrictions of the technology at the time or writing, which include some unsupported datatypes like XML, SqlVariant, User defined types and Filestream.  In memory and sparse tables are also not supported, and existing tables cannot be converted to ledger tables.

A word of caution, this technology doesn’t lend itself yet to high volume transaction environments, due to the overhead of the cryptographic processing – so don’t use it for everything in your database.  Instead use a blended approach by combining with other SQL Server technologies like System Versioned Temporal Tables to protect just the data you need to.

Would you like to know more?

Would you like to know more on how your organisation can take advantage of a modern cloud technology approach to solving your data problems?  Contact us on the link below.

About the author