Category: SQL Server

by Marcos Flores 2023-05-11

Ledger for SQL Server 2022

Hello! And welcome to another post in the best data and cloud blog. In today’s post we are talking about advantatges of apply Ledger in SQL Server 2022.

Introduction

With the advent of SQL Server version 2022, Microsoft has introduced a secure, tamper-proof system that ensures data integrity. Ledger is based on blockchain to ensure that changes can not be made without being visible. For those who don’t know yet blockchain in 2023, it’s basically a chain of blocks or tokens that contains encoded information of a transaction. These interleaved tokens allow the transfer of encrypted data securely through cryptography. This means that, once information is entered, it can not be deleted, since information from previous tokens would have to be modified.

Other SQL Server technologies that provide access to a history of changes, such as CDC (Change Date Capture), are not tamper-proof, since privileged users can manipulate date. Blockchain technology makes such tampering impossible, as any change is saved encrypted and contains the previous encrypted block.

In environments where the integrity of stored date is important (such as organizations with financial, medical or sensitive data), establishing trust around integrity has been a persistent problem over time. The new SQL Server 2022 feature helps protect data from any source, be it an attacker, highly privileged user, system and cloud database administrators.

Historical data is managed transparently to applications, so you don’t have to make any changes. Such data is in a relational format, so it supports SQL queries for auditing or forensic purposes. Thanks to this, in addition to guaranteeing data integrity, it also takes advantage of the efficiency, flexibility and performance of SQL databases.

Use cases

Audits

It is perhaps the first use case that comes to mind when implementing Ledger.

In any productive system, it is necesary to trust the data that is consumed and produced. If the data has been maliciously tampered with, it can result in disaster for the various business processes that depend on that data.

An audit verifies that implemented processes are correct, reviews audit logs, and inspects authentication and access controls. These manual processes can expose potential security flaws in the system, but they can not provide verifiable proof that data has not been maliciously modified.

Ledger demonstrates data integrity cryptographically. This test helps simplify the audit process.

Data reliability

Sometimes it is necessary to share certain information between the different supply chains of a company. This is a challenge when you need to share data and trust it at the same time.

Blockchain technology implemented with Ledger is the perfect solution for when we need to centrally verifiy data integrity in networks where trust is low.

Off-chain storage

When we need a blockchain network for some corporate process, it is necessary to be able to query the data without sacrificing the overall performance of the system.

The solution that is often used to solve this problem is to replicate the data from the blockchain to a store, such as an SQL database. Ledger ensures data integrity for off-chain storage, ensuring data trust throughout the system.

Example

USE master;
GO
-- Creating the test database
--
DROP DATABASE IF EXISTS DB_Ledger;
GO
CREATE DATABASE DB_Ledger;
GO
USE DB_Ledger;
GO

-- We need to activate the snapshot isolation in order to be able to verify the ledger
ALTER DATABASE DB_Ledger SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

With Snapshot Isolation activated, we make sure that the transactions we carry out on the server are isolated from each other, forming transaction blocks. Later we will see the utility of this.

USE DB_Ledger;
GO
/* Create the example table Employees and mark it as a Ledger table. Note: At the
moment, it is not yet possible to update a table to add the functionality of they Ledger, only when creating it */
DROP TABLE IF EXISTS [dbo].[Employees];
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[SSN] [char](11) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[Salary] [money] NOT NULL
	)
WITH 
(
  SYSTEM_VERSIONING = ON,
  LEDGER = ON
); 
GO

Once the table is created, we can see it in the interface marked as Updatable Ledger

USE DB_Ledger;
GO

/* Adding test data */

