In SQL server 2022, MS introduced a new feature in the area of security , called Ledger. SQL server ledger makes data temper-evident through cryptography which enables organizations to confirm if their data is 100% trusted or if it has been tempered with. This feature uses are power of blockchain yet avoid the complexity and performance overhead of it.
Ledger also ensures that data is not tempered by any high privileged user such as DBAs , system administrator or cloud administrators.
As we know that blockchain is a distributed database or ledger that stores different blocks of information cryptically linked to each other forming a chain, thus called blockchain. Creating a decentralized blockchain solution like bitcoin is a heavyweight solution and SQL server ledger solves this by providing integrity for off-chain storage of blockchain networks. This avoid the complexity and performance consideration of traditional blockchain solutions.
SQL ledger achieves this by using below concepts
- Database Ledger
- Ledger tables/database
- Trusted Storage
Database ledger incrementally stores information of state of ledger table as changes occur on ledger tables. It does so by storing the hash of every row modified by transaction occurring on a ledger table , called root hash. The transaction is also hashed . Both hashes together forms a block which is again hashed through the root hash of the block along with the root hash of the previous block as input to hashing function; thus linked to previous block and forming a blockchain. Ledger database is a database that contain only ledger tables. Regular tables cannot be created in ledger database.
There are two kind of ledger tables in SQL server
- Updatable ledger tables: Allows updates and deletion of existing rows.
- Append-only ledger tables : Only allows inserting new rows.
Trusted storage is required to store the latest block in database ledger , called database digests which represents the all ledger tables in database at the time block was created. This trusted storage can be Azure Blob Storage configured with immutability policies, Azure Confidential Ledger or on-premises Write Once Read Many (WORM) storage devices. Trusted immutable storage is required to ensure that no one can temper with database digests which are required at later stage to verify the data stored in ledger tables.
In summary, SQL server 2022 has introduced this new feature to enable organizations to confirm trust of their data to auditors/ government agencies or other parties with 100% surety. This feature uses are power of blockchain yet avoid the complexity and performance overhead of it.
Happy learning !!
References: Ledger overview – SQL Server | Microsoft Learn