DECLARE @SSN1 char(11) = '795-73-9833'; DECLARE @Salary1 Money = 61692.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN1, 'Catherine', 'Abel', @Salary1);
DECLARE @SSN2 char(11) = '990-00-6818'; DECLARE @Salary2 Money = 990.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN2, 'Kim', 'Abercrombie', @Salary2);
DECLARE @SSN3 char(11) = '009-37-3952'; DECLARE @Salary3 Money = 5684.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN3, 'Frances', 'Adams', @Salary3);
DECLARE @SSN4 char(11) = '708-44-3627'; DECLARE @Salary4 Money = 55415.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN4, 'Jay', 'Adams', @Salary4);
DECLARE @SSN5 char(11) = '447-62-6279'; DECLARE @Salary5 Money = 49744.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN5, 'Robert', 'Ahlering', @Salary5);
DECLARE @SSN6 char(11) = '872-78-4732'; DECLARE @Salary6 Money = 38584.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN6, 'Stanley', 'Alan', @Salary6);
DECLARE @SSN7 char(11) = '898-79-8701'; DECLARE @Salary7 Money = 11918.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN7, 'Paul', 'Alcorn', @Salary7);
DECLARE @SSN8 char(11) = '561-88-3757'; DECLARE @Salary8 Money = 17349.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN8, 'Mary', 'Alexander', @Salary8);
DECLARE @SSN9 char(11) = '904-55-0991'; DECLARE @Salary9 Money = 70796.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN9, 'Michelle', 'Alexander', @Salary9);
DECLARE @SSN10 char(11) = '293-95-6617'; DECLARE @Salary10 Money = 96956.00; INSERT INTO [dbo].[Employees] ([SSN], [FirstName], [LastName], [Salary]) VALUES (@SSN10, 'Marvin', 'Allen', @Salary10);
GO

USE DB_Ledger;

/* If we query all fields, we can see that the created ones appear */

SELECT EmployeeID, SSN, FirstName, LastName, Salary
FROM dbo.Employees;

USE DB_Ledger;
GO
/* But there are hidden fields that store information about the Ledger */
SELECT EmployeeID, SSN, FirstName, LastName, Salary, 
ledger_start_transaction_id, ledger_end_transaction_id, ledger_start_sequence_number, 
ledger_end_sequence_number
FROM dbo.Employees;
GO

We know that some columns are 0 or NULL. This is because no changes have been made to those columns yet.

USE DB_Ledger;
GO

/* If we make a query about the view that is automatically created when you create a Ledger table,
we can see hidden information about the table in addition to the operation that has been performed on that column */

SELECT * FROM dbo.Employees_Ledger;
GO

USE DB_Ledger;

/* If we query the view that is automatically created when  */

CREATE VIEW [dbo].[Employees_Ledger] AS   

SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary]
, [ledger_start_transaction_id] AS [ledger_transaction_id]
, [ledger_start_sequence_number] AS [ledger_sequence_number]
, 1 AS [ledger_operation_type]
, N'INSERT' AS [ledger_operation_type_desc] 
FROM [dbo].[Employees]   
UNION ALL   
SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary]
, [ledger_start_transaction_id] AS [ledger_transaction_id]
, [ledger_start_sequence_number] AS [ledger_sequence_number]
, 1 AS [ledger_operation_type]
, N'INSERT' AS [ledger_operation_type_desc] 
FROM [dbo].[MSSQL_LedgerHistoryFor_901578250]   
UNION ALL   
SELECT [EmployeeID], [SSN], [FirstName], [LastName], [Salary]
, [ledger_end_transaction_id] AS [ledger_transaction_id]
, [ledger_end_sequence_number] AS [ledger_sequence_number]
, 2 AS [ledger_operation_type]
, N'DELETE' AS [ledger_operation_type_desc] 
FROM [dbo].[MSSQL_LedgerHistoryFor_901578250]

USE DB_Ledger;
GO

/* We can set up a simple query to extract more useful information. We can see that the only user
who has entered data is me */

SELECT e.EmployeeID, e.FirstName, e.LastName, e.Salary, 
dlt.transaction_id, dlt.commit_time, dlt.principal_name, e.ledger_operation_type_desc, dlt.table_hashes
FROM sys.database_ledger_transactions dlt
INNER JOIN dbo.Employees_Ledger e
ON e.ledger_transaction_id = dlt.transaction_id
ORDER BY dlt.commit_time DESC;
GO

USE DB_Ledger;
GO

/* Now we generate the digest. The digest is a point of reference of the current sate of the Ledger objects. We will 
use the output to check that the system has not been manipulated. */

EXEC sp_generate_database_ledger_digest;
GO

/*

{"database_name":"DB_Ledger","block_id":0,"hash":"0x5F3D97F7C4C44E131447D081875193EC4B75C3563E97112DF74B2067D30692B2"
,"last_transaction_commit_time":"2023-05-09T13:52:27.9600000"
,"digest_time":"2023-05-09T12:32:11.2864004"}

*/

USE DB_Ledger;
GO

/* With the following query we can see the number of cryptographically linked block sets.
At the moment there is only one until we generate the next digest. */

SELECT * FROM sys.database_ledger_blocks;
GO

USE DB_Ledger;
GO

/* We are now ready to do a proper audit. Let's start by updating the salary of one of the employees. */

UPDATE dbo.Employees
SET Salary = Salary + 50000
WHERE EmployeeID = 4;
GO

USE DB_Ledger;
GO

/* If we do a regular query to the database, we can see the updated value but not who did it */

SELECT EmployeeID, SSN, FirstName, LastName, Salary, 
ledger_start_transaction_id, ledger_end_transaction_id, ledger_start_sequence_number, 
ledger_end_sequence_number
FROM dbo.Employees;
GO

USE DB_Ledger;
GO

/* But if we query on the system tables as we have done before, we can see the changes on the field, the user that has made the changes and the time */

SELECT e.EmployeeID, e.FirstName, e.LastName, e.Salary, 
dlt.transaction_id, dlt.commit_time, dlt.principal_name, e.ledger_operation_type_desc, dlt.table_hashes
FROM sys.database_ledger_transactions dlt
INNER JOIN dbo.Employees_Ledger e
ON e.ledger_transaction_id = dlt.transaction_id
ORDER BY dlt.commit_time DESC;
GO

We can see the different values that the field Salary has had and the time of modification (Insert and Delete).

USE DB_Ledger;
GO

/* Let's generate another digest and save the output again*/

EXEC sp_generate_database_ledger_digest;
GO

/*

{"database_name":"DB_Ledger","block_id":1,"hash":"0xC20F463A913BC01F76F14EA4CCA98C11180CEE8F4271AEA37382479A103CC17E"
,"last_transaction_commit_time":"2023-05-09T14:36:31.2766667"
,"digest_time":"2023-05-09T12:54:24.8138467"}

*/

USE DB_Ledger;
GO

/* Time to verify data integrity. If we run the following script with the JSON from the first generated digest,
the returned value matches the id of the block  */

EXECUTE sp_verify_database_ledger 
N'{"database_name":"DB_Ledger","block_id":0,"hash":"0x5F3D97F7C4C44E131447D081875193EC4B75C3563E97112DF74B2067D30692B2"
,"last_transaction_commit_time":"2023-05-09T13:52:27.9600000"
,"digest_time":"2023-05-09T12:32:11.2864004"}'
GO

USE DB_Ledger;
GO

/* We can see both blocks if we run the ledger blocks*/

SELECT * FROM sys.database_ledger_blocks;

USE DB_Ledger;
GO

/* We can also see that the previous_block_hash field of the second block (id 1) is the hash of the first digest we got.

If we check the second digest we can see it belongs with the second ledger block.
 */

EXECUTE sp_verify_database_ledger 
N'{"database_name":"DB_Ledger","block_id":1,"hash":"0xC20F463A913BC01F76F14EA4CCA98C11180CEE8F4271AEA37382479A103CC17E"
,"last_transaction_commit_time":"2023-05-09T14:36:31.2766667"
,"digest_time":"2023-05-09T12:54:24.8138467"}'
GO

We have managed to check two things:

First, the data was valid in the moment we generated the digest.
Second, the internal blocks must match the salary update for employee number 4.

And that’s all. If you liked this post, you will surely be interested in our latest articles:

Update MongoDB without Service Issues

The importance of SQL Server Updates

Access Virtual tables from Microsoft Dataverse with SQL Server

If you need to improve and optimize the performance of your databases, visit our SQL Server Health Check service.

SQL Server Health Check

Marcos Flores

DBA SQL Server with 5 years of experience. Although I have advanced knowledge in various areas of SQL Server, I am also currently discovering MongoDB.